db reset --linked silently skips a user schema when its OID collides with another catalog's OID (missing pd.classid)

[AlexBittermann]

Environment

CLI version	2.111.0. Verified still present at v2.116.0 (current latest); the query has carried this since 2024-04-25.
OS	Linux (ubuntu-latest, GitHub Actions). Not platform specific — the defect is a SQL predicate executed against the remote database.
Install method	supabase/setup-cli@v1
Command	supabase db reset --linked --yes

Schema, table and migration names are redacted below. Nothing about the mechanism depends on them.

Actual output

The reset reports success, but one user schema is never dropped. The next migration that creates an object in that schema then fails:

Applying migration <redacted>.sql...
ERROR: relation "<redacted>" already exists (SQLSTATE 42P07)

Afterwards the schema is still fully populated — in our case 19 tables and 63 functions survived a "successful" reset.

There is no warning and no error at the point the schema is skipped.

Expected behavior

db reset --linked should drop every user-defined schema. A schema that no extension created should be dropped regardless of its OID.

The cause

apps/cli/src/legacy/commands/db/shared/legacy-drop-schemas.ts (and identically apps/cli-go/pkg/migration/queries/drop.sql) lists user schemas with:

select pn.*
from pg_namespace pn
left join pg_depend pd on pd.objid = pn.oid
where pd.deptype is null
  and not pn.nspname like any(array[...])
  and pn.nspowner::regrole::text != 'supabase_admin'

pg_depend.objid is only meaningful together with pg_depend.classid, which says which system catalog the OID belongs to. OIDs are unique only within a catalog, so without the classid constraint a dependency row belonging to a pg_class / pg_proc / pg_type entry satisfies the join for an unrelated schema that happens to share the number. pd.deptype is null is then false and the schema is silently dropped from the result set — and therefore never dropped from the database.

Steps to reproduce

The trigger is an OID collision, so it cannot be forced on demand. But the defect is visible in the query itself, and the resulting state is directly observable. To check whether a given project is affected:

-- Any row returned is a schema `db reset` will silently skip.
select pn.nspname,
       pn.oid,
       (select count(*) from pg_depend pd
         where pd.objid = pn.oid) as rows_the_cli_counts,
       (select count(*) from pg_depend pd
         where pd.objid = pn.oid
           and pd.classid = 'pg_namespace'::regclass) as real_dependency_rows
from pg_namespace pn
where pn.nspname not like any(array['information\_schema','pg\_%','\_analytics',
      '\_realtime','\_supavisor','pgbouncer','pgmq','pgsodium','pgtle',
      'supabase\_migrations','vault','extensions','public'])
  and pn.nspowner::regrole::text != 'supabase_admin'
  and exists (select 1 from pg_depend pd where pd.objid = pn.oid);

On our project this returned exactly one row:

nspname    | oid   | rows_the_cli_counts | real_dependency_rows
-----------+-------+---------------------+---------------------
<redacted> | 16424 |                   2 |                    0

real_dependency_rows = 0 is the point: with classid constrained, the schema has no dependency rows at all. It is unambiguously user-defined. The two rows the CLI counts describe a different catalog object that happens to share the number 16424 — in our case the extensions.pg_stat_statements view.

Suggested fix

Constrain the join to the namespace catalog:

left join pg_depend pd
  on pd.objid = pn.oid
 and pd.classid = 'pg_namespace'::regclass

Optionally also and pd.deptype = 'e', since the intent stated in the query's own comment is specifically to exclude extension-created schemas.

Additional context

• Introduced in b5e0e17 (fix: simplify query for user defined schemas #2206, "fix: simplify query for user defined schemas", 2024-04-25), which replaced a name-based information_schema.schemata lookup with this join. The previous implementation could not exhibit the problem.
• Carried unchanged into the TypeScript port in feat(cli): port db push, db reset, and db start to native TypeScript #5715 (2026-07-07).
• The failure state is self-sustaining, which is why it looks rare and then becomes permanent: a skipped schema is never dropped, so it keeps its OID, so it collides again on every subsequent reset. In our project the four sibling schemas carried freshly assigned OIDs in the 375100-375103 range because they are dropped and recreated each reset, while the affected schema still sat at 16424 from the original install. Recovery required dropping it manually once; after that it was reassigned a fresh OID and stopped colliding.
• Because the skip is silent, the visible symptom is a migration failure that looks like a fault in the user's own migrations.