Driver adapter (pg): after a database error inside an interactive transaction, subsequent queries on the same client receive other queries' responses #30374

Package and version

@prisma/adapter-pg@7.8.0

What happened?

Summary

On a long-lived PrismaClient using @prisma/adapter-pg, a PostgreSQL error raised inside an interactive $transaction leaves the client returning responses that belong to different queries. The SQL ROLLBACK is issued successfully; the corruption appears on queries issued after the transaction has finished.

A findUnique for an existing row returns null, and the query after it fails with P2023 because it received the previous query's payload.

The same failure does not occur when the transaction fails via a plain JavaScript throw — only when PostgreSQL itself raises the error.

Environment

Adapter is constructed with nothing but a connection string, so it builds its own pg.Pool with library defaults:

const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL })
const prisma = new PrismaClient({ adapter })

No transaction options are used anywhere — all $transaction calls take the defaults.

Reproduction

Schema:

model User {
  id    String  @id @default(cuid())
  email String  @unique
  name  String?
}

const prisma = new PrismaClient({
  adapter: new PrismaPg({ connectionString: process.env.DATABASE_URL }),
})

const u = await prisma.user.create({ data: { email: 'a@example.test', name: 'BEFORE' } })

// Prior history on the client is REQUIRED — see note below.
for (let i = 0; i < 3; i++) {
  await prisma.user.findUnique({ where: { id: u.id }, select: { name: true } })
}
try {
  await prisma.$transaction(async (tx) => {
    await tx.user.update({ where: { id: u.id }, data: { name: 'WARM' } })
    throw new Error('warm-up rollback')          // JS throw — harmless
  })
} catch {}
await prisma.user.update({ where: { id: u.id }, data: { name: 'BEFORE' } })

// The failing transaction: PostgreSQL raises the error, not the callback.
try {
  await prisma.$transaction(async (tx) => {
    await tx.user.update({ where: { id: u.id }, data: { name: 'ROLLED' } })
    await tx.user.create({ data: { id: u.id, email: 'dup@example.test', name: 'DUP' } }) // P2002
  })
} catch (e) {
  console.log('transaction failed with:', e.code)        // P2002
}

console.log(await prisma.user.findUnique({ where: { id: u.id }, select: { name: true } }))
console.log(await prisma.user.count({ where: { id: u.id } }))

Note on the warm-up: a pristine client (create + one read, then the failing transaction) did not reproduce this for us. Adding prior query and transaction history to the client before the failing transaction reproduces it reliably. We have not narrowed which part of that history is required.

Observed output

transaction failed with: P2002
findUnique(name)  ->  null          // expected { name: 'BEFORE' } — the row exists
count()           ->  ERROR P2023

The P2023 message is the informative part — a count query received a row payload from an earlier, different query:

Missing data field (Value): '_count$_all';
node: {"type":"field","dbName":"_count$_all","fieldType":{"arity":"required","type":"int"}};
data: {"id":"<cuid>","email":"<test-email>"}

The identifiers shown in the error payload are from a disposable local test database and contain no production data.

In a longer run we observed the same across two client objects sharing one engine (a base client and a $extends-ed one): a user.create on the second client received a payload that had been produced by a findUnique … select: { email: true } issued earlier on the first. The condition does not clear on its own — every subsequent query on that client failed.

DEBUG=prisma:driver-adapter:pg confirms the transaction is unwound correctly:

[js::startTransaction] options: { "usePhantomQuery": false }
[js::execute_raw] { "sql": "BEGIN" }
[js::query_raw]   … the UPDATE …
[js::query_raw]   … the failing INSERT …
[js::execute_raw] { "sql": "ROLLBACK" }
[js::rollback]
[js::query_raw]   … the next query, which returns the wrong response …

What we ruled out

Not pg. The same sequence driven directly against pg.Pool — BEGIN → duplicate-key error (SQLSTATE 23505) → ROLLBACK → client.release() without the error argument → reuse — returns correct results:

pg Q1 SELECT id    -> [{"id":1}]
pg Q2 SELECT count -> [{"n":1}]

Not a JavaScript throw. Replacing the duplicate-key insert with throw new Error(...) in the same position leaves the client completely healthy (three follow-up queries of different shapes, all correct, on both a plain and an $extends-ed client).

Not process-level. A completely fresh PrismaClient + fresh PrismaPg + fresh pg.Pool, constructed in the same process immediately after the corruption, answered five queries of four different shapes correctly (findUnique by id, count, findUnique by unique field, aggregate, findMany).

Not a test-runner artifact. Reproduced across separate client objects, with the healthy fresh-stack control running in the same process and the same script.

What we are not claiming

We have deliberately not identified a root cause, and we would rather ask than guess:

