Plastic Labs Internal Wiki

Recently Updated

message-seq-session-counter

9 min read

Per-Session Message Sequence Counter (remove the global advisory lock)

Status: Draft | Owner: vineeth | Last updated: 2026-06-01

1. Problem Statement

create_messages (src/crud/message.py) assigns each message a gap-free, per-session sequence number (seq_in_session) and protects that assignment with a blocking, transaction-scoped PostgreSQL advisory lock:

await db.execute(text("SET LOCAL lock_timeout = '5s'"))
await db.execute(
    text("SELECT pg_advisory_xact_lock(hashtext(:workspace_name), hashtext(:session_name))"),
    {"workspace_name": workspace_name, "session_name": session_name},
)
last_seq = (await db.scalar(
    select(models.Message.seq_in_session)
    .where(... workspace_name, session_name ...)
    .order_by(models.Message.seq_in_session.desc()).limit(1)
)) or 0
# build N message objects with seq = last_seq + offset, INSERT, then commit (releases the lock)

The lock exists because the API allows concurrent POST .../messages calls to the same session. Without serialization, two callers both read max(seq) = N, both write N+1, and one violates UniqueConstraint(workspace_name, session_name, seq_in_session) (src/models.py:258).

This lock is the prime suspect behind production connection-saturation incidents on our managed (Groudon) deployment, where many per-tenant Honcho instances share one Postgres database with schema isolation, fronted by the Supavisor transaction pooler. Three properties make it pathological in that topology:

  1. Advisory locks are database-scoped, not schema-scoped. PostgreSQL keys advisory locks by (MyDatabaseId, classid, key1, key2) — the database, never the schema. The key here is derived from hashtext(workspace_name) and hashtext(session_name), application-level strings that carry no tenant/schema component. Any two tenants in different schemas that reuse the same workspace/session names take the identical lock and serialize against each other despite being fully isolated at the table level. Plus low-probability 32-bit hashtext collisions on top.

  2. A blocking lock under transaction pooling is a connection amplifier. In transaction-pooling mode a backend is bound to a client for the duration of the transaction. A transaction parked on pg_advisory_xact_lock sits in state=active, wait_event=Lock/advisory and pins both a Supavisor client slot and a Postgres backend for up to the 5s lock_timeout. A burst of writes to one hot (or cross-tenant-colliding) session queues clients behind the holder, and the Supavisor client count climbs toward the 9,000 EMAXCONN ceiling — a thundering herd.

  3. It’s invisible to deadlock detection and hard to observe. Advisory-lock waits don’t show the contended object the way row locks do, complicating diagnosis during an incident.

Message.id is already a global BigInteger Identity() (src/models.py:208) with an index on (session_name, id) — message ordering is already solved, lock-free. The advisory lock exists only to manufacture the gap-free per-session counter.

2. Goals / Non-Goals

Goals

  • G1: Remove the global pg_advisory_xact_lock from the message-create path.
  • G2: Preserve the existing public contract of seq_in_session: gap-free, contiguous, monotonically increasing per session, starting at 1.
  • G3: Scope all write serialization to within a single tenant schema, so no tenant can ever block another’s pool slots.
  • G4: Keep the (workspace_name, session_name, seq_in_session) unique constraint as a correctness backstop.
  • G5: Reduce the seq-assignment critical path from two round-trips (pg_advisory_xact_lock + SELECT max(seq)) to one (UPDATE … RETURNING).
  • G6: Provide a clean, per-schema migration with backfill.

Non-Goals

  • NG1: Removing same-session write serialization entirely. Gap-free numbering inherently requires a single assignment point per session; the goal is to make that point tenant-local and brief, not to eliminate it. (A fully lock-free design that drops gap-free seq_in_session is discussed in §7 as a possible follow-up.)
  • NG2: Changing the seq_in_session semantics consumed by the summarizer or dialectic context windows (see §3).
  • NG3: Changes to Groudon provisioning, Supavisor config, or the per-tenant schema topology (tracked separately in the connection-saturation investigation).

