COMMENT ON COLUMN misresolved when a table shares the target schema name — breaks dump→plan round trip

[kinghuang]

Summary

When a table has the same name as the target schema, pgschema plan misresolves two-part COMMENT ON COLUMN names in the desired-state SQL. SQL semantics for COMMENT ON COLUMN x.y are <table>.<column>, but pgschema appears to strip a leading x. equal to the target schema as if it were a schema qualifier, producing an unqualified COMMENT ON COLUMN y that PostgreSQL rejects during the desired-state replay:

ERROR: column name must be qualified (SQLSTATE 42601)

The worst part: pgschema dump itself emits the failing form, so the dump → plan round trip is broken with no user-authored SQL involved. Dumping a schema catalog that contains a commented table catalog (single-file or --multi-file) produces COMMENT ON COLUMN catalog.title IS ..., which pgschema plan then cannot process.

(Unrelated to #501.)

Environment

• pgschema v1.11.1 (1.11.1@7efe9b83 darwin/arm64) — also reproduced on v1.11.0
• PostgreSQL 18.4 (postgres:18 Docker image, Debian 18.4-1.pgdg13+1)

Repro 1 — dump → plan round trip (no user-authored SQL)

docker run -d --name pg18 -e POSTGRES_PASSWORD=postgres -p 15432:5432 postgres:18

psql "host=localhost port=15432 user=postgres password=postgres dbname=postgres" <<'SQL'
CREATE SCHEMA catalog;
CREATE TABLE catalog.catalog (
    catalog_id uuid PRIMARY KEY,
    title text
);
COMMENT ON COLUMN catalog.catalog.title IS 'round trip';
SQL

pgschema dump --host localhost --port 15432 --user postgres --db postgres \
  --schema catalog > dump.sql

pgschema plan --host localhost --port 15432 --user postgres --db postgres \
  --schema catalog --file dump.sql

The dump contains:

CREATE TABLE IF NOT EXISTS catalog (
    catalog_id uuid,
    title text,
    CONSTRAINT catalog_pkey PRIMARY KEY (catalog_id)
);

COMMENT ON COLUMN catalog.title IS 'round trip';

and the plan on that dump fails:

Error: failed to apply desired state: failed to apply schema SQL to temporary schema pgschema_tmp_20260703_165751_3d9e62c4: ERROR: column name must be qualified (SQLSTATE 42601)

Same result with --multi-file (the emitted tables/catalog.sql has the same two-part COMMENT ON COLUMN). Since the state was created by pgschema dump itself, every schema that contains a commented table named after the schema currently has no working dump-based desired state.

Repro 2 — minimal hand-written desired state

schema.sql:

CREATE TABLE catalog (
    catalog_id uuid PRIMARY KEY,
    title text
);

COMMENT ON COLUMN catalog.title IS 'fails';

psql "host=localhost port=15432 user=postgres password=postgres dbname=postgres" \
  -c 'CREATE SCHEMA catalog;'

pgschema plan --host localhost --port 15432 --user postgres --db postgres \
  --schema catalog --file schema.sql

Error: failed to apply desired state: failed to apply schema SQL to temporary schema pgschema_tmp_20260703_165640_33424f94: ERROR: column name must be qualified (SQLSTATE 42601)

Expected behavior

COMMENT ON COLUMN catalog.title inside a desired state for --schema catalog should be treated as <table catalog>.<column title> (standard COMMENT ON COLUMN relation_name.column_name semantics — the object name for COMMENT ON COLUMN always ends in a column, so a two-part name is table.column, never schema.table). Instead the leading catalog. is stripped as if it were a schema qualifier, leaving a bare column name.

Workaround

Writing the fully qualified three-part form in the desired state works:

COMMENT ON COLUMN catalog.catalog.title IS 'works';

pgschema plan accepts it and normalizes the schema away in the emitted DDL:

Plan: 1 to add.
...
COMMENT ON COLUMN catalog.title IS 'works';

— but this requires hand-editing every dump, and the emitted/normalized form is again the two-part one, so the state file can't be round-tripped without re-editing.

Related check: COMMENT ON TABLE is fine

Two-part COMMENT ON TABLE catalog.catalog in the desired state works correctly (there the two-part name genuinely is schema.table, and the schema-qualifier stripping is appropriate). Only COMMENT ON COLUMN — where the part count is shifted by the trailing column name — is affected. Other COMMENT ON variants whose object name ends in a sub-object (e.g. constraints: COMMENT ON CONSTRAINT ... ON table) may deserve a look for the same table-named-like-schema edge case.