pg-delta: function replacement can over-recreate tables for default and check-constraint dependencies

[xmh1011]

Bug report

pg-delta can generate destructive DROP TABLE + CREATE TABLE migrations when replacing a function signature that is referenced only by a column default or a check constraint. The table itself does not need to be replaced; the dependent expression can be dropped/updated around the function replacement.

This was found during deep validation of current main.

• Affected package: pg-delta
• Tested commit: 9f01826b00b64e7f9628844ecb6f69a07f53c1f8
• PostgreSQL version: 17.6 via integration container
• Runtime: Bun 1.3.14
• Docker: 28.3.2
• OS: macOS/Darwin arm64
• Command shape: focused PG17 integration probe using createPlan

Scenario 1: function signature change referenced by a column default

Source state

CREATE SCHEMA probe_default;
CREATE FUNCTION probe_default.make_amount(value integer)
RETURNS integer
LANGUAGE sql
IMMUTABLE
AS $$ SELECT value $$;

CREATE TABLE probe_default.items (
  id integer PRIMARY KEY,
  amount integer DEFAULT probe_default.make_amount(1)
);

Target state

ALTER TABLE probe_default.items ALTER COLUMN amount DROP DEFAULT;
DROP FUNCTION probe_default.make_amount(integer);
CREATE FUNCTION probe_default.make_amount(value bigint)
RETURNS integer
LANGUAGE sql
IMMUTABLE
AS $$ SELECT value::integer $$;
ALTER TABLE probe_default.items
  ALTER COLUMN amount SET DEFAULT probe_default.make_amount(1::bigint);

Actual generated SQL

SET check_function_bodies = false;
DROP TABLE probe_default.items;
DROP FUNCTION probe_default.make_amount(value integer);
CREATE FUNCTION probe_default.make_amount(value bigint)
 RETURNS integer
 LANGUAGE sql
 IMMUTABLE
AS $function$ SELECT value::integer $function$;
CREATE TABLE probe_default.items (id integer NOT NULL, amount integer DEFAULT probe_default.make_amount((1)::bigint));
ALTER TABLE probe_default.items ALTER COLUMN amount SET DEFAULT probe_default.make_amount((1)::bigint);
ALTER TABLE probe_default.items ADD CONSTRAINT items_pkey PRIMARY KEY (id);

The DROP TABLE probe_default.items is not required and would destroy table data.

Expected SQL shape

SET check_function_bodies = false;
ALTER TABLE probe_default.items ALTER COLUMN amount DROP DEFAULT;
DROP FUNCTION probe_default.make_amount(value integer);
CREATE FUNCTION probe_default.make_amount(value bigint)
RETURNS integer
LANGUAGE sql
IMMUTABLE
AS $$ SELECT value::integer $$;
ALTER TABLE probe_default.items
  ALTER COLUMN amount SET DEFAULT probe_default.make_amount(1::bigint);

Scenario 2: function signature change referenced by a check constraint

Source state

CREATE SCHEMA probe_constraint;
CREATE FUNCTION probe_constraint.is_valid_amount(value integer)
RETURNS boolean
LANGUAGE sql
IMMUTABLE
AS $$ SELECT value > 0 $$;

CREATE TABLE probe_constraint.items (
  id integer PRIMARY KEY,
  amount integer NOT NULL,
  CONSTRAINT amount_is_valid CHECK (
    probe_constraint.is_valid_amount(amount)
  )
);

Target state

ALTER TABLE probe_constraint.items DROP CONSTRAINT amount_is_valid;
DROP FUNCTION probe_constraint.is_valid_amount(integer);
CREATE FUNCTION probe_constraint.is_valid_amount(value bigint)
RETURNS boolean
LANGUAGE sql
IMMUTABLE
AS $$ SELECT value > 0 $$;
ALTER TABLE probe_constraint.items
  ADD CONSTRAINT amount_is_valid CHECK (
    probe_constraint.is_valid_amount(amount::bigint)
  );

Actual generated SQL

SET check_function_bodies = false;
DROP TABLE probe_constraint.items;
DROP FUNCTION probe_constraint.is_valid_amount(value integer);
CREATE FUNCTION probe_constraint.is_valid_amount(value bigint)
 RETURNS boolean
 LANGUAGE sql
 IMMUTABLE
AS $function$ SELECT value > 0 $function$;
CREATE TABLE probe_constraint.items (id integer NOT NULL, amount integer NOT NULL);
ALTER TABLE probe_constraint.items ADD CONSTRAINT amount_is_valid CHECK (probe_constraint.is_valid_amount(amount::bigint));
ALTER TABLE probe_constraint.items ADD CONSTRAINT items_pkey PRIMARY KEY (id);

