Migrations are incompatible with PostgreSQL 17.12+ on fresh install

The trigger is in src/kdive/db/schema/0112_capture_operation_supervision.sql (function at line 334, worker_incarnations hazard at 352) and 0113_capture_publication_fence.sql line 41, with the runner checksum check in src/kdive/db/migrate.py and scripts/guards/schema_immutable_guard.py. Reproduce with the three-line CREATE TABLE/CREATE FUNCTION/ALTER TABLE case on 17.12. Done means a fresh 17.12 database migrates the full chain, but this requires an ADR decision first, so it is not a good first task.

Problem

On a fresh install, the migration chain is incompatible with PostgreSQL 17.12 and later. 0113 fails with:

psycopg.errors.FeatureNotSupported: cannot alter table "capture_operations" because function capture_launch_abort_evidence_valid(capture_operations,jsonb) uses its row type

#3610 restores CI, compose and lab bring-ups by pinning the compose and fixture Postgres images by digest to 17.10. That pin defers the break; it does not fix it. Two cases still hit it:

• Any operator running their own 17.12+ server.
• Any future bump of the pin.

This issue tracks the cause-level fix. It relates to #3610 but does not block it. It came up during campaign work on #3583/#3584.

Evidence

Trigger

• src/kdive/db/schema/0112_capture_operation_supervision.sql:334 creates public.capture_launch_abort_evidence_valid(p_operation public.capture_operations, p_evidence jsonb). It is a LANGUAGE sql function with a new-style (SQL-standard) RETURN body. The body reads row fields: p_operation.host_instance and p_operation.launch_token, at lines 347-349.

• src/kdive/db/schema/0113_capture_publication_fence.sql:41 runs ALTER TABLE public.capture_operations ADD COLUMN publication_state text NOT NULL DEFAULT 'pending', ..., which 17.12 rejects.

• The narrowed trigger was reproduced on 17.12 (postgres:17@sha256:846a7ddaa67a4b354ae1d21087b683901aa6113a4ed626d3787cfbf6c85e3e07). The failing case is ADD COLUMN with a non-null default on a table whose row type is a parameter of a new-style SQL function body that reads its fields:

  CREATE TABLE a (id int, s text);
  CREATE FUNCTION fa(p a) RETURNS boolean LANGUAGE sql RETURN p.s IS NOT NULL;
  ALTER TABLE a ADD COLUMN x text DEFAULT (1)::text;
  -- 17.12: ERROR:  cannot alter table "a" because function fa(a) uses its row type
  -- 17.10: ALTER TABLE
  

• These cases still succeed on 17.12:

  • ADD COLUMN without a default
  • ADD CONSTRAINT ... CHECK
  • string-body (AS $$ ... $$) SQL functions
  • plpgsql functions
  • new-style bodies that do not read a row field

• The same latent hazard exists on worker_incarnations. 0112:352 declares public.capture_recovery_authorized(p_owner public.worker_incarnations, p_replacement public.worker_incarnations) with a new-style body that reads row fields (p_owner.state, p_owner.terminated_at, ...).

  • No current migration alters capture_operations (after 0113) or worker_incarnations (after 0112).
  • Any future ADD COLUMN ... DEFAULT on either table will fail on fresh installs and on existing 17.12+ databases.

• The full chain on a fresh 17.12 database stops at 0113. On 17.10 it applies all 187 migrations.

• Databases that already applied 0113 are unaffected by a minor upgrade. I migrated a 17.10 data directory through 0195 and restarted it on 17.12: the runner applied 0, the checksums verified, and capture_operations was readable.

Upstream change

The PostgreSQL 17.12 release notes (https://www.postgresql.org/docs/release/17.12/, released 2026-11-12) have an item under "Add defenses against problems caused by altering composite types concurrently with operations on data of those types". It reads: "Tighten the rules about whether ALTER COLUMN TYPE is allowed when dependencies exist on the composite type. For example, it's no longer allowed when a function's default expression or new-style SQL function body depends on the type." The note names only ALTER COLUMN TYPE. The reproduction above shows the same check also rejects ADD COLUMN ... DEFAULT.

Immutability blocks the obvious fix twice

• src/kdive/db/migrate.py:125-132 hard-fails on any applied file whose checksum changed: applied migration … checksum changed; applied migrations are immutable (ADR-0015).
• scripts/guards/schema_immutable_guard.py forbids modifying, deleting or renaming any existing src/kdive/db/schema/*.sql file (#1218, #1723).
• So 0112 and 0113 cannot be edited in place. Doing so would break every database that applied them, and the guard rejects the edit regardless.

Expected

• A fresh install migrates through the whole chain on PostgreSQL 17.12 and later.
• Databases that already applied 0112/0113 keep passing the ADR-0015 checksum check.
• The #3610 digest pin on docker-compose.yml and tests/compose/fixtures/worker-lifecycle-live.yml is lifted, or moved to a 17.12+ digest, once the chain is compatible. The db fixture digest in tests/db/conftest.py can then move to 17.12+ as well.
• A guard keeps the hazard from coming back.

Proposed approach

This needs an ADR before implementation, because each route changes or qualifies ADR-0015. The options are:

1. ADR-0015 exception. Add a reviewed allowlist mapping (version, old_sha256) → new_sha256, honoured by both migrate.py and schema_immutable_guard.py. 0113 could then wrap its ALTER by dropping and recreating capture_launch_abort_evidence_valid and re-issuing its grant (0112:1566). Its callers at 0112:1033/:1113 are plpgsql and resolve at run time.
2. Runner hook. Before applying 0113 on a fresh database, drop the dependent function, then recreate it afterwards. The files stay byte-identical, but the runner gains special-cased knowledge of specific migrations.
3. Re-baseline. Squash the chain into a baseline migration for fresh installs, written so it never adds a defaulted column under a dependent new-style function. Existing databases keep their recorded history.

Whichever route is chosen, also:

• Pre-empt the worker_incarnations hazard (0112:352), for example by redefining both functions with scalar parameters or string bodies in a new migration.
• Add a guard that fails a new migration which runs ALTER TABLE ... ADD COLUMN ... DEFAULT on capture_operations or worker_incarnations. More generally, it should cover any table whose row type is a parameter of a new-style SQL function body. The guard is not needed for tables where the dependency has been removed.
• Lift the #3610 digest pin, or move it to a 17.12+ digest. Run the full chain on a fresh 17.12+ database as the proof.

Related: #3610 (digest pin, the fast unblock). This issue does not block #3610.