driver-sql: first concurrent autonumber insert from two tenants fails on Postgres (25P02) — the sequence-row race handler runs inside an already-aborted transaction

[baozhoutao]

On Postgres, when two tenants insert into the same autonumber-bearing object for the first time concurrently, the whole batch fails with 25P02 current transaction is aborted. The counters are advanced anyway, so the numbers the failed attempt reserved are lost — a permanent gap at the start of both tenants' sequences.

Single-tenant concurrency is fine at every level I tried. The tenant boundary is what breaks it, which makes this specific to multi-org deployments — and the EE stack runs Postgres.

Measured

Driver-level probe, SqlDriver on postgres:16, object { organization_id, code: autonumber 'TK-{0000}', name }, one Promise.all burst per case, each case on a fresh driver and a fresh table:

case	total inserts	result
1 tenant × 2 / 4 / 6 / 8 / 12	up to 12	OK — TK-0001…TK-0012, contiguous, no duplicates
2 tenants × 2	4	FAILED 25P02
2 tenants × 4	8	FAILED 25P02
2 tenants × 6	12	FAILED 25P02
2 tenants × 6, pool max: 20	12	FAILED 25P02 — not pool exhaustion
2 tenants, strictly serial	8	OK — A:TK-0001 B:TK-0001 A:TK-0002 B:TK-0002 …

The failing statement is the sequence reservation itself:

select * from "_objectstack_sequences" where "key_hash" = $1 limit $2 for update
  - current transaction is aborted, commands ignored until end of transaction block

Cold vs warm is the discriminator. Seed one serial row per tenant first (so both counter rows exist), then run the exact burst that failed cold:

[W] warm-up => orgA:TK-0001 orgB:TK-0001
[W] counter rows now => [{"tenant_id":"orgA","last_value":"1"},{"tenant_id":"orgB","last_value":"1"}]
[W] warm concurrent => OK orgA=TK-0002..TK-0005 orgB=TK-0002..TK-0005
[W] cold concurrent  => FAILED code=25P02

Retry "recovers" but loses numbers. Three successive bursts on a cold object:

[R] attempt 1 => FAILED code=25P02
[R]   counters after 1 => [{"tenant_id":"orgA","last_value":"3"},{"tenant_id":"orgB","last_value":"1"}]
[R] attempt 2 => OK orgA:TK-0004 TK-0005 TK-0006   orgB:TK-0002 TK-0003 TK-0004
[R] attempt 3 => OK orgA:TK-0007 TK-0008 TK-0009   orgB:TK-0005 TK-0006 TK-0007

TK-0001…TK-0003 (orgA) and TK-0001 (orgB) exist in no row — the counters were advanced by the attempt whose transaction then aborted. So the user-visible story is: "creating the first records failed; I retried, it worked, and my numbering starts at 0004."

SQLite is unaffected — the identical cold cross-tenant burst succeeds:

[R] sqlite cold burst => OK orgA:TK-0001 TK-0002 TK-0003 orgB:TK-0001 TK-0002 TK-0003

which is why the existing autonumber suite (sqlite-backed) does not catch it.

Cause

reserveSequenceValue handles the first-insert race by catching the unique violation and continuing inside the same transaction (packages/drivers/driver-sql/src/sql-driver.ts, ~line 4209):

try {
  await trx(SEQUENCES_TABLE).insert({ ...insertRow, last_value: initial });
  return initial;
} catch (err) {
  // Another writer raced us to the first INSERT. Fall through to
  // the UPDATE path with the now-present row.
  existing = await trx(SEQUENCES_TABLE).where(key).forUpdate().first();   // ← 25P02 here
  if (!existing) throw err;
}

In Postgres any statement error poisons the whole transaction: every subsequent statement returns 25P02 until rollback. So this recovery path can never run on Postgres — the SELECT … FOR UPDATE in the catch is itself the statement that raises the error we observe. The pattern is only valid on SQLite/MySQL, where a statement error does not abort the transaction. The comment a few lines above (Postgres/MySQL behave normally here) is the assumption that does not hold.

Suggested fix

Make the first-insert race dialect-safe, e.g.

• wrap the speculative INSERT in a SAVEPOINT and roll back to it before falling through, or
• do the insert as INSERT … ON CONFLICT DO NOTHING (the driver already uses onConflict at sql-driver.ts:4596) and then re-select, so no statement ever errors.

