Part of #93 (defect D2 there).
bin/structural_diff never compares the cat_tools schema itself — not its ACL, not its comment. Every install script runs GRANT USAGE ON SCHEMA cat_tools TO cat_tools__usage, so if an update script ever failed to preserve that, structural_diff would report the two databases identical.
Why it is missed
bin/structural_diff.sql's members CTE enumerates extension members the usual way, via pg_depend with refclassid = 'pg_extension' and deptype = 'e'.
The cat_tools schema is not a member. cat_tools.control declares schema = 'cat_tools', so the schema is created by the CREATE EXTENSION machinery rather than by the script, and PostgreSQL records the relationship in the opposite direction — the extension depends on the schema, not the schema on the extension:
classid | objid | refclassid | refobjid | deptype
pg_extension | 2563251 | pg_namespace | 2563250 | n
So the members CTE, which looks for rows where the extension is the referenced object, never sees it. Note _cat_tools IS a member and IS compared — it is created by the script — which is why this went unnoticed.
Demonstration
In two databases that are otherwise identical, revoking USAGE on schema cat_tools from cat_tools__usage in one of them leaves structural_diff reporting IDENTICAL. Doing the same to the member schema _cat_tools is correctly reported:
-ACL: cat_tools__usage=U/root,root=UC/root
+ACL: root=UC/root
FAIL: structural diff between 'sch_a' and 'sch_b' …
Fix
Extend the members CTE to include the extension's own schema. Either direction works — reading extnamespace from pg_extension directly, or picking up the inverted pg_depend row shown above. The pg_depend form is the more general expression of the relationship; extnamespace is simpler. Whichever is chosen, the schema then flows through the existing per-kind rendering and gets its ACL and comment compared like any other member.
Small and self-contained — no dependency on other work under #93.
Part of #93 (defect D2 there).
bin/structural_diffnever compares thecat_toolsschema itself — not its ACL, not its comment. Every install script runsGRANT USAGE ON SCHEMA cat_tools TO cat_tools__usage, so if an update script ever failed to preserve that,structural_diffwould report the two databases identical.Why it is missed
bin/structural_diff.sql'smembersCTE enumerates extension members the usual way, viapg_dependwithrefclassid = 'pg_extension'anddeptype = 'e'.The
cat_toolsschema is not a member.cat_tools.controldeclaresschema = 'cat_tools', so the schema is created by theCREATE EXTENSIONmachinery rather than by the script, and PostgreSQL records the relationship in the opposite direction — the extension depends on the schema, not the schema on the extension:So the
membersCTE, which looks for rows where the extension is the referenced object, never sees it. Note_cat_toolsIS a member and IS compared — it is created by the script — which is why this went unnoticed.Demonstration
In two databases that are otherwise identical, revoking
USAGEon schemacat_toolsfromcat_tools__usagein one of them leavesstructural_diffreporting IDENTICAL. Doing the same to the member schema_cat_toolsis correctly reported:Fix
Extend the
membersCTE to include the extension's own schema. Either direction works — readingextnamespacefrompg_extensiondirectly, or picking up the invertedpg_dependrow shown above. Thepg_dependform is the more general expression of the relationship;extnamespaceis simpler. Whichever is chosen, the schema then flows through the existing per-kind rendering and gets its ACL and comment compared like any other member.Small and self-contained — no dependency on other work under #93.