3. Contiguity Is a Hard Requirement

seq_in_session is not merely an ordering key; downstream logic depends on contiguous, gap-free values. Any solution that introduces gaps (e.g. a raw SEQUENCE, which skips on rollback/cache) is disqualified:

  • src/utils/summarizer.py:292,295 — triggers short/long summaries on seq % messages_per_short_summary == 0 and % messages_per_long_summary == 0 (every 20 / 60). A gap silently skips a summary trigger.
  • src/utils/summarizer.py:419 — start_seq = max(seq - messages_per_summary + 1, 1), then a between(start_seq, end_seq) range query expects exactly N contiguous messages.
  • src/utils/agent_tools.py:1162 and src/crud/message.py:152 — dialectic context windows select messages within ±N of a target seq via between, assuming adjacency.

The chosen design must produce the same gap-free sequence these consumers already rely on.

4. Proposed Design — Atomic Counter on the Session Row

Add a high-water-mark counter to the sessions table and reserve a contiguous block of sequence numbers in a single atomic statement, replacing both the advisory lock and the max(seq) read.

4.1 Schema change

# src/models.py — Session
message_seq_counter: Mapped[int] = mapped_column(
    BigInteger, nullable=False, server_default=text("0")
)

4.2 New create_messages flow

async def create_messages(db, messages, workspace_name, session_name):
    await get_or_create_session(db, session=..., workspace_name=workspace_name)
 
    n = len(messages)
    # Atomic reserve: locks ONLY this session row, in THIS schema, until commit.
    high_water = await db.scalar(
        text("""
            UPDATE sessions
            SET message_seq_counter = message_seq_counter + :n
            WHERE name = :session_name AND workspace_name = :workspace_name
            RETURNING message_seq_counter
        """),
        {"n": n, "session_name": session_name, "workspace_name": workspace_name},
    )
    first_seq = high_water - n + 1  # contiguous block [first_seq .. high_water]
 
    message_objects = [
        models.Message(seq_in_session=first_seq + offset, ...)
        for offset, message in enumerate(messages)
    ]
    db.add_all(message_objects)
    await db.commit()  # releases the row lock; embeddings happen after, as today
    ...

4.3 Why this satisfies every constraint

  • Gap-free preserved. The counter advances inside the same transaction as the inserts. On rollback the UPDATE rolls back too, so no number is consumed — unlike a SEQUENCE, which would leave gaps.
  • Tenant-local. The row lock is on sessions[session_name] within the calling schema. Cross-tenant false sharing is structurally impossible — the production blast radius is eliminated.
  • No global namespace, no hash collisions. Removes hashtext entirely.
  • One round-trip. UPDATE … RETURNING replaces pg_advisory_xact_lock + SELECT max(seq).
  • Observable + deadlock-safe. A normal row lock appears in pg_locks against the real relation and participates in deadlock detection.
  • Brief critical section. The lock is held from the UPDATE to commit() across a single bulk INSERT (no external calls — embeddings already run post-commit per the “never hold a DB session across external I/O” rule in the repo CLAUDE.md), rather than across a 5s lock_timeout window.
Before:  tenant A · session S ─┐
         tenant B · session S ─┼─► one global advisory key ─► serialize (false sharing)
         tenant C · session S ─┘

After:   tenant A · session S ─► row lock A.sessions[S] ─┐
         tenant B · session S ─► row lock B.sessions[S] ─┼─► independent, parallel
         tenant C · session S ─► row lock C.sessions[S] ─┘

4.4 Summarizer simplification (optional, same PR)

The summarizer’s modulo triggers can read the returned high_water directly rather than re-deriving from inserted rows, decoupling the trigger from the seq column. Low-risk; can be deferred.

5. Migration & Rollout

The unique constraint stays throughout — it is the correctness backstop that catches any logic error during rollout.

