Include Required Extensions in Dump Output

[NFUChen]

Problem

When a schema uses features provided by PostgreSQL extensions (e.g., btree_gist for EXCLUDE constraints with non-default GiST operator classes), pgschema's dump command doesn't include CREATE EXTENSION statements in its output.

This causes the plan command to fail when applying the dumped SQL to a temporary database:

Error for schema public: failed to apply desired state: failed to apply schema SQL to temporary schema pgschema_tmp_20260516_145911_5bf990fb: ERROR: data type uuid has no default operator class for access method "gist" (SQLSTATE 42704)

Example

Schema with an EXCLUDE constraint using btree_gist:

CREATE TABLE IF NOT EXISTS subscription_charges (
    id uuid,
    subscription_id uuid NOT NULL,
    start_charge_time date NOT NULL,
    end_charge_time date NOT NULL,
    -- ... other columns ...
    CONSTRAINT no_overlap_per_subscription EXCLUDE USING gist (
        subscription_id WITH =,
        daterange(start_charge_time, end_charge_time, '[]'::text) WITH &&
    )
);

The WITH = operator on a UUID column in a GiST index requires btree_gist extension. Without CREATE EXTENSION IF NOT EXISTS btree_gist; in the dump output, the plan command fails.

Expected Behavior

The dump command should detect extensions that schema objects depend on and include CREATE EXTENSION IF NOT EXISTS <extname>; statements at the top of the dump output, before any schema object definitions.

Expected dump output:

--
-- pgschema database dump
--

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE IF NOT EXISTS subscription_charges (
    ...
);

Scope

• Only include extensions that are actually used by objects in the target schema (detected via pg_depend)
• Exclude built-in extensions like plpgsql
• Applies to common extensions: btree_gist, btree_gin, pgvector, citext, ltree, hstore, pg_trgm, uuid-ossp, postgis, etc.

Affected Commands

• dump: Should include CREATE EXTENSION in output
• plan: Should work correctly when the dumped SQL includes extension statements
• diff: Should handle extension differences between old and new state

Technical Notes

Extension dependencies can be detected via PostgreSQL system catalogs:

• pg_depend tracks dependencies between objects
• pg_extension lists installed extensions
• Extension-provided objects (operator classes, types, functions) are marked with deptype = 'e' (extension member) in pg_depend

The dependency chain: schema_object → (depends on) → extension_member → (member of) → extension

[NFUChen]

Opening #437 to address this.