Prisma Postres error prepared statement \"s0\" already exists

Question

I have a Next.js project where I'm using Prisma to read from Postgresql.

I have code like:

const rows = await prisma.receipts.findMany({
      where: {
        action_receipts: {
          // https://www.prisma.io/docs/concepts/components/prisma-client/filtering-and-sorting#filter-on-relations
          signer_account_id: {
            equals: accountName,
          },
        },
      },
      orderBy: {
        included_in_block_timestamp: 'desc',
      },
      take: 2, 
    });

I'm often getting errors like:

 error: PrismaClientUnknownRequestError: 
  Invalid `prisma.receipts.findMany()` invocation:
  
  
    Error occurred during query execution:
  ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Error { kind: Db, cause: Some(DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState("42P05"), message: "prepared statement \"s0\" already exists", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("prepare.c"), line: Some(480), routine: Some("StorePreparedStatement") }) }) })

(Sometimes instead of "s0" it says something else though, such as "s8".)

What does this mean, and how can I avoid it?

It seems like the problem often goes away if I stop my local server and wait a minute and then start again via yarn dev and then try my Prisma query again. But I'm oblivious to the root cause, which I want to identify and solve.

Dordrecht

Are you using pgbouncer? I found this issue - github.com/prisma/prisma/issues/4752 This describes error which you are facing. – Villon

@NurulSundarani Thanks. I'd found that issue too, but I've never heard of pgbouncer so doubted that I'm using it. I wonder if it would appear in yarn.lock, which I haven't checked yet. – Dordrecht

Were you able to solve this? – Langsdon

My intent is always to follow up and write an answer (if I ever learn one) for every question I ask. So I doubt I ever found one for this. – Dordrecht

Answer 1

I restarted the project on superbase and it worked.

Go to the settings on the supabase project file and click on restart project

Asteria

Answer 2

In my case I was using database pooling (on Supabase) but not including the directUrl property in the Prisma schema, like:

datasource db {
    provider  = "postgresql"
    url       = env("DATABASE_URL")
    directUrl = env("DIRECT_URL")
 }

Everything worked without this until I came to do a migration (when updating a Vercel deployment where changes had been made to the Prisma schema).

In .env I added:

# PostgreSQL connection string used for migrations
DIRECT_URL="postgres://postgres:[YOUR-PASSWORD]@db.[YOUR-PROJECT-REF].supabase.co:5432/postgres"

In addition to:

# PostgreSQL connection string with Supavisor config — used by Prisma Client
DATABASE_URL="postgres://postgres.[YOUR-PROJECT-ID]:[YOUR-PASSWORD]@aws-0-[REGION].pooler.supabase.com:6543/postgres?pgbouncer=true&connection_limit=1"

(Obviously Supabase-specific!)

Then I ran npx prisma db push locally and redeployed on Vercel (via GitHub) and voila.

(This info was on https://supabase.com/partners/integrations/prisma, although the page seems to have vanished in the last few hours.)

Guiltless

Answer 3

this worked for me but every time I do the consult again I face the same error so I have to be restarting the supabase project

Transmittal

Answer 4

according to the supabase integration with prisma docs, you need to append ?pgbouncer=true&connection_limit=1 to your DATABASE_URL. pgbouncer=true disables Prisma from generating prepared statements. you don't necessarily need to restart your database always.

please, read the official documentation => https://supabase.com/partners/integrations/prisma

To do this, set the connection mode to Transaction in the database settings page and copy the connection string and append ?pgbouncer=true&connection_limit=1. pgbouncer=true disables Prisma from generating prepared statements. This is required since our connection pooler does not support prepared statements in transaction mode yet. The connection_limit=1 parameter is only required if you are using Prisma from a serverless environment.

Cloninger

Answer 5

The key is to add the query parameter pgbouncer=true to your Transaction connection pooler string, i.e. DATABASE_URL with port 6543 in your .env file. Without this setting, you may also encounter similar prepared statement related errors such as:

“prepared statement \“s#\” does not exist”

prepared statement \"s0\" already exists

From the official docs:

if you were executing the same query over-and-over, but only changing the arguments or some other small aspect, prepared statements offer performance benefits. Prisma will try to create prepared statements in the background, but Supavisor does not support them and leads to the following error. To prevent Supavisor from erroring, it is necessary to add the following query parameter to your connection strings: pgbouncer=true to the Transaction connection pooler string

The Session pooler (connection string with port 5432) already supports prepared statements, and is used for long-lived connections.

Disavowal