Again, the table replacement is unnecessary and destructive.

Root cause

This looks like an over-broad dependency promotion in the replacement cascade:

1. Replacing the function signature seeds expandReplaceDependencies from the dropped/recreated procedure stable id.
2. mainCatalog.depends correctly reports expression dependencies from PostgreSQL catalogs:
   • pg_attrdef default expression depends on the old function;
   • pg_constraint check expression depends on the old function.
3. The dependency walk reaches a column:* or constraint:* dependent.
4. normalizeDependentId promotes those ids to the owning table:*.
5. buildReplaceChanges then emits DropTable + CreateTable for the table.

That promotion is too destructive for expression dependents that PostgreSQL can update independently. In both repros, the table is only a container for the dependent expression; it does not need to be dropped.

Suggested fix direction

Treat expression dependents as targeted replacement/removal units before falling back to table replacement:

• For pg_attrdef / column-default dependencies, ensure the migration emits ALTER TABLE ... ALTER COLUMN ... DROP DEFAULT before the old function is dropped, then ALTER TABLE ... ALTER COLUMN ... SET DEFAULT ... after the new function exists.
• For check constraints, reuse the existing table-constraint changes (DROP CONSTRAINT, ADD CONSTRAINT) instead of promoting the owning table to DROP TABLE.
• Restrict column:* / constraint:* -> table:* normalization to cases where the owning table truly has to be replaced, or only after checking whether an existing targeted column/default/constraint change already resolves the dependency.
• Add integration coverage that asserts the generated plan contains no DROP TABLE for these cases and applies cleanly with existing rows preserved.

Local validation command

PGDELTA_SKIP_DUMMY_SECLABEL_BUILD=1 \
PGDELTA_TEST_POSTGRES_VERSIONS=17 \
TESTCONTAINERS_RYUK_DISABLED=true \
  ./node_modules/.bin/bun run --cwd packages/pg-delta test \
  --test-name-pattern "column default does not replace the table" \
  tests/integration/deep-scenario-probes.test.ts

The focused default and check-constraint probes both failed consistently after retries because the generated plan contained DROP TABLE.

[xmh1011]

Additional same-class repro from deeper testing: generated columns also hit the over-broad table replacement path.

This belongs to this issue rather than a new one because the root cause is the same: an expression dependency on a replaced function signature is normalized to the owning table, so pg-delta emits DROP TABLE even though a targeted expression update exists.

Scenario 3: function signature change referenced by a generated column

Source state

CREATE SCHEMA probe_generated;
CREATE FUNCTION probe_generated.double_amount(value integer)
RETURNS integer
LANGUAGE sql
IMMUTABLE
AS $$ SELECT value * 2 $$;

CREATE TABLE probe_generated.items (
  id integer PRIMARY KEY,
  amount integer NOT NULL,
  doubled integer GENERATED ALWAYS AS (
    probe_generated.double_amount(amount)
  ) STORED
);

Target state

The target is valid on PostgreSQL 17 when the new overload is created first, the generated expression is updated, and the old overload is dropped:

CREATE FUNCTION probe_generated.double_amount(value bigint)
RETURNS integer
LANGUAGE sql
IMMUTABLE
AS $$ SELECT (value * 2)::integer $$;

ALTER TABLE probe_generated.items
  ALTER COLUMN doubled SET EXPRESSION AS (
    probe_generated.double_amount(amount::bigint)
  );

DROP FUNCTION probe_generated.double_amount(integer);

Actual generated SQL

Current main generated:

SET check_function_bodies = false;
DROP TABLE probe_generated.items;
DROP FUNCTION probe_generated.double_amount(value integer);
CREATE FUNCTION probe_generated.double_amount(value bigint)
 RETURNS integer
 LANGUAGE sql
 IMMUTABLE
AS $function$ SELECT (value * 2)::integer $function$;
CREATE TABLE probe_generated.items (id integer NOT NULL, amount integer NOT NULL, doubled integer GENERATED ALWAYS AS (probe_generated.double_amount((amount)::bigint)) STORED);
ALTER TABLE probe_generated.items ALTER COLUMN doubled SET EXPRESSION AS (probe_generated.double_amount((amount)::bigint));
ALTER TABLE probe_generated.items ADD CONSTRAINT items_pkey PRIMARY KEY (id);

The DROP TABLE probe_generated.items is unnecessary and would destroy table data. The generated ALTER COLUMN doubled SET EXPRESSION AS ... also shows pg-delta already has a targeted representation for the generated expression change; it just gets combined with a destructive table replacement from the dependency-expansion path.

