Skip to content

CORE-324 lock order follow-up

Base: PR #651 head 6cbafb6f06d15f79c1abeb25666afebaa6ae6e73. These are disposable PostgreSQL 16 checks, not staging or CI evidence.

Reproduced defects and correction

Both review findings produced SQLSTATE 40P01 on the exact base: retirement held endpoint A while its edge DELETE waited for B, and old INSERT held B while waiting for A; old UPDATE/DELETE held an edge row while waiting for retirement's endpoint lock, while retirement waited for that edge row. The initial pre-correction experiment observed 40P01 in all six cases (three SQL operations, two edge upsert modes). That experiment preceded the committed test's stronger immediate-refusal expectations; running the current test against the parent can fail earlier on its refusal deadline. The current insert branch requires exact 23514 and the retired/foreign-owner refusal message.

Retirement's DELETE now returns OLD for an already-tombstoned endpoint before locking other endpoints. Old UPDATE/DELETE reject any live OLD guarded endpoint before acquiring any endpoint lock, retaining the existing 23514 refusal. The precheck can conservatively refuse a transaction overlapping retirement; it cannot authorize a live mutation. Unregistered mutations retain the locked recheck, so registration cannot slip past the guard lookup. READ COMMITTED is checked before the tombstoned DELETE bypass. No new retry SQLSTATE is introduced.

Tests pause the actual retirement immediately before its edge DELETE, arrange the opposite lock ownership, observe PostgreSQL lock waits, and assert bounded completion, exact old-mutation refusal, no 40P01, committed retirement, persisted tombstone, and rollback of an earlier ordinary write in the refused transaction. Additional UPDATE/DELETE tests hold unregistered endpoint locks while registration waits, proving post-wait incident checks.

Residual deployment constraints

  • These trigger changes do not impose a transaction deadline. Direct generic pool transactions and old SQL clients can still wait indefinitely on a legitimately held endpoint lock. The separate CORE-322 host transaction whole-transaction retry helper must be composed with an absolute deadline and verified for bounded host work (attempt count alone is not a deadline); deployments must configure appropriate client/transaction time budgets.
  • The named host retains the expressly trusted guard SELECT/INSERT/UPDATE privileges. Arbitrary host SQL can clear a tombstone or alter its owner. Tombstone permanence therefore assumes the host uses the admitted runtime operations. Database enforcement of irreversible guard rows would require a separately specified privilege and lifecycle contract.
  • Installer attestation checks the named host's owner membership, direct unsafe grants, function bodies, trigger configuration, and schema/table ownership. It does not accept named legacy principals and cannot attest which owner-role members are trusted installers versus legacy writers. Operators must verify that legacy credentials cannot assume/inherit owner or privileged roles, including role membership changes after admission. This is an outstanding deployment attestation gap, not evidence that arbitrary legacy credentials satisfy the documented three-role separation.
  • DDL body attestation intentionally fails closed on an older installed guard. Updating an existing installation needs an explicit offline migration; this patch does not silently replace deployed trigger bodies.

Unresolved multi-statement deadlock (merge blocker)

At 4fddecbd, a legacy transaction can UPDATE only weight (which does not fire this trigger), or SELECT an edge FOR UPDATE. Retirement then holds A and waits for that earlier edge row; the writer's later INSERT waits for A. A host holding the edge row and later retiring B can instead wait for the tenant-wide retirement lock while retirement A waits for its row. All three schedules produced 40P01 in both upsert modes in disposable PostgreSQL. The diagnostic test_postgres_working_guard_multistatement_live.py records strict expected failures only after observing exact 40P01 and atomic victim rollback; other failures are not swallowed. These expected failures remain merge blockers.

Trigger-local ordering cannot order locks already obtained by SELECT FOR UPDATE or arbitrary earlier SQL. The low-level retirement API must join the caller's transaction, and a savepoint cannot release or replay its prior locks. Safe bounded host recovery therefore requires an own-transaction callback covering the complete operation, confirmed whole-transaction rollback before replay, and an absolute deadline. Legacy clients need their corresponding transaction retry contract. Fail-fast INSERT or NOWAIT retirement would introduce a new client error/retry contract and still cannot make arbitrary prior host locks obey an order. This patch does not claim to solve that broader transaction API.

The historical DELETE policy is also retained: ANY retired OLD endpoint allows edge deletion even if the other endpoint remains live. A runtime writer cannot insert that stale edge after retirement. An owner-injected historical-edge test covers this policy; changing it would be a separate ownership/lifecycle change.

Read-only CORE-322 callback assessment

Inspected stacked CORE-322 head 639aa7c95396085a31e9f1fa28ef59b8e8fe5995 without changing its branch. run_working_transaction refuses an ambient transaction, replays only a body 40P01 after the transaction context confirms rollback, and allows at most three attempts. It does not retry commit/rollback errors or impose an absolute deadline. PostgresWorkingCreateService.create uses this callback for owner claim, node creation, registration, retention enumeration and retirement; it sets 5s lock and 10s statement timeouts in each attempt. Audit and committed-state readback are outside replay.

A separate disposable probe loaded that exact helper read-only and reproduced the earlier-weight-UPDATE/later-INSERT cycle while selecting the host victim. Both upsert modes passed: the callback observed one 40P01, ran twice, rolled back an ordinary marker insert from attempt one, and committed the complete retirement plus exactly one marker in attempt two. This verifies callback recovery for that body failure, not the full Agent endpoint or every victim.

The prior-row-lock host variant is outside this admitted POST path because ambient transactions are refused. A legacy multi-statement writer can still make an admitted host body deadlock; the callback covers a host body victim, while a legacy victim needs its own client transaction retry. Three failed attempts can still fail the host operation, and per-statement timeouts do not bound the entire transaction, pool acquisition, commit or readback. Therefore the raw CORE-324 multi-statement limitation and whole-operation deadline gap remain explicit; the trigger correction alone does not establish universal transaction liveness.