Strengthen ALTER EXTENSION UPDATE safety (tracking)

[jnasbyupgrade]

Tracking issue for one question: does ALTER EXTENSION UPDATE reliably produce the same database as a fresh CREATE EXTENSION?

All work carries the update-safety label. Sub-issues are linked below.

Scope: this is forward-looking

Released update scripts are locked and will not be touched barring something serious. The pair that matters is the current development cycle — sql/cat_tools--<last-released>.sql.in → sql/cat_tools.sql.in, with sql/cat_tools--<last-released>--stable.sql.in as the accumulator every SQL-touching PR extends. That accumulator exists and is tracked. Checks are run against historical pairs to validate the checks themselves, which is useful, but protecting those pairs is not the goal and findings against them are not blockers.

Corollary for tooling: false positives are cheap. Future update scripts can be required to satisfy whatever rule we adopt, so a check that occasionally needs a one-line waiver is fine. Silent failure — reporting green while having understood nothing — is not.

Separation of concerns

Changes that add or alter cat_tools features get their own PRs, separate from work that only improves CI and checking. Where a check would be more useful as a user-facing function than as test scaffolding, prefer the function — but weigh ongoing maintenance and testing cost before committing to that.

Choosing the right mechanism per case

The governing principle for everything below: think hard about the right way to verify each individual case. Use a parser where parsing is genuinely easy. Where it is not, the answer is a different mechanism — not a cleverer parser. A case that is awkward to check by text comparison is positive evidence that it belongs somewhere else, and declining to handle it in a static checker is the correct outcome rather than a coverage gap to apologize for.

Worked example: enum contents. The install script states a full label list in CREATE TYPE … AS ENUM (…); an update script says ALTER TYPE … ADD VALUE, one label at a time. Teaching a static checker that those two statement forms mean the same change is exactly the kind of special-casing that makes such a tool grow without bound. A test asserting each enum's contents is both simpler and stronger — the suite already runs identical expected output against fresh, updated and pg_upgraded databases, so a disagreement fails on its own, in whichever mode diverges. Enum support was therefore dropped from #92 and replaced by #97.

Status

• D1 — privilege divergence between fresh and updated installs: fixed by Converge privileges between a fresh install and an updated one #96. pg_init_privs was 319 rows fresh vs 324 updated; it is now 336 in both, and pg_dump output differs only by its per-dump \restrict nonce.
• D2 — bin/structural_diff never compares the extension's own schema: open, see structural_diff: cover the extension's own schema #94.
• Add bin/update_lint: static check that the update script covers the object diff #90 (semantic parser, 1188 code lines) closed as the wrong trade — it was structurally blind to definition drift, which is the bug class that matters most here.
• Static check that the update script covers this cycle's install-script changes #92 (text-first, ~204 code lines) being finished as the static check.

Known limitation to document

A fresh install and an updated install can have identical runtime behaviour while differing in catalog state that pg_dump cares about. D1 was exactly this: typacl byte-identical and has_type_privilege true on both sides, yet five pg_init_privs rows differed, because ALTER DEFAULT PRIVILEGES grants implicitly and implicit grants are not snapshotted.

That means our checks are imperfect in a specific, nameable way, and that should be written down rather than discovered again. bin/structural_diff compares per-object ACL columns and reported these databases identical. A text-level check cannot see it at all. Only a pg_init_privs comparison or a pg_dump diff catches it.

Plan

Privilege correctness

