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

[NikolayS]

Testing evidence

Head: adf98c1d26791b1e7f9160dee61e6d7de5f548d7

CI

• 17/17 GitHub checks green
• PostgreSQL regression matrix green on 14, 15, 16, 17, 18, and 19 beta1
• generated SQL drift check green
• pg_tle, pg_cron, pg_timetable, stable install, upgrade, and all client jobs green

Local PostgreSQL 17

• tests/run_all.sql — ALL TESTS PASSED
• tests/acceptance/run_acceptance.sql — ALL ACCEPTANCE TESTS PASSED
• tests/two_session_registration_locks.sh — PASS
• same concurrency harness with PGQUE_TEST_LOCKER_START_DELAY=0.7 — PASS
• forced TERM during the harness — exit 143, zero leaked objects
• post-run residue query — 0|0|0|0 for test triggers, queues/consumers, and barrier tables
• bash build/transform.sh
• git diff --check

Red/green evidence

Before the SQL fix, the deterministic first-registration barrier reproduced:

ERROR: duplicate key value violates unique constraint "consumer_name_uq"

Before the harness cleanup fix, the residue check found:

leaked_triggers = 2
leaked_queues = 6
leaked_consumers = 13

After the fixes, concurrent ordinary registration, mixed cooperative group formation, and the partition receive/finish lock edge all pass, with zero residue.

REV

REV was run twice. The first pass found two blocking defects in the new test harness (transactional teardown rollback and a timing-based barrier). Both were fixed and reproduced. The follow-up review verified the exact head, reran the relevant scenarios, found 0 blocking issues, and returned approve and merge. SOC2-only checks were ignored per repository policy.