pg-delta: multiple leading enum values generate ADD VALUE before a non-existent label

[xmh1011]

Bug report

pg-delta generates invalid ALTER TYPE ... ADD VALUE SQL when a target enum inserts a contiguous run of two or more new values before the first existing label.

This is different from #262: it is not about using a newly added enum value in dependent DDL before a commit. The generated enum DDL itself references an anchor label that does not exist yet.

• I confirm this is a bug with Supabase, not with my own application.
• I confirm I have searched the Docs, GitHub Discussions, and Discord.

To Reproduce

Source database:

CREATE SCHEMA probe;
CREATE TYPE probe.status AS ENUM ('c');

Target database:

CREATE SCHEMA probe;
CREATE TYPE probe.status AS ENUM ('a', 'b', 'c');

On current main (c06f081, @supabase/pg-delta@1.0.0-alpha.30), createPlan produces one transactional unit:

ALTER TYPE probe.status ADD VALUE 'a' BEFORE 'b';
ALTER TYPE probe.status ADD VALUE 'b' AFTER 'a';

Applying that plan fails immediately:

ERROR 22023: "b" is not an existing enum label

The failure is deterministic because the first statement tries to insert a before b, but b is also new and has not been added yet.

Additional cases tested

These cases were validated against a temporary PostgreSQL 16.13 cluster using createPlan + applyPlan from origin/main:

Source labels	Target labels	Generated shape	Result
('b')	('a', 'b')	ADD 'a' BEFORE 'b'	applies
('c')	('a', 'b', 'c')	ADD 'a' BEFORE 'b'; ADD 'b' AFTER 'a'	fails, 22023
('d')	('a', 'b', 'c', 'd')	ADD 'a' BEFORE 'b'; ADD 'b' AFTER 'a'; ADD 'c' AFTER 'b'	fails, 22023
('a', 'd')	('a', 'b', 'c', 'd')	ADD 'b' AFTER 'a'; ADD 'c' AFTER 'b'	applies
('b')	('a', 'b', 'c')	ADD 'a' BEFORE 'b'; ADD 'c' AFTER 'b'	applies

So the problematic shape is specifically a contiguous inserted segment at the start of the enum where the segment length is at least two.

Expected behavior

pg-delta should only use an existing label, or a label already added by an earlier statement, as the BEFORE/AFTER anchor.

For the repro above, one valid order is to add the leading run from right to left:

ALTER TYPE probe.status ADD VALUE 'b' BEFORE 'c';
ALTER TYPE probe.status ADD VALUE 'a' BEFORE 'b';

That order was validated directly on PostgreSQL 16.13 and produces the desired final order a, b, c.

Root cause hypothesis

diffEnumLabels simulates additions left-to-right over the branch order. When the first added label is before another added label, nextBranch is not in workingLabels, but the code still emits position = { before: nextBranch }:

} else if (nextBranch) {
  // nextBranch exists but not in workingLabels yet (shouldn't happen in normal flow)
  position = { before: nextBranch };
  workingLabels.push(newValue);
}

That branch is reachable for leading inserted runs such as target labels a, b, c from source c, and produces ADD VALUE 'a' BEFORE 'b' before b exists.

Possible fix direction

When a newly inserted run appears before the first existing label, emit that run from right to left against the nearest existing right-side anchor, or otherwise choose anchors from the current workingLabels only.

Focused regression coverage should include:

• failing case: source ('c'), target ('a', 'b', 'c')
• failing case: source ('d'), target ('a', 'b', 'c', 'd')
• passing controls: one leading value, multiple middle values after an existing value, and leading plus trailing values

[xmh1011]

I can take this one if the maintainers agree with the scope.

More precise root cause

The failure is in the local planning logic for enum label additions, before execution planning or transaction boundaries matter.

diffEnumLabels currently walks addedValues in the insertion order derived from the target enum. For the source/target shape:

source: c
target: a, b, c

it sees a first. At that point:

workingLabels = ["c"]
prevBranch = undefined
nextBranch = "b"

Because nextBranch exists but is not in workingLabels, the fallback branch emits:

ALTER TYPE probe.status ADD VALUE 'a' BEFORE 'b';

That fallback comment says this should not happen in normal flow, but it is reachable for any leading inserted run with length >= 2. The generated statement is invalid because b is also new and has not been created yet.

This is separate from #262: #262 had valid enum DDL followed by dependent DDL that needed a commit boundary. Here the first enum DDL statement itself is invalid.

Proposed fix

I would keep the fix inside enum label planning and avoid touching execution units.

The invariant should be:

Every generated BEFORE or AFTER anchor must refer to a label that is already present in workingLabels at the moment that statement is emitted.

A narrow implementation path:

1. Replace the current single left-to-right loop with a small scheduler over pending added labels.
2. Repeatedly emit any pending label whose previous target neighbor is already in workingLabels as AFTER prev.
3. Otherwise emit any pending label whose next target neighbor is already in workingLabels as BEFORE next.
4. Update workingLabels after each emitted statement.
5. If a full pass makes no progress, throw or fall back explicitly rather than emitting SQL with a non-existent anchor. In practice additions-only enum diffs should always make progress because at least one neighbor of an inserted run borders an existing label or the list boundary.

That naturally handles the failing leading-run case by emitting from right to left:

ALTER TYPE probe.status ADD VALUE 'b' BEFORE 'c';
ALTER TYPE probe.status ADD VALUE 'a' BEFORE 'b';

and preserves the already-working middle-run case as left-to-right AFTER statements:

ALTER TYPE probe.status ADD VALUE 'b' AFTER 'a';
ALTER TYPE probe.status ADD VALUE 'c' AFTER 'b';

Regression coverage I would add

I would add focused coverage for these cases:

• source ('c'), target ('a', 'b', 'c'): fails today with 22023, should apply.
• source ('d'), target ('a', 'b', 'c', 'd'): same leading-run class, length 3.
• source ('b'), target ('a', 'b'): single leading value remains valid.
• source ('a', 'd'), target ('a', 'b', 'c', 'd'): existing middle-run behavior remains valid.
• source ('b'), target ('a', 'b', 'c'): leading plus trailing additions remain valid.

I can open a small PR with RED/GREEN evidence for the first failing case and the focused controls above.

[xmh1011]

Opened a fix PR: #316

What it covers:

• enum ADD VALUE planning now only uses anchors that already exist at emit time; the leading inserted-run case from this issue is emitted from the right-side existing anchor back toward the start.
• empty enum extraction is preserved, so adding labels to an existing empty enum no longer gets misplanned as CREATE TYPE.
• empty-string enum labels remain valid as values and BEFORE/AFTER anchors.
• existing label reorders are rejected explicitly instead of producing a no-op or an incorrect append-only plan.
• removed-label replacement now restores enum security labels.

Current local verification for the PR branch:

• focused enum unit tests: 24 pass / 0 fail.

The PR body also notes that the normal package-local pg-delta test command hung in the Docker/global-setup path, so I used the repo-documented unit-only workaround from outside the package directory for the focused unit run.