fix(registration): prevent deadlocks and first-create races

[NikolayS]

Summary

• acquire existing consumer rows with FOR NO KEY UPDATE, preserving registrar serialization without conflicting with subscription FK checks
• create missing ordinary, cooperative-main, and cooperative-member rows with INSERT ... ON CONFLICT DO NOTHING followed by a locking re-read
• add deterministic multi-session regressions for ordinary first-registration races, mixed cooperative group formation, and the partition receive/finish lock edge
• run the concurrency regression across the supported PostgreSQL CI matrix

Fixes #357

Why

FOR UPDATE conflicts with the FOR KEY SHARE lock taken by the
subscription.sub_consumer -> consumer.co_id foreign-key trigger. Together
with partition-slot lease renewal, that creates the registration/receive
AB-BA deadlock observed by Wave.

The old SELECT ... FOR UPDATE; if not found INSERT protocol also had no row
to lock during first registration, so concurrent creators could collide on
consumer_name_uq.

Testing

• Red: deterministic advisory-barrier test reproduced consumer_name_uq on unmodified main
• Green: tests/two_session_registration_locks.sh passes with:
  • 8 simultaneous first registrations of one ordinary consumer
  • register_consumer_at racing 4 cooperative members for one new group
  • an open registrar transaction concurrent with an empty filtered receive_partitioned batch
• PostgreSQL 17: tests/run_all.sql
• PostgreSQL 17: tests/acceptance/run_acceptance.sql
• bash build/transform.sh
• git diff --check

Review context

This recreates the original three commits from #358 after that PR was reverted because it was merged without the owner's review.

The resulting source tree is byte-for-byte identical to the tested PR 358 head and accidental merge commit. This PR is intentionally left open for proper review and requires explicit merge authorization tied to this PR.

[NikolayS]

Testing and review evidence

Head: 94cfb26e9fac897dfc94b6ba8ac2fbc9f219cb4c

Exact-source verification

• PR 360 tree: 4bcd7aaf3928a5e65cca5806db6ff4f0b1a1178f
• tested PR 358 head/merge tree: 4bcd7aaf3928a5e65cca5806db6ff4f0b1a1178f
• git diff --exit-code 15c802c6 HEAD passed
• original three commits were recreated individually on restored main

CI

• 17/17 GitHub checks green
• PostgreSQL regression matrix green on 14, 15, 16, 17, 18, and 19 beta1
• registration concurrency regressions green on every supported PostgreSQL version
• generated SQL drift, install paths, upgrades, and all client jobs green
• run: https://github.com/NikolayS/PgQue/actions/runs/30185252554

Local PostgreSQL 17 evidence from the identical source tree

• tests/run_all.sql — ALL TESTS PASSED
• tests/acceptance/run_acceptance.sql — ALL ACCEPTANCE TESTS PASSED
• tests/two_session_registration_locks.sh — PASS
• delayed-locker run — PASS
• forced TERM cleanup — zero leaked objects
• post-run residue query — 0|0|0|0
• bash build/transform.sh
• git diff --check

Original detailed evidence: #358 (comment)

REV

REV ran twice on the byte-identical source tree. The first pass found two blocking test-harness defects; both were fixed in the third commit. The second pass verified the exact tree, found zero blockers, and returned approve-and-merge. SOC2-only findings were ignored per repository policy.

This PR remains open for owner review. It must not be merged without explicit authorization tied to #360.