Boundary check

I also tested an expression index depending on a replaced function signature. That case passed: pg-delta only replaced the index and did not drop the table. So the over-replacement seems concentrated around expression dependencies that normalize to column:* / constraint:* and then to table:*, not around every expression-bearing dependent object.

Focused local command:

PGDELTA_SKIP_DUMMY_SECLABEL_BUILD=1 \
PGDELTA_TEST_POSTGRES_VERSIONS=17 \
TESTCONTAINERS_RYUK_DISABLED=true \
  ./node_modules/.bin/bun run --cwd packages/pg-delta test \
  --test-name-pattern "generated column does not replace the table" \
  tests/integration/deep-scenario-probes.test.ts

[xmh1011]

Additional same-root finding from the deep-validation pass: domain defaults also hit the over-broad replacement path when the default expression depends on a function whose signature changes.

I am adding this to #280 rather than opening a separate issue because the failure mode is the same class: a function-signature replacement reaches an expression dependency through pg_depend, and pg-delta promotes the owning object to a destructive replacement even though a targeted alter already exists.

Scenario 4: function signature change referenced by a domain default

Source state

CREATE SCHEMA probe_domain_default;
CREATE FUNCTION probe_domain_default.default_code(value integer)
RETURNS text
LANGUAGE sql
IMMUTABLE
AS $$ SELECT 'code-' || value::text $$;

CREATE DOMAIN probe_domain_default.code AS text
  DEFAULT probe_domain_default.default_code(1);

Target state

The target is valid with targeted domain-default changes:

ALTER DOMAIN probe_domain_default.code DROP DEFAULT;
DROP FUNCTION probe_domain_default.default_code(integer);

CREATE FUNCTION probe_domain_default.default_code(value bigint)
RETURNS text
LANGUAGE sql
IMMUTABLE
AS $$ SELECT 'code-' || value::text $$;

ALTER DOMAIN probe_domain_default.code
  SET DEFAULT probe_domain_default.default_code(1::bigint);

Actual generated SQL

Current main (9f01826b00b64e7f9628844ecb6f69a07f53c1f8) generated:

SET check_function_bodies = false;
DROP DOMAIN probe_domain_default.code;
DROP FUNCTION probe_domain_default.default_code(value integer);
CREATE FUNCTION probe_domain_default.default_code(value bigint)
 RETURNS text
 LANGUAGE sql
 IMMUTABLE
AS $function$ SELECT 'code-' || value::text $function$;
CREATE DOMAIN probe_domain_default.code AS text DEFAULT probe_domain_default.default_code((1)::bigint);
ALTER DOMAIN probe_domain_default.code SET DEFAULT probe_domain_default.default_code((1)::bigint);

The DROP DOMAIN is unnecessary, and the trailing ALTER DOMAIN ... SET DEFAULT shows the diff already has a targeted representation for the default update; it is being combined with a destructive replacement.

More severe variant: table column using the domain

When an existing table column uses that domain, the replacement expansion goes further and recreates the table too:

CREATE TABLE probe_domain_default_table.items (
  id integer PRIMARY KEY,
  code probe_domain_default_table.code NOT NULL
);

Actual generated SQL included:

SET check_function_bodies = false;
DROP TABLE probe_domain_default_table.items;
DROP DOMAIN probe_domain_default_table.code;
DROP FUNCTION probe_domain_default_table.default_code(value integer);
CREATE FUNCTION probe_domain_default_table.default_code(value bigint)
 RETURNS text
 LANGUAGE sql
 IMMUTABLE
AS $function$ SELECT 'code-' || value::text $function$;
CREATE DOMAIN probe_domain_default_table.code AS text DEFAULT probe_domain_default_table.default_code((1)::bigint);
ALTER DOMAIN probe_domain_default_table.code SET DEFAULT probe_domain_default_table.default_code((1)::bigint);
CREATE TABLE probe_domain_default_table.items (id integer NOT NULL, code probe_domain_default_table.code NOT NULL);
ALTER TABLE probe_domain_default_table.items ADD CONSTRAINT items_pkey PRIMARY KEY (id);

This turns a domain default change into table data loss risk.

Boundary check

A similar domain CHECK constraint probe passed locally: pg-delta used targeted domain constraint/default ordering and did not replace the domain. So this looks concentrated on domain default expression dependencies, not every domain expression dependency.

Root cause direction

expandReplaceDependencies can resolve domain:* as a replacement target. For a domain default expression that depends on the replaced function, the cascade currently promotes the surviving domain to DropDomain + CreateDomain, then follows the domain type dependency to any table column using it.