ALTER DEFAULT PRIVILEGES stays. It is not merely documentation — it actively enforces the default, and removing it would break restore for already-updated databases whose dumps carry no explicit GRANT. Explicit REVOKE/GRANT normalization (landed in #96) is the second half of a belt-and-suspenders arrangement, not a replacement: ADP enforces the default, normalization guarantees the explicit end state.

Remaining: detect privilege drift directly (#95), so normalization is defence-in-depth rather than the only thing standing between us and drift. Deferred for now — the immediate priority was getting the two paths in sync, which #96 did. A pg_init_privs comparison must land after #96 or it fires on the divergence #96 fixed.

The ADP invariant check — for every pg_default_acl entry, assert every existing object of that class/schema/owner already carries the privilege — is worth building as a real cat_tools feature rather than test scaffolding, since it is useful against any database including production, and needs no superuser. Feature PR, separate from CI work. Weigh maintenance cost first.

Detection gaps

• structural_diff: cover the extension's own schema #94 — bin/structural_diff misses the extension's own schema. Small, independent, actionable now.
• Detect privilege drift directly, so permission normalization is defence-in-depth #95 — privilege drift detection (pg_init_privs, pg_default_acl state, column-level ACLs).
• update_lint: compare object definitions, not just identity #91 — compare definitions, not just identity. Largely answered by Static check that the update script covers this cycle's install-script changes #92.
• Roles — cat_tools__usage is created inside a DO block; CREATE ROLE fires no event trigger and a role is not an extension member, so nothing sees it. Approach: compare global objects via pg_dumpall, which needs brand-new clusters per side.
• Leaked objects — created by a script and left behind as non-members. Detectable via event-trigger DDL capture during CREATE EXTENSION; nothing else covers it. Note this needs superuser, so it is a testing tool only and cannot be part of a non-superuser install path.
• Object inventory — a test/sql/ file dumping extension members via pg_describe_object with expected output from make results. Rides the existing suite in fresh, update and post-pg_upgrade modes for free, and makes object additions visible in the PR diff for human review.

Release-time gate

The only moment this truly matters is a release, since that is when a version's contents are locked. A heavier, partly interpretive check is acceptable there and need not be cheap enough for every PR: pg_dump comparison of fresh vs updated across every supported update origin, pg_init_privs included, plus review of the update script against the inter-release diff. A dump comparison alone would have caught D1 with no new tooling.

Static early-warning check

#92, kept deliberately simple. Its value is catching small, subtle diffs — a changed function body, a changed grant, a tweaked view definition. Whole new objects are comparatively easy to spot unaided in a diff of the install script, so the check does not need to chase them and should not grow machinery to do so. Enum labels are explicitly out of scope; see the principle above and #97.

Related work, tracked separately

#88 (non-superuser install, superuser = off) constrains the permission model — see the comment below for the constraints adopted here.

[jnasbyupgrade]

Highlights from the exploration that produced the plan above, including the negative results — several ideas that sound good were tested and don't work, and that's worth recording so they aren't re-proposed.

PostgreSQL behaviour established empirically (PG12 and PG17 unless noted)

Event triggers fire on GRANT/REVOKE/ALTER DEFAULT PRIVILEGES, but carry no object identity. GRANT USAGE ON TYPE s.e, s.e2 TO r produces exactly one ddl_command_end row with classid, objid and object_identity all NULL. GRANT … ON ALL TABLES IN SCHEMA likewise produces one anonymous row — you cannot even recover the cardinality. Probe output is byte-identical between 12.22 and 17.10.

CREATE ROLE does not fire an event trigger at all, including when issued through dynamic SQL inside a DO block. Roles are global objects. This is why role coverage needs a separate mechanism.

Objects created through dynamic SQL are fully visible to event triggers, with complete identity — including the four views built via __cat_tools.exec(format($fmt$ … $fmt$)) and objects created by EXECUTE inside a helper function. Whatever a parser struggles with here, PostgreSQL sees for free.

current_query() is useless inside CREATE EXTENSION — every trigger invocation returns the literal string CREATE EXTENSION cat_tools. GET DIAGNOSTICS … PG_CONTEXT does recover fully-expanded statement text (format() substituted, dynamic SQL expanded), but coverage is inverted relative to what a parser needs: 300 of 408 commands, comprising exactly the statements that went through EXECUTE/SPI and none of the literal ones. All 14 CREATE TYPEs and the ADP statement are literal, so it contributes nothing to the case that matters most.

For an extension, plain pg_dump sees privilege divergence and essentially nothing else. checkExtensionMembership() restricts extension members to DUMP_COMPONENT_ACL. Structural divergence (dropped-column tombstones, NOT VALID, stale comments) needs --binary-upgrade or a catalog diff.

Ideas tested and rejected

Ordered transcript comparison does not work. The theory was appealing: D1 is fundamentally an ordering bug (fresh runs ADP then CREATE TYPE; the update path creates the types first), so comparing ordered DDL transcripts should catch it with no semantic model. It does — in a toy reproduction, cleanly. On the real pair it collapses: 276 of 305 transcript lines differ, all legitimately, because an update script re-CREATE OR REPLACEs a subset of objects in a completely different order from a fresh install. The unordered multiset comparison is no better. The signal is real but buried under order differences inherent to what an update script is.

DDL capture is largely redundant with bin/structural_diff. Against five seeded bug classes, structural_diff caught everything DDL capture caught plus function bodies, comments and ACLs. DDL capture's one exclusive square is detecting an object created and left behind as a non-member — which nothing else covers, and which is why it survives in the plan for that purpose only.

SQL::SplitStatement is disqualified. It passes synthetic dollar-quote and nested-comment probes, then silently fuses 131 real statements into 40, one chunk containing 60 semicolons. Silent fusion is precisely the failure mode that makes a checking tool worse than useless.

No prior art exists. The only purpose-built extension-update checker (rjuju/pg_validate_extupgrade) requires a live database and is a peer of structural_diff, not a replacement. squawk lints a single file for risky DDL with no two-file diff. migra, pgquarrel and pg-schema-diff all need live databases. A 2020 pgsql-hackers thread names this exact gap and leaves it unfilled.

Measurements bearing on tool choice

The runtime check is not slow. Locally: make install 0.49s, fresh CREATE EXTENSION 0.053s, install-at-0.2.2-plus-update 0.055s, structural_diff compare 0.051s — about 0.7s total for a check strictly stronger than any static one. Its cost is a database and roughly a minute of CI latency, not wall-clock.

Update scripts already copy install-script statements nearly verbatim. Across all real pairs, 85% of added statements and 95% of added objects appear in the update script as copies, and 98% of those matches are byte-exact. Only comment stripping and whitespace collapse were needed to reach that; case folding and dollar-quote-tag canonicalisation add zero additional matches.

libpg_query via pglast works but forces leaving Perl. A 257-line Python port reproduced the object sets byte-identically across all 20 SQL sources and reproduced the flagship finding exactly. But there is no Perl binding, it is a 26 MB compiled dependency requiring pip install in CI, and it loses the property that the tool works from a released git archive tarball. Also, format() templates are not valid SQL and need placeholder substitution before parsing — it fails loudly rather than silently, but the current head-only parse is structurally immune.

Existing behaviour worth documenting rather than changing

The 0.2.2→0.2.3 view rebuild re-issues privileges deliberately. sql/cat_tools--0.2.2--0.2.3.sql.in does DROP VIEW + CREATE VIEW on the pg_class_v/pg_attribute_v/column chain, which destroys ACLs, comments, security labels and the pg_init_privs row along with the OID. The script re-issues every one explicitly. That is load-bearing, not incidental — a future edit adding a view to the drop chain without a matching re-GRANT would silently lose privileges.

__cat_tools.create_function always restates the full options string, so CREATE OR REPLACE FUNCTION cannot silently reset prosecdef/proparallel/procost/proconfig to defaults through the helper. A hand-written CREATE OR REPLACE FUNCTION in a future update script could hit that; pg_get_functiondef would catch it.

Other non-retroactive or state-carrying features to watch

None are used by cat_tools today, and none are visible to bin/structural_diff as written:

• ALTER … OWNER TO rewrites relacl via aclnewowner without re-recording pg_init_privs, leaving it permanently stale. A second confirmed way to desynchronise that catalog.
• Auto-generated object names carry order-dependent numeric suffixes (t_expr_idx vs t_expr_idx1), so any creation-order difference permanently renames objects.
• ALTER TABLE ADD COLUMN … DEFAULT <non-volatile> stores the evaluated default in pg_attribute.atthasmissing/attmissingval; CREATE TABLE with the same clause never does.
• ACLs are sticky at non-NULL: a NULL relacl means "owner default", and no sequence of REVOKEs returns it to NULL once a GRANT materialises it.
• Constraint validation state: NOT VALID leaves convalidated = false permanently. Sharper variant — a UNIQUE constraint that succeeds on an empty fresh table can fail on an update path where rows already exist.
• Sequence state (last_value, is_called) diverges whenever the update path has seeded rows.
• Column-level ACLs (pg_attribute.attacl) are independent of table-level ones and are never subsumed by a later table-level grant.
• Sticky pg_class hint flags: relhassubclass stays true after every child is dropped; relhasindex is only cleared by VACUUM.
• Text search: ADD MAPPING fails with a raw unique-violation on an update path; ALTER MAPPING works on both.
• Unordered catalog scans follow insertion order, which is directly relevant to this repo's pgTAP expected output since fresh and updated databases create objects in different sequences.
• ALTER SYSTEM is not transactional and survives rollback of a failed update script.

The same structural batch was re-run on PG12.22 with an identical divergence set to PG17.10, so nothing here is version-gated across the supported range.

[jnasbyupgrade]

Cross-cutting constraint: this work interacts with #88 (non-superuser install, superuser = off), which is being worked separately.

The overlap is the permission model itself. Permission normalization (plan item 2) issues GRANTs to cat_tools__usage, and #88 is about the fact that the install script's CREATE ROLE cat_tools__usage NOLOGIN requires CREATEROLE and so blocks non-superuser install. Building normalization on the assumption that the role always exists, and that the installer can always create it, would bake in the very assumption #88 aims to remove.

Design constraints adopted for the normalization work so it does not foreclose #88:

• It must not require superuser.
• It must tolerate the usage role being absent rather than failing, since a non-superuser install may not be able to create it.
• The grantee must come from one place, so that whatever Install script requires CREATEROLE, blocking non-superuser install even with superuser = false #88 settles on (a configurable role, an optional role, or something else) is a single-point change rather than a sweep through every GRANT.

Also worth noting for #88's benefit: two checks in the plan above need superuser and therefore cannot be part of a non-superuser install path, only of testing — event-trigger DDL capture (plan item 4) requires superuser to create the event trigger. The ADP invariant check (item 3) does not; it is a plain catalog query and can run as any role that can read pg_default_acl.