ERROR: prepared statement "stmtcache_..." already exists (SQLSTATE 42P05)

What does the error look like in Go?

pgx wraps the server error in pgconn.PgError, whose message is the severity, the server message and the SQLSTATE code. Postgres lists 42P05 as duplicate_prepared_statement. The name is stmtcache_ followed by 48 hex characters, the first 24 bytes of the SHA-256 of the SQL text, so the same query always gets the same name.

ERROR: prepared statement "stmtcache_8f3c...e1" already exists (SQLSTATE 42P05)

How do I make pgx work with the Supabase transaction pooler?

Copy the transaction pooler string from the Connect dialog of your Supabase project (the docs say to copy the host from there, because it cannot be derived from the region) and add the pgx parameter. pgx reads default_query_exec_mode from the connection string and removes it before it connects. pgxpool.ParseConfig accepts the same parameters. The accepted values are cache_statement, cache_describe, describe_exec, exec and simple_protocol. The pgx docs say to prefer exec over simple_protocol, and say not to use describe_exec with transaction pooling.

DATABASE_URL=postgres://USER:PASSWORD@HOST:6543/postgres?default_query_exec_mode=exec

// or in code
cfg, err := pgxpool.ParseConfig(os.Getenv("DATABASE_URL"))
if err != nil {
  log.Fatal(err)
}
cfg.ConnConfig.DefaultQueryExecMode = pgx.QueryExecModeExec
pool, err := pgxpool.NewWithConfig(ctx, cfg)

Can I keep prepared statements instead?

Yes, on a connection that supports them. The Supabase troubleshooting article for this error says to connect directly or use the shared pooler in session mode on port 5432, and that the dedicated pooler supports prepared statements on PgBouncer 1.21 and later. The pgx docs say the same about PgBouncer 1.21.0 or newer with max_prepared_statements above zero. For serverless functions the Supabase connection guide recommends transaction mode, with the client created once and the pool set to 1, which pgx takes as pool_max_conns=1.

DATABASE_URL=postgres://USER:PASSWORD@HOST:6543/postgres?default_query_exec_mode=exec&pool_max_conns=1

Checklist

How to fix it, then check the fix

1. 01
   Find where the app opens its database connection:

   grep -rnE "pgxpool\.|pgx\.Connect|DATABASE_URL" --include=*.go .

2. 02Check which port the DATABASE_URL in your Vercel project (or Render, Railway or Fly service) uses. Port 6543 is the transaction pooler.
3. 03Add default_query_exec_mode=exec to that URL, or set cfg.ConnConfig.DefaultQueryExecMode = pgx.QueryExecModeExec after ParseConfig. Do not only set statement_cache_capacity=0: the default mode then fails with "cannot use QueryExecModeCacheStatement with disabled statement cache".
4. 04In a serverless function, create the pool once at package level instead of per request, and keep pool_max_conns small.
5. 05Redeploy, then call the endpoint several times in a row. Queries no longer use cached named statements, and the 42P05 error is gone from the logs.

Related

Related guides

• SupabaseVercelWhy does my Vercel app hit "Max client connections reached" on Supabase?Guide
• SupabaseRenderconnect ENETUNREACH on Render with SupabaseGuide
• VercelWhich env vars does my app read that are missing in Vercel production?Guide

Sources

Official docs behind this checklist

• Supabase, Connecting to your database
• Supabase, Error: prepared statement "XXX" already exists
• pgx v5 package documentation (PgBouncer, prepared statements, ParseConfig)
• pgx, conn.go (default mode, query exec modes, disabled cache error)
• pgx, internal/stmtcache/stmtcache.go (statement names)
• pgx, pgconn/errors.go (PgError message format)
• pgx, pgxpool/pool.go (ParseConfig, pool_max_conns)
• PostgreSQL, Error codes appendix (42P05)

FAQ

FAQ for builders before launch