That should be treated like the table default/check/generation cases already described in this issue: if the domain itself survives and diffDomains already emitted AlterDomainDropDefault / AlterDomainSetDefault, the replacement expansion should not synthesize DropDomain / CreateDomain or downstream DropTable / CreateTable. The expected order is:

1. ALTER DOMAIN ... DROP DEFAULT
2. DROP FUNCTION old_signature
3. CREATE FUNCTION new_signature
4. ALTER DOMAIN ... SET DEFAULT ...

Local validation

Focused commands, both rerun after the initial failure and both stable after the test runner's retry attempts:

PGDELTA_SKIP_DUMMY_SECLABEL_BUILD=1 \
PGDELTA_TEST_POSTGRES_VERSIONS=17 \
TESTCONTAINERS_RYUK_DISABLED=true \
  ./node_modules/.bin/bun run --cwd packages/pg-delta test \
  --test-name-pattern "domain default does not replace the domain" \
  tests/integration/deep-scenario-probes.test.ts

PGDELTA_SKIP_DUMMY_SECLABEL_BUILD=1 \
PGDELTA_TEST_POSTGRES_VERSIONS=17 \
TESTCONTAINERS_RYUK_DISABLED=true \
  ./node_modules/.bin/bun run --cwd packages/pg-delta test \
  --test-name-pattern "table-backed domain default keeps" \
  tests/integration/deep-scenario-probes.test.ts

[xmh1011]

@avallete I can take this one if the direction below looks reasonable.

Proposed fix plan

This looks like a replacement-expansion issue rather than a serializer issue: pg-delta already emits targeted changes for some of these expressions, but expandReplaceDependencies still promotes the owning object to a destructive replacement.

I would keep the fix in the pg_catalog/pg_depend-based path and avoid SQL parsing:

1. Keep extracting expression dependencies from pg_depend as the source of truth.
2. In the replacement-expansion step, distinguish expression-container dependents from objects that genuinely need replacement.
3. When the producer is a replaced function signature and the dependent object itself survives, do not promote these refs to DropTable / CreateTable or DropDomain / CreateDomain:
   • table column default
   • generated column expression
   • table CHECK constraint
   • domain default
4. Instead, rely on the targeted changes that the diff layer can already produce and make sure their ordering is explicit:
   • drop/clear the old expression before dropping the old function overload;
   • create the new function overload before setting/recreating the expression;
   • preserve the table/domain and all rows.
5. Keep real destructive replacement only for dependents that have no targeted alter representation or where the object identity actually changes.

Regression coverage I would add

I would add focused pg-delta integration coverage, using PG17 first for RED/GREEN and then rerun the broader focused matrix:

• function signature replacement used by a table column default: no DROP TABLE;
• function signature replacement used by a table CHECK constraint: no DROP TABLE;
• generated column expression variant: no DROP TABLE;
• domain default variant: no DROP DOMAIN;
• table column using that domain: no DROP TABLE cascaded from the domain;
• boundary check: expression index still only replaces the index, not the table.

The key assertion would be data-preservation oriented: the generated plan must not include DROP TABLE / DROP DOMAIN for the surviving object, and applying the plan should leave existing rows intact.

[xmh1011]

Follow-up root-cause narrowing from local unit-level probes, without Docker or SQL execution:

The destructive replacements are introduced inside expandReplaceDependencies(...), before serialization/sorting. Given only these inputs:

• AlterDomainDropDefault(domain)
• DropProcedure(old_signature)
• CreateProcedure(new_signature)
• AlterDomainSetDefault(domain)
• one pg_depend edge: domain:probe_domain_default.code -> procedure:probe_domain_default.default_code(integer)

expandReplaceDependencies(...) appends both DropDomain and CreateDomain:

[
  "AlterDomainDropDefault",
  "DropProcedure",
  "CreateProcedure",
  "AlterDomainSetDefault",
  "DropDomain",
  "CreateDomain"
]

The same pure-function reproduction for a column default appends DropTable and CreateTable when the pg_depend edge is:

column:probe_default.items.amount -> procedure:probe_default.make_amount(integer)

Resulting change classes:

[
  "AlterTableAlterColumnDropDefault",
  "DropProcedure",
  "CreateProcedure",
  "AlterTableAlterColumnSetDefault",
  "DropTable",
  "CreateTable"
]

So the failure is not caused by SQL serialization or final topological sorting. It comes from the replacement walk itself:

