Extension-owned type schema mismatch causes false-positive diffs (pgvector, etc.)

[ayusssmaan]

Bug

When a column's type is owned by a PostgreSQL extension (e.g. pgvector's vector), pgschema plan/diff can report a spurious ALTER COLUMN TYPE (or, for brand-new tables, fail outright at apply time) purely because the extension resolves to a different schema between the live database and the plan-time comparison environment.

Example: a table has chunk_vector domain.vector(384) on the real/live database (the vector extension is installed into schema domain), but in the ephemeral plan-time comparison database the extension happens to install into public instead (e.g. because that database's default extension-install path differs). The type is identical in every way that matters — only the schema qualifier differs — but pgschema's schema-qualifier comparison treats public.vector and domain.vector as different types and emits a diff.

A related issue: for a brand-new CREATE TABLE with an extension-owned column type, pgschema currently trusts whatever schema the plan-time environment resolved the extension to, with no live side to catch the discrepancy — this can produce CREATE TABLE DDL referencing a schema-qualified type that does not exist on the real target, failing with e.g. ERROR: type "public.vector" does not exist.

Impact

Any schema using an extension-owned type (pgvector, hstore, ltree, citext, etc.) installed in a non-default schema will see:

• Non-convergent plan/diff output (a diff that never resolves via apply), or
• Outright apply failures on first-time creation of tables with such columns.

Fix

Opening a PR that:

• Tracks the owning extension (if any) for columns, composite-type attributes, and domain base types via pg_depend/pg_extension.
• When both sides of a comparison are owned by the same extension, strips schema qualifiers before comparing — while still catching genuine changes (e.g. a vector dimension bump from vector(384) to vector(512)).
• Ordinary cross-schema types (domains, enums, contrib types installed at a fixed, deliberate location) are unaffected — only extension-owned types get this treatment.

Along the way, also found and fixed a related pre-existing bug where columns referencing (but not owning) another table's sequence were misclassified as SERIAL, causing non-idempotent apply runs and incorrect table creation ordering for shared sequences.

Referenced from PR (to be linked).

[tianzhou]

Thanks for the detailed report @ayusssmaan.

After digging in, here's my read: the mechanism is real — column types are schema-qualified relative to the table's schema during inspection, so if an extension resolves to different schemas on the plan database vs the target, the two IRs genuinely compare as public.vector(384) vs domain.vector(384) and produce a spurious diff (or a CREATE TABLE referencing a schema that doesn't exist on the target).

That said, the scenario only arises when the plan database's extension is installed in a different schema than the target's. pgschema's contract is that the plan database mirrors the target (we already enforce an exact major-version match, and dumps schema-qualify extension types per #197). If the plan database mirrors the target's extension placement, plan/apply converges. So rather than making the diff engine extension-aware (which risks emitting unqualified or wrongly-qualified DDL depending on apply-time search_path), the right fix is to make the mismatch loud instead of silent.

That's what #544 does: when creating the external plan database provider, pgschema now queries pg_extension on both sides and fails fast if any extension installed on both databases lives in different schemas, e.g.:

extension schema mismatch: extension "vector" is installed in schema "public" on the plan database but schema "domain" on the target database; the plan database must mirror the target's extension schemas (use ALTER EXTENSION ... SET SCHEMA or reinstall with CREATE EXTENSION ... WITH SCHEMA), see https://www.pgschema.com/cli/plan-db

Both plan and apply share this path, so both are covered. Extensions present on only one side are unaffected — a missing extension already fails loudly with the existing hint.

On the SERIAL/shared-sequence misclassification you found along the way: that looks like a genuine but independent bug (the nextval() fallback in the sequence query conflates referencing a sequence with owning it). Could you open a separate issue for it with a standalone repro? Happy to look at it on its own merits.

Closing via #544.