prisma
Postgres: too many bind variables in prepared statement on `findMany()`
Bug description
findMany() fails with error:
PrismaClientKnownRequestError:
Invalid `prisma.a.findMany()` invocation:
Assertion violation on the database: `too many bind variables in prepared statement, expected maximum of 32767, received 32769`
at Cn.handleRequestError (/Users/joaomlneto/git/joaomlneto/prisma-bug/node_modules/@prisma/client/runtime/library.js:123:6817)
at Cn.handleAndLogRequestError (/Users/joaomlneto/git/joaomlneto/prisma-bug/node_modules/@prisma/client/runtime/library.js:123:6206)
at Cn.request (/Users/joaomlneto/git/joaomlneto/prisma-bug/node_modules/@prisma/client/runtime/library.js:123:5926)
at async l (/Users/joaomlneto/git/joaomlneto/prisma-bug/node_modules/@prisma/client/runtime/library.js:128:9968)
at async file:///Users/joaomlneto/git/joaomlneto/prisma-bug/index.ts:22:1 {
code: 'P2035',
clientVersion: '5.5.2',
meta: {
database_error: 'too many bind variables in prepared statement, expected maximum of 32767, received 32769'
}
}
How to reproduce
- Create table
Awith a composite ID. - Create table
Bthat has an ID foreign key referencingA. - Populate first table with at least
16384items. - Attempt to execute
findMany()on the first table, while also selecting related entries from the second table.
const isInitialized = (await prisma.a.count()) > 0;
if (!isInitialized) {
console.log('going to initialize')
await prisma.a.createMany({
data: [...Array(16384).keys()].map(i => ({
key1: i,
key2: i,
}))
})
console.log('initialized successfully!')
}
console.log('going to run buggy query')
await prisma.a.findMany({
select: {
key1: true,
key2: true,
children: {
select: {
bKey: true,
},
},
},
})
Expected behavior
Query executes successfully.
Prisma information
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
model A {
key1 Int
key2 Int
children B[]
@@id([key1, key2])
}
model B {
a A @relation(fields: [key1, key2], references: [key1, key2])
key1 Int
key2 Int
bKey Int
@@id([key1, key2, bKey])
}
await prisma.a.findMany({
select: {
key1: true,
key2: true,
children: {
select: {
bKey: true,
},
},
},
})
Environment & setup
- OS: macOS
- Database: PostgreSQL, CockroachDB
- Node.js version:
v20.9.0
Prisma Version
prisma : 5.5.2
@prisma/client : 5.5.2
Current platform : darwin-arm64
Query Engine (Node-API) : libquery-engine aebc046ce8b88ebbcb45efe31cbe7d06fd6abc0a (at node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
Schema Engine : schema-engine-cli aebc046ce8b88ebbcb45efe31cbe7d06fd6abc0a (at node_modules/@prisma/engines/schema-engine-darwin-arm64)
Schema Wasm : @prisma/prisma-schema-wasm 5.5.1-1.aebc046ce8b88ebbcb45efe31cbe7d06fd6abc0a
Default Engines Hash : aebc046ce8b88ebbcb45efe31cbe7d06fd6abc0a
Studio : 0.494.0
Is this a new bug after an update or did you just notice with the newest version by chance?
Just noticed it with the newest Prisma version.
I've just migrated away from MySQL/Vitess and things break using Postgres/Cockroach.
Are you talking to a PostgreSQL database, or to a CockroachDB one? (We have a special provider for CockroachDB, because turns out their compatibility is... flexible. https://www.prisma.io/docs/concepts/database-connectors/cockroachdb)
their compatibility is... flexible.
😆 It happens with both :) The example above is for PostgreSQL. However, I just changed the provider to cockroachdb in my schema.prisma and could reproduce it with CockroachDB as well.
Since CockroachDB's speaks primarily(?) PostgreSQL and this error is related to the query generated, combined with issues related to the PostgreSQL driver, it makes sense in my head. I think it's mostly similar to https://github.com/prisma/prisma/issues/9326#issuecomment-1260546512, but maybe also https://github.com/prisma/prisma/issues/8832, and the number 32768 also appears on open issues https://github.com/prisma/prisma/issues/19901 https://github.com/prisma/prisma/issues/20760.
going to run buggy query
prisma:query SELECT "public"."A"."key1", "public"."A"."key2" FROM "public"."A" WHERE 1=1 OFFSET $1
prisma:error
Invalid `prisma.a.findMany()` invocation:
Assertion violation on the database: `too many bind variables in prepared statement, expected maximum of 32767, received 32769`
/Users/joaomlneto/git/joaomlneto/prisma-bug/node_modules/@prisma/client/runtime/library.js:123
`)}var Ji=({clientMethod:e,activeProvider:t,activeProviderFlavour:r})=>n=>{r!==void 0&&(t=r);let i="",o;if(Array.isArray(n)){let[s,...a]=n;i=s,o={values:gr(a||[]),__prismaRawParameters__:!0}}else switch(t){case"sqlite":case"mysql":{i=n.sql,o={values:gr(n.values),__prismaRawParameters__:!0};break}case"cockroachdb":case"postgresql":case"postgres":{i=n.text,o={values:gr(n.values),__prismaRawParameters__:!0};break}case"sqlserver":{i=il(n),o={values:gr(n.values),__prismaRawParameters__:!0};break}default:throw new Error(`The ${t} provider does not support ${e}`)}return o?.values?ll(`prisma.${e}(${i}, ${o.values})`):ll(`prisma.${e}(${i})`),{query:i,parameters:o}},ul={requestArgsToMiddlewareArgs(e){return[e.strings,...e.values]},middlewareArgsToRequestArgs(e){let[t,...r]=e;return new oe(t,r)}},cl={requestArgsToMiddlewareArgs(e){return[e]},middlewareArgsToRequestArgs(e){return e[0]}};function Hi(e){return function(r){let n,i=(o=e)=>{try{return o===void 0||o?.kind==="itx"?n??(n=pl(r(o))):pl(r(o))}catch(s){return Promise.reject(s)}};return{then(o,s){return i().then(o,s)},catch(o){return i().catch(o)},finally(o){return i().finally(o)},requestTransaction(o){let s=i(o);return s.requestTransaction?s.requestTransaction(o):s},[Symbol.toStringTag]:"PrismaPromise"}}}function pl(e){return typeof e.then=="function"?e:Promise.resolve(e)}var dl={isEnabled(){return!1},getTraceParent(){return"00-10-10-00"},async createEngineSpan(){},getActiveContext(){},runInChildSpan(e,t){return t()}},Wi=class{isEnabled(){return this.getGlobalTracingHelper().isEnabled()}getTraceParent(t){return this.getGlobalTracingHelper().getTraceParent(t)}createEngineSpan(t){return this.getGlobalTracingHelper().createEngineSpan(t)}getActiveContext(){return this.getGlobalTracingHelper().getActiveContext()}runInChildSpan(t,r){return this.getGlobalTracingHelper().runInChildSpan(t,r)}getGlobalTracingHelper(){return globalThis.PRISMA_INSTRUMENTATION?.helper??dl}};function ml(e){return e.includes("tracing")?new Wi:dl}function fl(e,t=()=>{}){let r,n=new Promise(i=>r=i);return{then(i){return--e===0&&r(t()),i?.(n)}}}var Kd=["$connect","$disconnect","$on","$transaction","$use","$extends"],zi=Kd;function gl(e){return typeof e=="string"?e:e.reduce((t,r)=>{let n=typeof r=="string"?r:r.level;return n==="query"?t:t&&(r==="info"||t==="info")?"info":n},void 0)}var wn=class{constructor(){this._middlewares=[]}use(t){this._middlewares.push(t)}get(t){return this._middlewares[t]}has(t){return!!this._middlewares[t]}length(){return this._middlewares.length}};var hl=S(li());function Pn(e){return typeof e.batchRequestIdx=="number"}function vn(e){return e===null?e:Array.isArray(e)?e.map(vn):typeof e=="object"?Qd(e)?Gd(e):pt(e,vn):e}function Qd(e){return e!==null&&typeof e=="object"&&typeof e.$type=="string"}function Gd({$type:e,value:t}){switch(e){case"BigInt":return BigInt(t);case"Bytes":return Buffer.from(t,"base64");case"DateTime":return new Date(t);case"Decimal":return new Pe(t);case"Json":return JSON.parse(t);default:He(t,"Unknown tagged value")}}function yl(e){if(e.action!=="findUnique"&&e.action!=="findUniqueOrThrow")return;let t=[];return e.modelName&&t.push(e.modelName),e.query.arguments&&t.push(Yi(e.query.arguments)),t.push(Yi(e.query.selection)),t.join("")}function Yi(e){return`(${Object.keys(e).sort().map(r=>{let n=e[r];return typeof n=="object"&&n!==null?`(${r} ${Yi(n)})`:r}).join(" ")})`}var Jd={aggregate:!1,aggregateRaw:!1,createMany:!0,createOne:!0,deleteMany:!0,deleteOne:!0,executeRaw:!0,findFirst:!1,findFirstOrThrow:!1,findMany:!1,findRaw:!1,findUnique:!1,findUniqueOrThrow:!1,groupBy:!1,queryRaw:!1,runCommandRaw:!0,updateMany:!0,updateOne:!0,upsertOne:!0};function Zi(e){return Jd[e]}var Tn=class{constructor(t){this.options=t;this.tickActive=!1;this.batches={}}request(t){let r=this.options.batchBy(t);return r?(this.batches[r]||(this.batches[r]=[],this.tickActive||(this.tickActive=!0,process.nextTick(()=>{this.dispatchBatches(),this.tickActive=!1}))),new Promise((n,i)=>{this.batches[r].push({request:t,resolve:n,reject:i})})):this.options.singleLoader(t)}dispatchBatches(){for(let t in this.batches){let r=this.batches[t];delete this.batches[t],r.length===1?this.options.singleLoader(r[0].request).then(n=>{n instanceof Error?r[0].reject(n):r[0].resolve(n)}).catch(n=>{r[0].reject(n)}):(r.sort((n,i)=>this.options.batchOrder(n.request,i.request)),this.options.batchLoader(r.map(n=>n.request)).then(n=>{if(n instanceof Error)for(let i=0;i<r.length;i++)r[i].reject(n);else for(let i=0;i<r.length;i++){let o=n[i];o instanceof Error?r[i].reject(o):r[i].resolve(o)}}).catch(n=>{for(let i=0;i<r.length;i++)r[i].reject(n)}))}}get[Symbol.toStringTag](){return"DataLoader"}};var Hd=O("prisma:client:request_handler"),Cn=class{constructor(t,r){this.logEmitter=r,this.client=t,this.dataloader=new Tn({batchLoader:ca(async({requests:n,customDataProxyFetch:i})=>{let{transaction:o,otelParentCtx:s}=n[0],a=n.map(p=>p.protocolQuery),l=this.client._tracingHelper.getTraceParent(s),u=n.some(p=>Zi(p.protocolQuery.action));return(await this.client._engine.requestBatch(a,{traceparent:l,transaction:Wd(o),containsWrite:u,customDataProxyFetch:i})).map((p,d)=>{if(p instanceof Error)return p;try{return this.mapQueryEngineResult(n[d],p)}catch(f){return f}})}),singleLoader:async n=>{let i=n.transaction?.kind==="itx"?xl(n.transaction):void 0,o=await this.client._engine.request(n.protocolQuery,{traceparent:this.client._tracingHelper.getTraceParent(),interactiveTransaction:i,isWrite:Zi(n.protocolQuery.action),customDataProxyFetch:n.customDataProxyFetch});return this.mapQueryEngineResult(n,o)},batchBy:n=>n.transaction?.id?`transaction-${n.transaction.id}`:yl(n.protocolQuery),batchOrder(n,i){return n.transaction?.kind==="batch"&&i.transaction?.kind==="batch"?n.transaction.index-i.transaction.index:0}})}async request(t){try{return await this.dataloader.request(t)}catch(r){let{clientMethod:n,callsite:i,transaction:o,args:s}=t;this.handleAndLogRequestError({error:r,clientMethod:n,callsite:i,transaction:o,args:s})}}mapQueryEngineResult({dataPath:t,unpacker:r},n){let i=n?.data,o=n?.elapsed,s=this.unpack(i,t,r);return process.env.PRISMA_CLIENT_GET_TIME?{data:s,elapsed:o}:s}handleAndLogRequestError(t){try{this.handleRequestError(t)}catch(r){throw this.logEmitter&&this.logEmitter.emit("error",{message:r.message,target:t.clientMethod,timestamp:new Date}),r}}handleRequestError({error:t,clientMethod:r,callsite:n,transaction:i,args:o}){if(Hd(t),zd(t,i)||t instanceof Me)throw t;if(t instanceof U&&Yd(t)){let a=bl(t.meta);En({args:o,errors:[a],callsite:n,errorFormat:this.client._errorFormat,originalMethod:r,clientVersion:this.client._clientVersion})}let s=t.message;throw n&&(s=Pt({callsite:n,originalMethod:r,isPanic:t.isPanic,showColors:this.client._errorFormat==="pretty",message:s})),s=this.sanitizeMessage(s),t.code?new U(s,{code:t.code,clientVersion:this.client._clientVersion,meta:t.meta,batchRequestIdx:t.batchRequestIdx}):t.isPanic?new ue(s,this.client._clientVersion):t instanceof K?new K(s,{clientVersion:this.client._clientVersion,batchRequestIdx:t.batchRequestIdx}):t instanceof F?new F(s,this.client._clientVersion):t instanceof ue?new ue(s,this.client._clientVersion):(t.clientVersion=this.client._clientVersion,t)}sanitizeMessage(t){return this.client._errorFormat&&this.client._errorFormat!=="pretty"?(0,hl.default)(t):t}unpack(t,r,n){if(!t||(t.data&&(t=t.data),!t))return t;let i=Object.values(t)[0],o=r.filter(a=>a!=="select"&&a!=="include"),s=vn(Ai(i,o));return n?n(s):s}get[Symbol.toStringTag](){return"RequestHandler"}};function Wd(e){if(e){if(e.kind==="batch")return{kind:"batch",options:{isolationLevel:e.isolationLevel}};if(e.kind==="itx")return{kind:"itx",options:xl(e)};He(e,"Unknown transaction kind")}}function xl(e){return{id:e.id,payload:e.payload}}function zd(e,t){return Pn(e)&&t?.kind==="batch"&&e.batchRequestIdx!==t.index}function Yd(e){return e.code==="P2009"||e.code==="P2012"}function bl(e){if(e.kind==="Union")return{kind:"Union",errors:e.errors.map(bl)};if(Array.isArray(e.selectionPath)){let[,...t]=e.selectionPath;return{...e,selectionPath:t}}return e}var El="5.5.2";var wl=El;function Pl(e){return e.map(t=>{let r={};for(let n of Object.keys(t))r[n]=vl(t[n]);return r})}function vl({prisma__type:e,prisma__value:t}){switch(e){case"bigint":return BigInt(t);case"bytes":return Buffer.from(t,"base64");case"decimal":return new Pe(t);case"datetime":case"date":return new Date(t);case"time":return new Date(`1970-01-01T${t}Z`);case"array":return t.map(vl);default:return t}}var Ml=S(Ui());var q=class extends Error{constructor(t){super(t+`
PrismaClientKnownRequestError:
Invalid `prisma.a.findMany()` invocation:
Assertion violation on the database: `too many bind variables in prepared statement, expected maximum of 32767, received 32769`
at Cn.handleRequestError (/Users/joaomlneto/git/joaomlneto/prisma-bug/node_modules/@prisma/client/runtime/library.js:123:6817)
at Cn.handleAndLogRequestError (/Users/joaomlneto/git/joaomlneto/prisma-bug/node_modules/@prisma/client/runtime/library.js:123:6206)
at Cn.request (/Users/joaomlneto/git/joaomlneto/prisma-bug/node_modules/@prisma/client/runtime/library.js:123:5926)
at async l (/Users/joaomlneto/git/joaomlneto/prisma-bug/node_modules/@prisma/client/runtime/library.js:128:9968)
at async file:///Users/joaomlneto/git/joaomlneto/prisma-bug/index.ts:22:1 {
code: 'P2035',
clientVersion: '5.5.2',
meta: {
database_error: 'too many bind variables in prepared statement, expected maximum of 32767, received 32769'
}
}
Node.js v20.9.0
Process finished with exit code 1
Looks related to other issues like you mentioned
- https://github.com/prisma/prisma/issues/19736
- https://github.com/prisma/prisma/issues/19901
- https://github.com/prisma/prisma/issues/20760
This message mentions it was fixed in 4.4.0 https://github.com/prisma/prisma/issues/8832#issuecomment-1260533913, related tests: https://github.com/prisma/prisma/blob/main/packages/client/tests/functional/issues/8832/tests.ts
Obviously you have a more recent version here, so it's something to be reproduced and investigated.
Note: I could reproduce this with Prisma 5.5.2 using PostgreSQL 10.21
PrismaClientKnownRequestError:
Invalid `prisma.a.findMany()` invocation in
/Users/j42/Repros/21648/main.ts:23:18
20
21 console.log("going to run buggy query");
22
→ 23 await prisma.a.findMany(
Assertion violation on the database: `too many bind variables in prepared statement, expected maximum of 32767, received 32769`
at Cn.handleRequestError (/Users/j42/Repros/21648/node_modules/@prisma/client/runtime/library.js:123:6817)
at Cn.handleAndLogRequestError (/Users/j42/Repros/21648/node_modules/@prisma/client/runtime/library.js:123:6206)
at Cn.request (/Users/j42/Repros/21648/node_modules/@prisma/client/runtime/library.js:123:5926)
at l (/Users/j42/Repros/21648/node_modules/@prisma/client/runtime/library.js:128:9968)
at main (/Users/j42/Repros/21648/main.ts:23:3)
Internal note: For some reason our chunking logic does not trigger here, which should split the query into multiple that can be prepared successfully. This leads to this error message.
Simpler recreation, no FKs or compound ids req'd:
await prisma.account.findMany({
where: {
name: 'Expense',
companyId: v4(),
id: {
in: _.times(() => v4(), 100_000),
},
},
});
If I remove the two arguments (name and companyId) it chunks exactly as expected:
await prisma.account.findMany({
where: {
id: {
in: _.times(() => v4(), 100_000),
},
},
});
What makes you think that is a simpler reproduction for the same problem @manuphatak? Isn't this just the same error message and symptom, but triggered by a very different query? I suggest you open a new issue so we can investigate this independently.
+1. My team is seeing this on our end as well for an .updateMany query
Assertion violation on the database: `too many bind variables in prepared statement, expected maximum of 32767, received 60160
Can you please also open a new issue @elaskowski? Please share the schema and full query. This is just one error message, that can have many reasons and we need to know about them to be able to fix them. Thanks.
Hey folks,
We have spotted this issue as potentially being solved by the new relationJoins preview feature, which uses JOINs to resolve relations in a single query.
If you have the time, we'd love for you to try it out and let us know if it has indeed fixed your issue. You can learn more about it on our release notes here.
Thank you 🙏
Hey folks,
We have spotted this issue as potentially being solved by the new
relationJoinspreview feature, which usesJOINs to resolve relations in a single query.If you have the time, we'd love for you to try it out and let us know if it has indeed fixed your issue. You can learn more about it on our release notes here.
Thank you 🙏
This has solved the issue for me.
I have just hit this error myself, after adding relations to a large table.
@janpio You wrote:
Internal note: For some reason our chunking logic does not trigger here, which should split the query into multiple that can be prepared successfully. This leads to this error message.
I was able to work around this problem by batching my findMany calls into batches of 30k with simple take and skip logic.
It's been almost a year since this issue was opened, and it is trivial to reproduce. Can we expect a fix anytime soon? A userland "fix" is ~10 LOC, so it would be great to see this fixed properly.
We have spotted this issue as potentially being solved by the new
relationJoinspreview feature, which usesJOINs to resolve relations in a single query.
This worked for me as well! To save a click for anyone who lands here (like me), here's how to enable that preview:
// schema.prisma
generator client {
provider = "prisma-client-js"
previewFeatures = ["relationJoins"]
}
Currently running into this same 'too many bind variables in prepared statement' issue. However, when I enable the relationJoins preview feature, pretty much any JOIN query just fails.
Are there any other workarounds other than manually batching myself?
when I enable the relationJoins preview feature, pretty much any JOIN query just fails
That's obviously not supposed to happen. Could you please open an issue (or issues) describing what exactly fails? Thank you!
EDIT: opened a new issue, #25645
Hi, documenting here but will also create another issue.
Backend is Nest.js deployed to AWS with Docker containers
Stack trace:
PrismaClientKnownRequestError:
Invalid `prisma.hubSubscriber.findMany()` invocation:
Query parameter limit exceeded error: Joined queries cannot be split into multiple queries..
at Ln.handleRequestError (/app/node_modules/@prisma/client/runtime/library.js:121:7753)
at Ln.handleAndLogRequestError (/app/node_modules/@prisma/client/runtime/library.js:121:7061)
at Ln.request (/app/node_modules/@prisma/client/runtime/library.js:121:6745)
at async l (/app/node_modules/@prisma/client/runtime/library.js:130:9633)
at async SubscriptionService.getTotalHubSubscribers (/app/src/subscription/subscription.service.ts:275:23)
at async <anonymous> (/app/node_modules/@opentelemetry/instrumentation-nestjs-core/src/instrumentation.ts:221:16)
In the code, this corresponds to
const subscriptionIds = await this.getSubscriptionIdsToHub({
hubId,
segmentId,
});
const subscribers = await this.prisma.hubSubscriber.findMany({
where: {
id: { in: subscriptionIds.map(({ id }) => id) },
},
});
And the subscriptionIds are gathered from
return await this.prisma.hubSubscriber.findMany({
take,
skip: page !== undefined && take ? take * page : undefined,
orderBy: sortOptions,
where: whereConditions,
select: {
id: true,
},
});
And our data models:
model HubSubscriber {
id String @id @default(uuid()) @db.Uuid
hubId String @db.Uuid
userId String @db.Uuid
importBatchId Int? @db.Integer()
createdAt DateTime @default(now()) @db.Timestamptz(6)
updatedAt DateTime @updatedAt @db.Timestamptz(6)
lastMessageDate DateTime? @db.Timestamptz(6)
isUnsubscribed Boolean @default(false)
hub Hub @relation(fields: [hubId], references: [id], onDelete: Cascade)
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
smsMarketingCampaigns SmsMarketingCampaign[]
importBatch ImportBatch? @relation(fields: [importBatchId], references: [id], onDelete: Cascade)
}
Note — this query executes perfectly fine when not using the relationJoins preview feature. And yes, the order of operations is a bit redundant, but just using it as an example here. There are some different queries that have trouble with autochunking that we're trying to solve by enabling relationJoins.