Support for invalid postgresql identifiers

[laurentS]

Bug report

Describe the bug

I got stuck trying to figure out why my foreign keys were not rebuilt with a loop of pg_dump + psql. It turns out that one of my table had what seems to be an invalid name, based on the postgresql docs.

To Reproduce

1. Create a blank db (all through supabase web admin)
2. Add first_table with just the default primary key (the names matter)
3. Add 2nd_table with the default primary key
4. Add an extra column to 2nd_table named fkey and make it reference id in first_table
5. use pg_dump -h db.xxxx.supabase.co -U postgres -W --schema-only --schema=public > db.sql to export the schema
6. In the web UI, delete the two tables we just created
7. use psql -h db.xxxx.supabase.co -U postgres < db.sql to restore the schema
8. Go to 2nd_table / fkey and notice that the foreign key has not been restored

If the table is named second_table instead of 2nd_table then everything works fine. You can try this out by doing something like s/2nd_/second_/g in the dumped file.

Expected behavior

Based on the postgresql docs linked above, 2nd_table does not seem to be a valid table identifier. From 4.1.1 in the docs:

SQL identifiers and key words must begin with a letter (a-z, but also letters with diacritical marks and non-Latin letters) or an underscore (_). Subsequent characters in an identifier or key word can be letters, underscores, digits (0-9), or dollar signs ($).

This tells me that a digit in the first position is not valid. The weird thing is that there is no error nor warning, but things don't work as expected: I expect that the foreign key should be recreated, or postgresql should tell me there is an error, not just silently ignore it.

I see 2 options:

• either it's a bug in postgresql tools (seems unlikely)
• supabase lets me pick an illegal identifier for my table which causes the unexpected behaviour down the line (weird as well, why doesn't postgres complain at table creation?)

I'm not entirely sure what the solution is here, but my guess would be that supabase should check the identifier and not let me use it in this case.

[steve-chavez]

2nd_table is a valid name if you add double quotes(which is what the Table Editor is doing). Try:

CREATE TABLE "2nd_table" ( "2nd_id" int, "2nd_name" text);

SELECT * FROM "2nd_table";
┌────────┬──────────┐
│ 2nd_id │ 2nd_name │
├────────┼──────────┤
└────────┴──────────┘

So the error lies within pg_dump. Maybe try adding --quote-all-identifiers.

[laurentS]

Thanks for your reply.
The sql dump does indeed quote the names, as this (this is without your suggested extra option):

CREATE TABLE public."2nd_table" (...);

ALTER TABLE ONLY public."2nd_table"
    ADD CONSTRAINT "second_table_fkey_fkey" FOREIGN KEY (fkey) REFERENCES public.first_table(id);

As mentioned above, the table is correctly created, but the Foreign key isn't. I tried your suggestion, using --quote-all-identifiers. I can see everything being quoted, including the schema name, etc... but it makes no difference: no foreign key in the end.

I've changed the names of my tables to avoid this issue, but it seems a little worrying to me that this constraint is not created, and there seems to be no warning or anything (psql does not say anything).

Is there a way I can see the server logs for my db? If so, I'll dig a little bit further to understand where the issue is.

[kiwicopple]

Hey @laurentS - I am going to convert this to a Discussion as it seems to be more of an issue with pgdump/psql than anything we can fix ourselves. I'm sure others could find this and comment or share tips