Aller au contenu

2026-08-08 — Neon pooler: intermittent SQLSTATE 25006 (read-only) on FOR UPDATE

TL;DR

On 2026-08-08 between ~05:09 and ~05:49 UTC, write-path workers intermittently failed SELECT … FOR UPDATE with Postgres SQLSTATE 25006 (cannot execute SELECT FOR UPDATE in a read-only transaction) while connected to the Neon read_write pooled endpoint. Same pods mixed success and failure. Worker restarts cleared the issue. No confirmed root cause. The error has not recurred since 05:49 UTC that day.

Neon found no endpoint/pooler event in their telemetry for the window. Their remaining hypothesis (pooled backend inheriting a read-only setting) is unconfirmed. Our session-level SET statement_timeout path is also ruled out: DATABASE_STATEMENT_TIMEOUT_MS was unset in production.

Impact

Area Effect
worker-bricks-purchase-p2p ~446 log errors — P2P creation watcher stuck / retrying
worker-bricks-assignation ~440 log errors — brick assignation watcher stuck / retrying
worker-played-lemonway-p2p ~16 log errors
worker-bricks-reservation-confirmation ~2 log errors
Customer-facing API Limited direct errors; purchase pipeline latency / backlog risk

Primary purchase state watchers (waiting_for_p2p_creation, waiting_for_assignation, etc.) could not lock rows while the bad sessions were in the pool.

Timeline (UTC)

Time Event
~05:05 Deploy reported in ops window (Datadog change stories empty for this service; deploy time is operator-reported)
05:09 First wave of 25006 errors on purchase-p2p / assignation workers
05:09–05:26 Peak error rate; same pods alternate OK and failing FOR UPDATE against the RW pooler hostname
05:27–05:34 Quieter minutes (gaps in log volume)
05:36–05:49 Secondary wave (likely overlapping staggered worker restarts)
05:49:06 Last observed 25006 log
After restarts Issue cleared; no further hits through at least 2026-08-12

What we verified

Destination was the RW pooler, not the replica

APM pg spans (service:bricks-api-postgres) on failing and successful FOR UPDATE queries during the window show:

peer.hostname / network.destination =
  ep-damp-bush-a2o2n4v7-pooler.eu-central-1.aws.neon.tech:5432

Zero spans to the read-only replica host ep-winter-surf-a2dgq04v for writers in that window. Writers were not misconfigured onto the RO endpoint.

App does not set read-only mode

Repo search: no SET TRANSACTION READ ONLY, no SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY, no default_transaction_read_only in application init.

Transactions are plain TypeORM / Kysely BEGIN → query → commit/rollback via safePgTransaction / ky_safePgTransaction.

SET statement_timeout was not active in prod

The codebase can run SET statement_timeout = ${timeoutMs} on pool connect when DATABASE_STATEMENT_TIMEOUT_MS is set:

  • projects/api/src/common/services/typeorm-config.service.ts
  • projects/api/src/__new/lib/postgres/get-app-shared-pg-pool.ts
  • projects/api/src/__new/lib/postgres/get-read-only-pg-pool.ts

In production that env var was unset, so those hooks logged statement_timeout not configured / skipped the SET and never sent it.

Internal note (Denis): when the flag is enabled in non-prod tests, issuing SET statement_timeout per connection on the Neon pooler has worked in practice — so Neon's "SET unsupported on pooler" claim does not match our observed behaviour, and in any case it does not apply to this outage.

Failing SQL (representative)

Workers claim work with FOR UPDATE … SKIP LOCKED, e.g.:

  • PurchaseWaitingForP2PWatcher — lock primary_purchase + wallet_transactions
  • PurchaseWaitingForBricksAssignationWatcher — lock primary_purchase

Driver path: pg → TypeORM QueryFailedError → Postgres routine PreventCommandIfReadOnly (utility.c).

Neon support (#00986843)

We opened a ticket with timeline, config (RW pooler vs separate RO replica), and later stack traces + connection-init details.

Round 1 — Neon on SET + pooler

Neon confirmed:

  • -pooler uses PgBouncer in transaction mode
  • Session-level SET / RESET are documented as not supported on pooled connections
  • Backend connections return to the pool after each transaction; unexpected session state can theoretically affect later clients

Their first mitigation ask assumed we issue SET statement_timeout on the pooler (remove it / use direct / use ALTER ROLE). That does not apply to this incident: DATABASE_STATEMENT_TIMEOUT_MS was unset in prod.

Round 2 — Neon after platform review (final reply)

Neon reviewed available logs with their team and did not identify any noteworthy endpoint or pooler event during 05:05–05:30 UTC.

Updated Neon docs list three common causes for SQLSTATE 25006 (Error: read-only transaction):

  1. Connected to a read replica — ruled out (APM → RW pooler only)
  2. Session/transaction explicitly set read-only — ruled out for our writers (no SET TRANSACTION READ ONLY / SET SESSION CHARACTERISTICS… / SET default_transaction_read_only in app code)
  3. A pooled backend inherited read-only state from a previous client that set default_transaction_read_only = on (or session characteristics) and did not reset it — not confirmed in our case; Neon still presents this as the remaining area to investigate, given intermittency + clears after worker restart

Neon asks us to audit app / migrations / scheduled jobs for session-level read-only settings; prefer transaction-scoped read-only; test session config over a direct (unpooled) connection.

Our audit after round 2

  • Writer app code / workers: no session-level read-only SET
  • Local integration DB init sets ALTER ROLE bricks_ro SET default_transaction_read_only = on for the read-only role only (projects/api/tests/integration/docker/postgres-init.sh). That is intentional for RO connections; PgBouncer pools are per-user, and failing APM spans used bricks_admin, not bricks_ro
  • Prod statement_timeout env still unset — unrelated to this outage

Root cause

Inconclusive.

Ruled out for this incident:

  • Writers pointed at the RO replica
  • App-issued read-only transaction/session commands on writers
  • App-issued SET statement_timeout on the pooler (env unset in prod)
  • A confirmed Neon endpoint/pooler event in their telemetry for that window

Still open (Neon’s remaining hypothesis, unproven):

  • A pooled backend for bricks_admin briefly carried inherited default_transaction_read_only (or equivalent) from some other client / script sharing that user+db pool — consistent with intermittent OK/KO and recovery after worker restarts, but no smoking gun in our codebase or Neon’s logs

Mitigation / follow-ups

Action Status Notes
Worker restarts Done (incident day) Cleared symptoms
Neon ticket Done (inconclusive) No platform event; inheritance theory unconfirmed
Audit session-level read-only settings Done No writer-side RO SET; RO role is role-level bricks_ro only
If enabling statement_timeout later Open Prefer Neon-recommended role-level ALTER ROLE … SET statement_timeout (or direct connection for session SETs) — ask Neon to confirm preferred approach for pooler apps
Optional: FOR UPDATE workers on direct endpoint Open Defense in depth if issue returns; costs max_connections
Datadog monitor on exact 25006 message Open Alert if the signature returns

How to investigate if it returns

  1. Datadog logs: env:prod "cannot execute SELECT FOR UPDATE in a read-only transaction"
  2. APM spans: service:bricks-api-postgres status:error @error.message:*read-only* — check peer.hostname
  3. Confirm writers still use RW pooler only; RO replica stays on read pools
  4. Confirm whether DATABASE_STATEMENT_TIMEOUT_MS is set (it was not during this incident)
  5. Restart affected workers to clear suspect pooled sessions
  6. Escalate to Neon with sample trace IDs + window; ask for pooler/compute events and whether any backend showed transaction_read_only / replica routing