1. function signature replacement seeds the old procedure stable id as a replace root;
2. mainCatalog.depends is walked from that old procedure id;
3. expression dependents are normalized to owning objects:
   • column:* -> table:* via normalizeDependentId();
   • domain:* remains directly resolvable;
4. resolveObjectForStableId(...) finds that table/domain in both catalogs;
5. buildReplaceChanges(...) synthesizes DropTable/CreateTable or DropDomain/CreateDomain, even though the original diff already had targeted default drop/set changes.

Boundary check from the same pure-function path: a domain CHECK constraint dependency does not append DropDomain/CreateDomain when the dependent id is constraint:probe_domain_constraint.non_empty_code.non_empty_code_check. That currently falls through because normalizeDependentId() treats all constraint:* ids as table-owned and normalizes this domain constraint to a non-existent table:probe_domain_constraint.non_empty_code. This explains why the domain CHECK probe passed while the domain DEFAULT probe failed; the DEFAULT is mapped directly to domain:* by pg_depend extraction.

This suggests the fix should be an expander-level suppression for targeted expression replacements, not a serializer-only change. In particular, when the dependent owner exists in both catalogs and the diff already contains the relevant targeted expression changes, the replacement walk should not synthesize whole-object replacements for:

• column:* dependents covered by AlterTable...DropDefault/SetDefault, generated-expression alters, or constraint drop/add;
• domain:* dependents covered by AlterDomainDropDefault/SetDefault;
• domain/table constraint dependents covered by explicit constraint drop/add.

A minimal first fix could build a set of targeted invalidated expression-owner ids from the existing changes before walking dependents, then skip buildReplaceChanges(...) for those owner ids. The important part is preserving the dependency edge for ordering while avoiding the destructive owner replacement.

[avallete]

Reviewed and reproduced: both original scenarios generate the reported destructive DROP TABLE byte-for-byte on current main (e396579). The root-cause analysis is correct, and the proposed plan above looks right — please go ahead and pick it up. A few caveats to fold into the implementation:

1. Condition the suppression on the referenced root kind, not just the dependent kind. A column:* dependent reached from a type:* replace root (enum/composite replacement) genuinely needs table recreation. A column:*/constraint:* dependent reached from a procedure:* root can only be an expression dependency (a function can never be a column's type), so that's the safe gate for the targeted path.

2. Don't rely solely on "the diff already emitted targeted changes". A same-stable-id drop+create (e.g. a parameter rename — argument_names is in SIGNATURE_BREAKING_FIELDS in procedure.diff.ts) leaves the dependent expression textually unchanged, so the diff emits nothing for it. The expansion must be able to synthesize the targeted drop/set (or constraint drop/add) pair itself, not just suppress the whole-object replacement when targeted changes happen to exist.

3. Drop-phase ordering mostly exists already — except for domains. AlterTableAlterColumnDropDefault / DropIdentity are explicitly routed to the drop phase in src/core/sort/utils.ts, so the inverted main-catalog pg_depend edges order them before the DROP FUNCTION; AlterTableDropConstraint lands there via its drops. But AlterDomainDropDefault is not in that routing — without adding it, the scenario-4 fix will sort the DROP DEFAULT after the DROP FUNCTION and fail at apply.

4. Generated columns (scenario 3) need a version gate. ALTER COLUMN ... SET EXPRESSION AS is PG17+; the test matrix includes PG15, where there is no in-place update of a generation expression. Keep the destructive fallback for is_generated columns on older versions — the scenario-3 expected SQL is only valid on 17.

5. Plan-level dedup will be needed. Scenario 1's current output already emits the default twice (inline in CREATE TABLE plus a redundant ALTER ... SET DEFAULT). Once the expansion synthesizes targeted changes, they can collide with diff-emitted ones; per the repo conventions, normalizePostDiffChanges is the chokepoint for pruning those.

6. The domain CHECK "boundary check" passing is an accident, not correct behavior. normalizeDependentId rewrites constraint:schema.domain.conname to a nonexistent table:schema.domain and silently skips the dependent. It only looks fine when the diff independently emitted targeted constraint changes; with a same-signature replacement (unchanged constraint text) the generated plan fails at apply with cannot drop function ... because other objects depend on it. Filed with a verified repro as pg-delta: domain CHECK constraint dependents are silently skipped by replacement expansion, producing plans that fail at apply #286 — please make the constraint: handling domain-aware as part of (or alongside) this fix rather than cementing the accidental fall-through.

Process-wise: RED → GREEN per AGENTS.md (failing regression tests committed first), integration coverage asserting no DROP TABLE/DROP DOMAIN for the surviving objects plus rows preserved after apply, and a changeset with the fix.

---

Generated by Claude Code