Replanning after interrupted CREATE INDEX CONCURRENTLY ignores the invalid index

[christophostertag]

Summary

After interrupting a pgschema-generated CREATE INDEX CONCURRENTLY, PostgreSQL retains the named index with indisvalid = false. Running pgschema plan again against the unchanged desired schema produces an empty plan, leaving the invalid index unrepaired.

The original apply correctly exits with an error. The problem is the subsequent successful no-op plan, which treats the invalid index as satisfying the desired schema.

Environment and evidence

• pgschema 1.13.0, Linux arm64
• PostgreSQL 18.4, external planning database on the same disposable instance
• Observed in a synthetic fault-injection fixture on 2026-09-13
• Latest release checked when reporting: v1.13.0. No claim that the fixture was rerun against unreleased main.

Schema and steps

Use disposable databases repro_target and repro_plan with an existing local role allowed to create objects in both. The commands below use generic names instead of the original fixture names.

Initial target schema/data:

CREATE TABLE public.item(id integer PRIMARY KEY, value integer NOT NULL);
INSERT INTO public.item VALUES (1,10),(2,20);

desired.sql:

CREATE TABLE public.item(id integer PRIMARY KEY, value integer NOT NULL);
CREATE INDEX item_value ON public.item(value);

Generate the saved plan:

pgschema plan --host localhost --port 5432 --db repro_target --user postgres \
  --sslmode disable --plan-host localhost --plan-db repro_plan \
  --plan-user postgres --plan-sslmode disable \
  --file desired.sql --output-json plan.json --output-sql plan.sql --no-color

The generated plan uses concurrent index creation. To make the interruption deterministic, hold a writer transaction open in a separate target session:

BEGIN;
UPDATE public.item SET value = value WHERE id = 1;
-- Leave this transaction open while starting apply in another process.

Start apply:

pgschema apply --host localhost --port 5432 --db repro_target --user postgres \
  --sslmode disable --plan plan.json --auto-approve --no-color

In a separate administrative session connected to this disposable target, identify the exact index-creation backend:

SELECT p.pid, p.phase, i.indisvalid
FROM pg_stat_progress_create_index p
JOIN pg_stat_activity a ON a.pid = p.pid
JOIN pg_index i ON i.indexrelid = p.index_relid
WHERE a.datname = current_database()
  AND p.relid = 'public.item'::regclass
  AND p.index_relid = to_regclass('public.item_value')
  AND p.command = 'CREATE INDEX CONCURRENTLY'
  AND NOT i.indisvalid;

Once this returns the matching backend, terminate that PID only with SELECT pg_terminate_backend(<matching_pid>);. Then roll back the held writer transaction. Our automated fixture additionally matched the migration role and a unique application name before terminating the backend.

Finally, rerun the same pgschema plan command into fresh output files against the same desired schema.

Actual results

The recorded sequence was:

Check	Observed
Phase at termination	waiting for writers before build
Index validity at termination	false
Termination acknowledged	true
Initial apply	exit 1, SQLSTATE 57P01
Rows after interruption	both original rows preserved
Index after interruption	item_value, invalid
Regenerated plan	exit 0, empty SQL, "groups": null
Independent final catalog check	index still invalid

Relevant regenerated-plan fields:

{
  "version": "1.0.0",
  "pgschema_version": "1.13.0",
  "groups": null
}

Independent check:

SELECT indexrelid::regclass, indisvalid, indisready
FROM pg_index
WHERE indexrelid = 'public.item_value'::regclass;

No manual DROP INDEX or REINDEX was inserted into the recorded recovery attempt.

Expected result

An invalid index should not count as convergence to a desired usable index. Ideally generate a safe recovery plan with appropriate concurrent-operation handling. At minimum, fail planning with an actionable diagnostic naming the invalid index and the required recovery rather than returning a successful empty plan.

This is separate from retrying a lock timeout within one apply (#560), and does not require the interrupted command itself to succeed. A regression should inspect index validity after interruption and verify that replanning either repairs the state or explicitly refuses it.