• We are not claiming a poisoned pooled connection. Destroying the client also destroys its adapter and pool, so our experiment cannot separate "connection state" from "client/engine state".
• We are not claiming an engine bug.
• We did observe the original client answering correctly again after $disconnect(), but we are explicitly not characterising that as a recovery mechanism or a workaround. Five correct queries cannot rule out a still-bad connection that simply was not drawn.
• We are not claiming any version fixes this. We inspected @prisma/adapter-pg 7.10.0: after normalising a single formatting-level change (a duplicated values argument to client.query, removed in 7.10.0), the PgQueryable / PgTransaction / PrismaPgAdapter section is byte-identical to 7.8.0 — commit, rollback, performIO and every connection release unchanged. We have not empirically tested 7.10.0.

Search for existing issues

We did not find a matching report. The nearest candidates, and why each differs:

• Query compiler + Driver adapter causing "Missing data field (Value)" #27359 — "Missing data field" with driver adapters, but attributed to bigint field parsing. Ours occurs on ordinary String columns and only after a failed transaction.
• PostgreSQL adapter fails to parse String[] @db.Citext arrays (Error P2023) #28349 — P2023 from String[] @db.Citext array parsing. Not applicable.
• Batch $transaction() intermittently never settles on driver adapters — query lost inside client engine before adapter's startTransaction; interactive tx with same payload always succeeds #30206 — batch $transaction() never settles, BEGIN never sent. Ours sends BEGIN and ROLLBACK correctly and returns incorrect data rather than hanging.
• adapter-pg doesn't properly handle DB connection errors #27626 — adapter-pg does not validate connections at $connect(). Different phase; closed via fix(adapter-pg): Add handling of socket and TLS level errors #27682.

If this duplicates something we missed, we would appreciate the pointer.

Questions

1. Which layer is responsible for correlating responses to requests here — the client engine, @prisma/driver-adapter-utils, or @prisma/adapter-pg?
2. Is a database error raised inside an interactive transaction expected to leave the client safe for reuse? PgTransaction.rollback() releases the connection with this.client.release() (no error argument), whereas startTransaction's own failure path uses conn.release(error). We do not know whether that asymmetry is relevant — our pg-only control suggests it is not sufficient on its own.
3. Is there a supported mitigation, or a version in which this is known to be fixed?
4. Is there additional instrumentation you would like us to capture? We have a reproducible local setup and can run further experiments.

Impact

In a multi-tenant application, this is a data-correctness concern rather than only an availability concern: after the triggering transaction error, a subsequent query can receive a response belonging to a different query. Application-level tenant checks operate on query arguments and therefore cannot detect a response-level mismatch.

What did you expect to happen?

After the transaction rolls back following a PostgreSQL database error, subsequent queries on the same PrismaClient should continue to receive the response belonging to their own query.

The client should either remain usable after the rollback or fail explicitly, but it must not return a response produced by a different query.

Minimal reproduction

Schema:

model User {
  id    String  @id @default(cuid())
  email String  @unique
  name  String?
}

const prisma = new PrismaClient({
  adapter: new PrismaPg({ connectionString: process.env.DATABASE_URL }),
})

const u = await prisma.user.create({ data: { email: 'a@example.test', name: 'BEFORE' } })

// Prior history on the client is REQUIRED — see note below.
for (let i = 0; i < 3; i++) {
  await prisma.user.findUnique({ where: { id: u.id }, select: { name: true } })
}
try {
  await prisma.$transaction(async (tx) => {
    await tx.user.update({ where: { id: u.id }, data: { name: 'WARM' } })
    throw new Error('warm-up rollback')          // JS throw — harmless
  })
} catch {}
await prisma.user.update({ where: { id: u.id }, data: { name: 'BEFORE' } })

// The failing transaction: PostgreSQL raises the error, not the callback.
try {
  await prisma.$transaction(async (tx) => {
    await tx.user.update({ where: { id: u.id }, data: { name: 'ROLLED' } })
    await tx.user.create({ data: { id: u.id, email: 'dup@example.test', name: 'DUP' } }) // P2002
  })
} catch (e) {
  console.log('transaction failed with:', e.code)        // P2002
}

console.log(await prisma.user.findUnique({ where: { id: u.id }, select: { name: true } }))
console.log(await prisma.user.count({ where: { id: u.id } }))

Note on the warm-up: a pristine client (create + one read, then the failing transaction) did not reproduce this for us. Adding prior query and transaction history to the client before the failing transaction reproduces it reliably. We have not narrowed which part of that history is required.

Environment

• Node: 24.19.0
• Prisma: 7.8.0
• @prisma/client: 7.8.0
• @prisma/adapter-pg: 7.8.0
• @prisma/driver-adapter-utils: 7.8.0
• pg: 8.22.0
• Database: PostgreSQL, local test instance
• Runtime: Prisma Rust-free client engine with driver adapters
• OS: Windows 11

Additional context

No response