Cannot reset database because "supabase db reset" tries to possibly drop types in wrong order

[hammerlscs]

Describe the bug

We cannot reset the database of our Supabase testing instance:

npx supabase db reset --linked --debug --create-ticket
Using connection pooler: postgres://...
Supabase CLI 1.163.2
Do you want to reset the remote database? [y/N]
y
Resetting remote database...

...

2024/04/24 13:31:55 PG Recv: {"Severity":"NOTICE","SeverityUnlocalized":"NOTICE","Code":"00000","Message":"truncate cascades to table \"mfa_amr_claims\"","Detail":"","Hint":"","Position":0,"InternalPosition":0,"InternalQuery":"","Where":"SQL statement \"truncate auth.sessions restart identity cascade\"\nPL/pgSQL function inline_code_block line 44 at EXECUTE","SchemaName":"","TableName":"","ColumnName":"","DataTypeName":"","ConstraintName":"","File":"tablecmds.c","Line":1826,"Routine":"ExecuteTruncateGuts","UnknownFields":null}
2024/04/24 13:31:55 PG Recv: {"Type":"ErrorResponse","Severity":"ERROR","SeverityUnlocalized":"ERROR","Code":"2BP01","Message":"cannot drop type our_enum_type[] because type our_enum_type requires it","Detail":"","Hint":"You can drop type our_enum_type instead.","Position":0,"InternalPosition":0,"InternalQuery":"","Where":"SQL statement \"drop type if exists public._our_enum_type cascade\"\nPL/pgSQL function inline_code_block line 64 at EXECUTE","SchemaName":"","TableName":"","ColumnName":"","DataTypeName":"","ConstraintName":"","File":"dependency.c","Line":828,"Routine":"findDependentObjects","UnknownFields":null}
2024/04/24 13:31:55 PG Recv: {"Type":"ReadyForQuery","TxStatus":"I"}
2024/04/24 13:31:55 PG Send: {"Type":"Terminate"}
ERROR: cannot drop type our_enum_type[] because type our_enum_type requires it (SQLSTATE 2BP01)  

Our theory so far is, that the order in pg_type is responsible for this problem.

This is the part of the code which is executed by supabase db reset to drop all types:

for rec in
    select *
    from pg_type t
    where t.typnamespace::regnamespace::name = 'public'
loop
    execute format('drop type if exists %I.%I cascade', rec.typnamespace::regnamespace::name, rec.typname);
end loop;

When we just executed the select part of this statement:

select *
from pg_type t
where t.typnamespace::regnamespace::name = 'public'
AND t.typname LIKE '%our_enum_type%'

We found out that the order is different in our production, testing and local setup.

In production:

oid	typname	typsubscript	typelem	typarray
54252	our_enum_type	-	0	54251
54251	_our_enum_type	array_subscript_handler	54252	0

And in local:

oid	typname	typsubscript	typelem	typarray
17960	our_enum_type	-	0	17959
17959	_our_enum_type	array_subscript_handler	17960	0

But in testing:

oid	typname	typsubscript	typelem	typarray
204381	_our_enum_type	array_subscript_handler	204382	0
204382	our_enum_type	-	0	204381

(I omitted all columns I thought were not significant to this problem.)

In production and local the enum type our_enum_type comes before its array type. In testing the order is reversed. We think that this might the root of the problem.

Therefore, a possible fix could be to specify the order when dropping the types:

for rec in
    select *
    from pg_type t
    where t.typnamespace::regnamespace::name = 'public'
    -- For example: order by t.typname DESC
    -- Or: order by t.typarray DESC
loop
    execute format('drop type if exists %I.%I cascade', rec.typnamespace::regnamespace::name, rec.typname);
end loop;

System information

• Ticket ID: 7118bdd272ec476c9c08eb8ce9f759ae
• Version of CLI: v1.163.2

[sweatybridge]

I agree with your analysis. Ordering is indeed a problem. If you want to submit a PR, I'd be happy to merge it.

[hammerlscs]

@sweatybridge, thanks for the quick reply. Here is the PR with our proposed fix: https://github.com/supabase/cli/pull/2208

[sweatybridge]

Could you also report the postgres version of your production and testing projects? I just want to rule out the possibility of version mismatch.

In addition, could you also provide a SQL snippet for creating the types our_enum_type[] and _our_enum_type to help me reproduce? I tried a few things but none of them gave the same result as what you described.

create type our_type_enum as enum ('a', 'b');
create domain our_type_domain as our_type_enum[];
create type our_type_composite as (one our_type_enum, two our_type_enum);

[hammerlscs]

Production: Postgres version 15.1.0.137 Testing: Postgres version 15.1.0.116

This is the migration for the enum type:

CREATE TYPE our_enum_type AS ENUM ('a', 'b');

CREATE TABLE public.our_table
(
    "id" UUID DEFAULT gen_random_uuid() NOT NULL,
...
    "enum_type" our_enum_type NOT NULL,
...

    PRIMARY KEY ("id")
);

The type is not used in any other table, in no function, in no RLS policy, ...

And we do not create the our_enum_type[] array type. It is created automatically by PostgreSQL:

Whenever a user-defined type is created, PostgreSQL automatically creates an associated array type, whose name consists of the element type's name prepended with an underscore [...].

https://www.postgresql.org/docs/current/sql-createtype.html

[sweatybridge]

Ic, thanks for clarifying. It seems like the underscore type is always created as a base type, which we can filter out with where typtype != 'b'.

It is not possible for users to create a base type on Supabase because it requires superuser. Hence, there's also no need to drop a base type.

The fourth form of CREATE TYPE creates a new base type (scalar type). To create a new base type, you must be a superuser. (This restriction is made because an erroneous type definition could confuse or even crash the server.)

[hammerlscs]

Thank you @sweatybridge, the problem is fixed in the latest release v1.163.4