04 — Workstream C: fn_birth_registry_auto Conflict-Target Patch Rehearsal

Result: PROVEN. The patch is a pure body change via CREATE OR REPLACE FUNCTION; OID is stable, so all 166 dependent triggers stay bound with no trigger rewrite. Rehearsed live inside BEGIN … ROLLBACK; post-rollback md5 == entry md5.

Entry baseline

Metric	Value
md5(def) entry	1f729b3571a74963089bb3ef388217f3
OID	39232
triggers calling this function	166

The change

Only one line changes:

- ON CONFLICT (entity_code) DO NOTHING;
+ ON CONFLICT (entity_code, collection_name) DO NOTHING;

The function's skip guard is already composite-aware (WHERE entity_code=… AND collection_name=TG_TABLE_NAME), so after the conflict target matches the new composite index, a code that exists in another collection will be correctly born in its own collection instead of being silently dropped. The full patched body is staged in the apply packet (doc 10); byte-identical to current except that line.

In-transaction proof

Probe	Result
CREATE OR REPLACE FUNCTION succeeds in txn	yes
OID after replace	39232 (unchanged — CREATE OR REPLACE never re-creates the OID)
triggers still bound after replace	166 (unchanged)
patched conflict target present	true
post-ROLLBACK md5(def)	1f729b3571a74963089bb3ef388217f3 == entry

OID-stability is the critical finding: because CREATE OR REPLACE FUNCTION preserves the OID (39232), pg_trigger.tgfoid for all 166 triggers continues to resolve to the same function. No trigger needs to be dropped, recreated, or re-pointed. The "166 triggers" risk is not a rewrite risk — it is a single function-body swap.

Runtime-validity note

PL/pgSQL does not validate the ON CONFLICT target at CREATE time; it resolves at execution. So the function can be replaced before/after the composite index exists without a parse error, BUT at runtime the patched conflict target requires the matching unique index to exist or any birth insert raises "no unique or exclusion constraint matching the ON CONFLICT specification". Apply ordering is mandatory: composite index/constraint must be live before the function patch is committed.

fn_birth_registry_auto_id (the second call site)

Used by tables without a code field; entity_code = TG_TABLE_NAME || ':' || id (inherently collection-prefixed). For correctness it should receive the same conflict-target change and its single-column EXISTS guard should become composite-aware. Entry md5 3f3515dedf1d5bb0f0f66711297b5b5c. Included in the apply packet as a paired change.

Regression tests (for apply)

1. After composite + patch: new-code dot_tools insert → born once.
2. Code existing in another collection → now born in its own collection.
3. Re-insert same (code, collection) → no duplicate.
4. count(*) FROM pg_trigger WHERE tgfoid='fn_birth_registry_auto'::regproc → still 166.

Risk list

• R1 (ordering): patch committed before composite index exists → runtime birth failures. Mitigation: enforce order; smoke-test in a txn.
• R2 (auto_id symmetry): patch both functions together.
• R3 (superuser drift): could CREATE OR REPLACE back to single-col. Detect-only via hash baseline (doc 09).

Decision

FN_PATCH = PROVEN. OID-stable, reversible, no trigger rewrite. Gated only by apply-ordering (composite first) and owner DDL approval.