Background
bin/structural_diff.sql (added in PR #55, https://github.com/Postgres-Extensions/cat_tools/blob/master/bin/structural_diff.sql)
enumerates every object belonging to a given extension via pg_depend
(deptype = 'e', the same relationship \dx+ uses) and renders a
kind-appropriate structural definition for each one: pg_get_functiondef/
pg_get_viewdef for routines/views, an ordered column dump for a plain table
or standalone composite type, an ordered label list for enums, comments via
obj_description(), and ACLs via whichever ACL column the object's catalog
has (proacl/typacl/relacl/nspacl). It's deliberately generic over
object kind — anything it doesn't have a specific renderer for still gets
listed via its pg_describe_object() identity, comment, and ACL, rather than
being silently skipped.
That tool is test-only plumbing (a psql -v extname=... -f script used to diff
a database reached via extension UPDATE against a fresh install of the same
version). But the underlying capability — "list everything a given extension
owns, with each object's actual definition" — is exactly the kind of catalog
introspection cat_tools already exists to provide (see cat_tools.pg_extension_v,
which wraps pg_extension itself but doesn't enumerate member objects at all).
Proposal
Add a real, user-facing cat_tools feature — a function and/or view, generalized
over ANY extension name (not hardcoded to cat_tools itself) — that lists every
object owned by a given extension along with a structural rendering of its
definition. Useful beyond testing: auditing what an extension actually
installed, generating documentation, diffing across environments, or just
answering "what does this extension actually contain" without hand-rolling a
pg_depend query every time.
Open questions for whoever picks this up
- Interface shape: a single wide view/function returning one row per object
with a kind + rendered-definition column, vs. something closer to
structural_diff.sql's per-kind branching exposed as multiple narrower
functions. Should probably follow whatever's more consistent with cat_tools'
existing conventions (__cat_tools-prefixed helpers wrapped by public
functions/views, see omit_column/create_function for precedent).
- How much of
structural_diff.sql's logic can be reused/shared directly
(it's a .sql script run via psql -f, not SQL callable from within the
extension itself) vs. needs reimplementing as proper CREATE FUNCTIONs.
- Whether ACL/comment rendering as plain text (as
structural_diff.sql does,
optimized for diffing) is the right output shape for a user-facing feature,
or whether a more structured (e.g. composite/array) return type serves
actual users better than a diff tool.
Background
bin/structural_diff.sql(added in PR #55, https://github.com/Postgres-Extensions/cat_tools/blob/master/bin/structural_diff.sql)enumerates every object belonging to a given extension via
pg_depend(
deptype = 'e', the same relationship\dx+uses) and renders akind-appropriate structural definition for each one:
pg_get_functiondef/pg_get_viewdeffor routines/views, an ordered column dump for a plain tableor standalone composite type, an ordered label list for enums, comments via
obj_description(), and ACLs via whichever ACL column the object's cataloghas (
proacl/typacl/relacl/nspacl). It's deliberately generic overobject kind — anything it doesn't have a specific renderer for still gets
listed via its
pg_describe_object()identity, comment, and ACL, rather thanbeing silently skipped.
That tool is test-only plumbing (a
psql -v extname=... -fscript used to diffa database reached via extension UPDATE against a fresh install of the same
version). But the underlying capability — "list everything a given extension
owns, with each object's actual definition" — is exactly the kind of catalog
introspection cat_tools already exists to provide (see
cat_tools.pg_extension_v,which wraps
pg_extensionitself but doesn't enumerate member objects at all).Proposal
Add a real, user-facing cat_tools feature — a function and/or view, generalized
over ANY extension name (not hardcoded to cat_tools itself) — that lists every
object owned by a given extension along with a structural rendering of its
definition. Useful beyond testing: auditing what an extension actually
installed, generating documentation, diffing across environments, or just
answering "what does this extension actually contain" without hand-rolling a
pg_dependquery every time.Open questions for whoever picks this up
with a kind + rendered-definition column, vs. something closer to
structural_diff.sql's per-kind branching exposed as multiple narrowerfunctions. Should probably follow whatever's more consistent with cat_tools'
existing conventions (
__cat_tools-prefixed helpers wrapped by publicfunctions/views, see
omit_column/create_functionfor precedent).structural_diff.sql's logic can be reused/shared directly(it's a
.sqlscript run viapsql -f, not SQL callable from within theextension itself) vs. needs reimplementing as proper
CREATE FUNCTIONs.structural_diff.sqldoes,optimized for diffing) is the right output shape for a user-facing feature,
or whether a more structured (e.g. composite/array) return type serves
actual users better than a diff tool.