DEFERRABLE INITIALLY DEFERRED on UNIQUE constraints is silently stripped during dump, plan, and apply

[ftheo]

Description

DEFERRABLE INITIALLY DEFERRED on UNIQUE constraints is parsed (it's in the syntax docs) but silently dropped at every stage:

1. dump — strips DEFERRABLE INITIALLY DEFERRED from output
2. plan — does not detect the difference between a deferrable and non-deferrable constraint
3. apply — strips DEFERRABLE INITIALLY DEFERRED when creating a new table

Why it matters

We use DEFERRABLE INITIALLY DEFERRED on unique constraints so that batch UPDATE ... SET col = CASE WHEN ... statements can swap unique values between rows without triggering a duplicate key error. Without it, PostgreSQL checks the constraint row-by-row and fails even though the final state is valid.

Reproduction

I've attached a self-contained script (pgschema_deferrable_bug.sh) that demonstrates all three issues. It creates a test schema, runs dump, plan, and apply, and reports the results.

To run:

PGUSER=postgres PGPASSWORD=postgres PGDATABASE=postgres ./pgschema_deferrable_bug.sh

Output on pgschema v1.9.0, PostgreSQL 16.3:

Full output

════════════════════════════════════════════════════════
pgschema DEFERRABLE bug reproduction
════════════════════════════════════════════════════════

▶ Step 1: Create schema and table WITHOUT DEFERRABLE
✓ Table created: deferrable_test.items with non-deferrable UNIQUE constraint

▶ Step 2: pgschema dump (baseline — no DEFERRABLE expected)
Output:
--
-- pgschema database dump
--

-- Dumped from database version PostgreSQL 18.3
-- Dumped by pgschema version 1.9.0


--
-- Name: items; Type: TABLE; Schema: -; Owner: -
--

CREATE TABLE IF NOT EXISTS items (
    id varchar(6),
    code varchar(32) NOT NULL,
    CONSTRAINT items_pkey PRIMARY KEY (id),
    CONSTRAINT uq_code UNIQUE (code)
);

▶ Step 3: Create target SQL file WITH DEFERRABLE INITIALLY DEFERRED
Target SQL:
CREATE TABLE IF NOT EXISTS items (
id VARCHAR(6),
code VARCHAR(32) NOT NULL,
CONSTRAINT items_pkey PRIMARY KEY (id),
CONSTRAINT uq_code UNIQUE (code) DEFERRABLE INITIALLY DEFERRED
);

▶ Step 4: pgschema plan — should detect DEFERRABLE change
Output:
No changes detected.

⚠ BUG: pgschema reports 'No changes detected' even though the live DB
has a non-deferrable constraint and the target SQL has DEFERRABLE.

▶ Step 5: Drop table, then apply target SQL (new table creation)
✓ Table dropped

Applying target SQL via pgschema apply...
Plan: 1 to add.

Summary by type:
  tables: 1 to add

Tables:
  + items

DDL to be executed:
--------------------------------------------------

CREATE TABLE IF NOT EXISTS items (
    id varchar(6),
    code varchar(32) NOT NULL,
    CONSTRAINT items_pkey PRIMARY KEY (id),
    CONSTRAINT uq_code UNIQUE (code)
);

Applying changes...
Set search_path to: deferrable_test, public

Executing group 1/1...
  Executing 1 statements in implicit transaction
Changes applied successfully!

▶ Step 6: Check if DEFERRABLE was applied in the database
Query: pg_constraint.condeferrable for uq_code
condeferrable = f
✗ BUG: DEFERRABLE was NOT applied — constraint is NOT deferrable

▶ Step 7: pgschema dump after apply
Output:
--
-- pgschema database dump
--

-- Dumped from database version PostgreSQL 18.3
-- Dumped by pgschema version 1.9.0


--
-- Name: items; Type: TABLE; Schema: -; Owner: -
--

CREATE TABLE IF NOT EXISTS items (
    id varchar(6),
    code varchar(32) NOT NULL,
    CONSTRAINT items_pkey PRIMARY KEY (id),
    CONSTRAINT uq_code UNIQUE (code)
);

⚠ Note: Check if DEFERRABLE INITIALLY DEFERRED appears in dump output
but is missing.

════════════════════════════════════════════════════════
Summary
════════════════════════════════════════════════════════

1. pgschema dump strips DEFERRABLE from UNIQUE constraints
2. pgschema plan does not detect DEFERRABLE differences
3. pgschema apply does not preserve DEFERRABLE on new tables

Expected: DEFERRABLE INITIALLY DEFERRED should be tracked,
diffed, and preserved — matching the syntax docs.
════════════════════════════════════════════════════════

Expected behavior

• dump should include DEFERRABLE INITIALLY DEFERRED in the constraint definition
• plan should detect when a constraint's deferrable property differs between the target SQL and the live database
• apply should preserve DEFERRABLE INITIALLY DEFERRED when creating or altering tables

pgschema_deferrable_bug.sh