5.1 Alembic migration (runs per-schema, as all Honcho migrations do)

ALTER TABLE sessions ADD COLUMN message_seq_counter BIGINT NOT NULL DEFAULT 0;
 
-- Backfill the high-water mark from existing messages.
UPDATE sessions s
SET message_seq_counter = COALESCE((
    SELECT max(m.seq_in_session)
    FROM messages m
    WHERE m.workspace_name = s.workspace_name AND m.session_name = s.name
), 0);

Backfill notes:

  • On large tenants this UPDATE touches every session row; run it in batches if a single statement risks lock/timeout pressure. (Existing migrations run over the session pooler, port 5432, per migrations/env.py:ensure_session_pooler.)
  • Historical messages assigned to a default session (src/models.py:216 note) are covered by the max(seq) subquery.

5.2 Sequencing

  1. (Migration) Add + backfill message_seq_counter. No code reads it yet — safe to deploy ahead of the code change.
  2. (Code) Swap create_messages to the UPDATE … RETURNING path; delete the advisory lock and the max(seq) read.
  3. (Verify) Confirm in production that pg_stat_activity shows zero wait_event='advisory' on the message path, and that connection spikes no longer correlate with message bursts.

5.3 Optional interim hotfix (independent of this spec)

If a saturation event recurs before this lands, a one-line mitigation scopes the existing advisory key to the schema and widens it to 64 bits, removing cross-tenant false sharing without the migration:

await db.execute(
    text("SELECT pg_advisory_xact_lock(hashtextextended(:key, 0))"),
    {"key": f"{settings.DB.SCHEMA}:{workspace_name}:{session_name}"},
)

This is a stopgap, not the destination — it keeps the global namespace, the extra round-trip, and the 5s pin window. Prefer landing §4.

6. Testing

  • Concurrency: N concurrent create_messages to the same session produce a contiguous 1..M sequence with no gaps and no duplicates (assert against the unique constraint never firing).
  • Batch reservation: a batch of size N reserves exactly N contiguous seqs; interleaved batches from two callers partition the space without overlap.
  • Rollback leaves no gap: a transaction that reserves then raises before commit consumes zero sequence numbers (next writer continues contiguously).
  • Cross-tenant isolation (integration, shared-DB/multi-schema fixture): concurrent writes to identically-named sessions in two schemas do not serialize against each other (no shared lock).
  • Summarizer triggers: short/long summaries still fire at the correct multiples after the change.
  • Migration backfill: message_seq_counter == max(seq_in_session) for every pre-existing session; new sessions start at 0 → first message gets seq 1.

7. Alternatives Considered

  • Schema-scoped advisory key (§5.3) — kept as an interim hotfix only. Fixes cross-tenant sharing but retains every other advisory-lock downside.
  • Per-session SEQUENCE — rejected: sequences skip on rollback/cache, violating the gap-free contract (§3), and creating a sequence object per session bloats the catalog.
  • Optimistic insert + retry on unique violation — no lock at all; on unique_violation recompute seq and retry. Attractive because genuine same-session concurrency is usually low, but has tail-risk (retry storms / livelock under a genuinely hot session) and re-pins a pooled connection per retry. Rejected as the default; the counter gives deterministic behavior.
  • Drop gap-free seq_in_session; order on global id (the true lock-free design) — order messages by the existing BigInteger Identity() id, rewrite the summarizer modulo trigger to fire off a plain message_count and the dialectic context windows to use id-relative row_number(). This removes same-session serialization entirely. Deferred as a larger follow-up because it touches the summarizer and multiple dialectic tools (§3); the counter design delivers the production fix with far less surface area.

8. Open Questions

  • Should the summarizer be refactored to consume the returned high_water in this PR (§4.4) or a follow-up?
  • Batch size threshold above which the §5.1 backfill should be chunked for the largest tenants.
  • Do we want a follow-up tracking the fully lock-free design (§7, last bullet)?

Graph View

Backlinks