A regression test needs to be Postgres-backed and cross-tenant + cold + concurrent — the three conditions together. Single-tenant or warm variants pass regardless.

Environment

Reproduced against postgres:16 in Docker, via SqlDriver directly (branch claude/multi-org-service-testing-a75937, deps built from the current worktree). Same shape as the objectos-ee-deploy stack serving http://localhost:8080, which is single-database with organization_id partitioning. The probe files were temporary and are not part of this report; the tables above are the full output.

[baozhoutao]

Found while testing autonumber behaviour on the same multi-org deployment as #8090–#8094, #8095, #8096.

[os-zhuang]

Claim: PM loop, drivers lane
Session: session_01VoxQqG5FiUHZKCST7KDoZC
Branch: claude/issue-8269-autonumber-cold-race-25p02
Worktree: objectstack-issue-8269
Domain: domain:drivers
File surface: packages/drivers/driver-sql/src/sql-driver.ts + a new Postgres-backed test in packages/drivers/driver-sql/src/ + .changeset/ (stop on breach; explain in the report)
Container & model: M, mode:subagent, model: opus
Serial constraints cleared: sql-driver.ts last moved by #8198 (a5dcb74) and #8234 (7e5af5c), both MERGED; no open drivers card touches this file — #8138 is a measurement card on sql-driver-overlay-index-drift.test.ts, disjoint.

⚠️ Taken without a pm:* state label present — this card carried bug + priority:p1 + domain:drivers and no pm-state. I treated the domain:* label as the routing authority and did not add or alter domain:*, priority:* or the type (triage's fields — I added only pm:dispatched, the executing seat's own state marker). A p1 data-integrity defect with a located cause and a live customer surface should not wait a sweep cycle for a queue label. If triage wanted this held, say so and I yield.

Premise verified on origin/main @ 021d8dd before dispatch — live, with three corrections

The defect reproduces in the code as described, but the report's landmarks have drifted (main moved after the reporting branch):

1. The function is getNextSequenceValue (sql-driver.ts:4305), not reserveSequenceValue — that name does not exist in this file. Line ~4209 is now ~4373.
2. The Postgres/MySQL behave normally here comment is on a different try/catch than the one that breaks. It sits at :4353, on the forUpdate() fallback; the first-insert race the report measured is at :4373.
3. There are TWO catch-inside-transaction sites, not one. Besides the measured one, :4353 does try { …forUpdate().first() } catch { …first() } — on Postgres that fallback SELECT runs inside the same poisoned transaction and would raise 25P02 for exactly the same reason. It is a rarer trigger (it needs forUpdate() itself to throw), so it is not what the report captured, but a fix that only patches :4373 leaves the same bug behind a narrower door.

The report's analysis is otherwise correct and its measurement is the good kind — the cold/warm discriminator and the sqlite-passes/postgres-fails split are exactly what explain why the existing autonumber suite never caught this.

---

Generated by Claude Code

[hotlong]

Triage: lands in packages/drivers/driver-sql (sql-driver.ts first-insert race handler — anchors re-verified on origin/main, the speculative-INSERT catch + forUpdate().first() fall-through at lines 4354/4377–4379) ⇒ domain:drivers confirmed; not in the driver-memory/mongodb investment freeze. pm:queue, type Bug.

target:v17 per the release-board binary test, class ① (published-surface defect users hit today): measured on the EE multi-org deploy — the first concurrent cross-tenant autonumber insert on Postgres fails 25P02 and permanently burns reserved sequence numbers, and Postgres + multi-org is exactly the EE stack's shape.

Removed priority:p1: the p1–p5 ladder is retired (only priority:p0 has a reader — the jump-the-queue rule; a p1 label has none). Urgency is carried by the release board plus the Bug-first tie-break; nothing about this card's severity is lost by the removal.

For the drivers seat at claim time: serial-check vs in-flight #8138 (same lane; expected file-disjoint — verify, don't assume). The regression test must be Postgres-backed AND cross-tenant AND cold AND concurrent — the card measures that dropping any one of the three conditions goes green regardless. Size/model suggestion: M, mode:subagent, opus.

---

Generated by Claude Code