GetTables / GetColumns queries may assign wrong table comments when same table name exists in multiple schemas

[rosssaunders-fd]

Bug Description

When a database has tables with identical names in different schemas (e.g., core.account and cdc_projection.account), the dump command may assign the wrong schema's COMMENT ON TABLE to a table. The misattribution is non-deterministic across database instances because it depends on internal PostgreSQL OID ordering.

Root Cause

In ir/queries/queries.sql, several queries join pg_class on relname alone without filtering by relnamespace:

-- GetTables (line 31), GetTablesForSchema (line 49)
LEFT JOIN pg_class c ON c.relname = t.table_name
LEFT JOIN pg_namespace n ON c.relnamespace = n.oid AND n.nspname = t.table_schema

-- GetColumns (line 112)
LEFT JOIN pg_class cl ON cl.relname = c.table_name
LEFT JOIN pg_namespace n ON cl.relnamespace = n.oid AND n.nspname = c.table_schema

-- GetSequencesForSchema (line 942)
LEFT JOIN pg_class c ON c.relname = s.sequencename
LEFT JOIN pg_namespace n ON c.relnamespace = n.oid AND n.nspname = s.schemaname

When two schemas contain a table with the same name, pg_class matches both rows. The subsequent pg_namespace LEFT JOIN filters one out, but pg_description (joined via d.objoid = c.oid) may already have resolved to the wrong table's OID, depending on which pg_class row the planner returns first.

Notably, GetColumnsForSchema (line 229) already has the correct pattern:

LEFT JOIN pg_namespace n ON n.nspname = c.table_schema
LEFT JOIN pg_class cl ON cl.relname = c.table_name AND cl.relnamespace = n.oid

Fix

Join pg_namespace first, then qualify the pg_class join with relnamespace:

-- Before (buggy)
LEFT JOIN pg_class c ON c.relname = t.table_name
LEFT JOIN pg_namespace n ON c.relnamespace = n.oid AND n.nspname = t.table_schema

-- After (fixed)
LEFT JOIN pg_namespace n ON n.nspname = t.table_schema
LEFT JOIN pg_class c ON c.relname = t.table_name AND c.relnamespace = n.oid

This ensures pg_class resolves to exactly one row per table, scoped to the correct schema.

Affected Queries

┌───────────────────────┬──────┬─────────────────┐
│ Query │ Line │ Status │
├───────────────────────┼──────┼─────────────────┤
│ GetTables │ 31 │ Bug │
├───────────────────────┼──────┼─────────────────┤
│ GetTablesForSchema │ 49 │ Bug │
├───────────────────────┼──────┼─────────────────┤
│ GetColumns │ 112 │ Bug │
├───────────────────────┼──────┼─────────────────┤
│ GetColumnsForSchema │ 229 │ Already correct │
├───────────────────────┼──────┼─────────────────┤
│ GetSequencesForSchema │ 942 │ Bug │
└───────────────────────┴──────┴─────────────────┘

Reproduction

1. Create two schemas with an identically-named table, each with a different COMMENT ON TABLE
2. Run pgschema dump --schema and pgschema dump --schema
3. Observe that some table comments are swapped between schemas
4. Drop and recreate the database, re-run migrations, dump again — the swaps may differ

Environment

• pgschema v1.7.2
• PostgreSQL 17