Repository navigation
Strengthen ALTER EXTENSION UPDATE safety (tracking) #93
Description
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_privswas 319 rows fresh vs 324 updated; it is now 336 in both, andpg_dumpoutput differs only by its per-dump\restrictnonce. - D2 —
bin/structural_diffnever 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_diffmisses 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_aclstate, 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__usageis created inside aDOblock;CREATE ROLEfires no event trigger and a role is not an extension member, so nothing sees it. Approach: compare global objects viapg_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 viapg_describe_objectwith expected output frommake results. Rides the existing suite in fresh, update and post-pg_upgrademodes 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.
Activity
jnasbyupgrade commentedon Sep 9, 2026
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 rproduces exactly oneddl_command_endrow withclassid,objidandobject_identityall NULL.GRANT … ON ALL TABLES IN SCHEMAlikewise produces one anonymous row — you cannot even recover the cardinality. Probe output is byte-identical between 12.22 and 17.10.CREATE ROLEdoes not fire an event trigger at all, including when issued through dynamic SQL inside aDOblock. 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 byEXECUTEinside a helper function. Whatever a parser struggles with here, PostgreSQL sees for free.current_query()is useless insideCREATE EXTENSION— every trigger invocation returns the literal stringCREATE EXTENSION cat_tools.GET DIAGNOSTICS … PG_CONTEXTdoes 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 throughEXECUTE/SPI and none of the literal ones. All 14CREATE TYPEs and the ADP statement are literal, so it contributes nothing to the case that matters most.For an extension, plain
pg_dumpsees privilege divergence and essentially nothing else.checkExtensionMembership()restricts extension members toDUMP_COMPONENT_ACL. Structural divergence (dropped-column tombstones,NOT VALID, stale comments) needs--binary-upgradeor 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_diffcaught 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::SplitStatementis 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 ofstructural_diff, not a replacement.squawklints a single file for risky DDL with no two-file diff.migra,pgquarrelandpg-schema-diffall 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 install0.49s, freshCREATE EXTENSION0.053s, install-at-0.2.2-plus-update 0.055s,structural_diff compare0.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_queryviapglastworks 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 requiringpip installin CI, and it loses the property that the tool works from a releasedgit archivetarball. 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.3view rebuild re-issues privileges deliberately.sql/cat_tools--0.2.2--0.2.3.sql.indoesDROP VIEW+CREATE VIEWon thepg_class_v/pg_attribute_v/columnchain, which destroys ACLs, comments, security labels and thepg_init_privsrow 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-GRANTwould silently lose privileges.__cat_tools.create_functionalways restates the full options string, soCREATE OR REPLACE FUNCTIONcannot silently resetprosecdef/proparallel/procost/proconfigto defaults through the helper. A hand-writtenCREATE OR REPLACE FUNCTIONin a future update script could hit that;pg_get_functiondefwould 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_diffas written:ALTER … OWNER TOrewritesrelaclviaaclnewownerwithout re-recordingpg_init_privs, leaving it permanently stale. A second confirmed way to desynchronise that catalog.t_expr_idxvst_expr_idx1), so any creation-order difference permanently renames objects.ALTER TABLE ADD COLUMN … DEFAULT <non-volatile>stores the evaluated default inpg_attribute.atthasmissing/attmissingval;CREATE TABLEwith the same clause never does.relaclmeans "owner default", and no sequence ofREVOKEs returns it to NULL once aGRANTmaterialises it.NOT VALIDleavesconvalidated = falsepermanently. Sharper variant — aUNIQUEconstraint that succeeds on an empty fresh table can fail on an update path where rows already exist.last_value,is_called) diverges whenever the update path has seeded rows.pg_attribute.attacl) are independent of table-level ones and are never subsumed by a later table-level grant.pg_classhint flags:relhassubclassstays true after every child is dropped;relhasindexis only cleared byVACUUM.ADD MAPPINGfails with a raw unique-violation on an update path;ALTER MAPPINGworks on both.ALTER SYSTEMis 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 commentedon Sep 9, 2026
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 tocat_tools__usage, and #88 is about the fact that the install script'sCREATE ROLE cat_tools__usage NOLOGINrequiresCREATEROLEand 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:
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.Finish the text-first update-script checker and wire it into CI