pg_upgrade broken for DBs updated 0.2.0/0.2.1 → 0.2.2 (omit_column uses != ANY instead of != ALL)

[jnasbyupgrade]

Summary

A database that installed cat_tools 0.2.0 or 0.2.1 on PostgreSQL < 12 and then ran ALTER EXTENSION cat_tools UPDATE TO '0.2.2' (or '0.2.3') carries a _cat_tools.pg_class_v whose stored definition still references relhasoids/relhaspkey. Binary pg_upgrade of such a cluster to PG 12+ then fails in pg_restore with column c.relhasoids does not exist. Fresh installs of 0.2.2/0.2.3 are NOT affected — only the update path.

So the 0.2.2 "pg_upgrade compatibility" re-release fixed only fresh installs, not databases upgraded from 0.2.0/0.2.1.

Root cause

The __cat_tools.omit_column helper defined inside the update scripts sql/cat_tools--0.2.0--0.2.2.sql.in and sql/cat_tools--0.2.1--0.2.2.sql.in filters with attname != ANY(omit) instead of attname != ALL(omit).

x != ANY(array) is true whenever x differs from at least one element, so with a multi-element omit array (e.g. array['oid','relhasoids','relhaspkey']) it excludes nothing. The single-element default array['oid'] still works by coincidence ('oid' != ANY(array['oid']) is false), which is why it went unnoticed. The fresh-install scripts (cat_tools.sql.in, cat_tools--0.2.2.sql.in, cat_tools--0.3.0.sql.in) correctly use != ALL.

Chain: 0.2.0 install freezes c.* (incl. relhasoids/relhaspkey, present on PG10) into pg_class_v; the 0.2.0→0.2.2 update recreates the view via CREATE OR REPLACE VIEW with the buggy omit_column, which returns the full column list, so the recreate is a no-op (no error, columns retained); pg_upgrade to PG12+ then fails restoring the frozen definition.

The same != ANY no-op also silently defeats the other multi-column omits in those two scripts (pg_attribute_v, extension views).

Scope

• Buggy: sql/cat_tools--0.2.0--0.2.2.sql.in, sql/cat_tools--0.2.1--0.2.2.sql.in — present in released tags 0.2.2 and 0.2.3.
• sql/cat_tools--0.2.2--0.2.3.sql.in does not touch pg_class_v, so 0.2.3 does not repair an already-broken view.
• 0.2.2 was published to PGXN → affected DBs may exist in the field. 0.2.3 was tagged but never published.

Fix (not a one-liner)

Flipping != ANY → != ALL alone is necessary but converts the silent no-op into a hard cannot drop columns from view error (CREATE OR REPLACE VIEW cannot drop mid-list columns). So pg_class_v recreation must become DROP + CREATE down the dependency chain (pg_class_v → pg_attribute_v → column → cat_tools.* wrapper views → pg_class__get function), reverse dependency order, no CASCADE, grants restored — mirroring what the scripts already do for _cat_tools.column.

Remediation plan:

1. Fix omit_column (ANY→ALL) + DROP/CREATE pg_class_v chain in 0.2.0--0.2.2 and 0.2.1--0.2.2 (corrects future 0.2.0/0.2.1 → 0.2.2 updates).
2. Make 0.2.2--0.2.3 force-rebuild pg_class_v so already-broken published-0.2.2 DBs are repaired on update to 0.2.3.
3. Re-roll 0.2.3 (unpublished; move the tag) with the above — or cut 0.2.4. Decision pending.
4. Final step of that release: flip master to the stable pseudo-version.

Prevention

The bridge-journey pg-upgrade matrix leg added on the new_functions branch (#16) — install old version on old PG, bridge-update to the intermediate, binary pg_upgrade, then update to current — is what exposed this. Keep exercising the 0.2.0/0.2.1 → current → pg_upgrade path so it cannot regress.

Status

Diagnosis complete; fix intentionally on hold pending the re-roll-vs-0.2.4 and field-repair decisions.

[jnasbyupgrade]

Not tracking this as an issue — moving it to an internal working TODO instead. Closing.