DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 42P05 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 42P05: duplicate prepared statement

PostgreSQL error code 42P05 occurs when you attempt to create a prepared statement using a name that already exists in the current session. Since prepared statements are session-scoped, they persist until explicitly deallocated or the session ends, making name collisions a common issue in long-running applications or connection pool environments.

Top 3 Causes

1. Re-executing PREPARE Without Deallocating First

The most common cause: your application calls PREPARE with the same name twice in a session without cleaning up the first one.

-- First call: works fine
PREPARE get_user (int) AS
  SELECT * FROM users WHERE id = $1;

-- Second call in the same session: raises 42P05!
PREPARE get_user (int) AS
  SELECT * FROM users WHERE id = $1;
-- ERROR:  prepared statement "get_user" already exists
Enter fullscreen mode Exit fullscreen mode

Fix: Always deallocate before re-preparing.

-- Safe pattern
DEALLOCATE get_user;

PREPARE get_user (int) AS
  SELECT * FROM users WHERE id = $1;

EXECUTE get_user(1);
Enter fullscreen mode Exit fullscreen mode

Or check existence first:

DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM pg_prepared_statements WHERE name = 'get_user'
  ) THEN
    DEALLOCATE get_user;
  END IF;
END;
$$;

PREPARE get_user (int) AS
  SELECT * FROM users WHERE id = $1;
Enter fullscreen mode Exit fullscreen mode

2. PgBouncer Transaction Mode Conflicting with Driver Caching

When PgBouncer runs in transaction mode, connections are swapped between clients mid-session. Drivers like JDBC or psycopg2 that cache prepared statements on the server side will fail because the statement registered on one backend is not available on another — and retry attempts can trigger 42P05.

-- Check currently active prepared statements in your session
SELECT name, statement, prepare_time
FROM pg_prepared_statements;

-- Clean slate: deallocate everything
DEALLOCATE ALL;
Enter fullscreen mode Exit fullscreen mode

Fix: Disable server-side prepared statements when using PgBouncer in transaction mode.

-- For JDBC: add to connection URL
-- jdbc:postgresql://host/dbname?prepareThreshold=0

-- For psycopg2: avoid named cursors, use anonymous execution
-- cur.execute("SELECT * FROM users WHERE id = %s", (1,))

-- Nuclear option: reset entire session state on connection return
DISCARD ALL;
Enter fullscreen mode Exit fullscreen mode

3. Missing Cleanup After Transaction Rollback

PREPARE is not transactional — a prepared statement created inside a rolled-back transaction still exists in the session. Retry logic that re-prepares the same name will hit 42P05.

BEGIN;

PREPARE insert_order (int, text) AS
  INSERT INTO orders (customer_id, status) VALUES ($1, $2);

-- Simulate an error and rollback
ROLLBACK;

-- The prepared statement STILL exists after rollback!
SELECT name FROM pg_prepared_statements;
-- Returns: insert_order

-- Retry without cleanup raises 42P05
PREPARE insert_order (int, text) AS   -- ERROR!
  INSERT INTO orders (customer_id, status) VALUES ($1, $2);

-- Correct approach: deallocate before retrying
DEALLOCATE insert_order;

PREPARE insert_order (int, text) AS
  INSERT INTO orders (customer_id, status) VALUES ($1, $2);
Enter fullscreen mode Exit fullscreen mode

Quick Fix Summary 

-- 1. Deallocate a specific statement
DEALLOCATE my_statement;

-- 2. Deallocate all statements in the session
DEALLOCATE ALL;

-- 3. Full session reset (most aggressive)
DISCARD ALL;

-- 4. Inspect what's currently prepared
SELECT name, statement, parameter_types, prepare_time
FROM pg_prepared_statements
ORDER BY prepare_time;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Always wrap PREPARE in an existence check or use DEALLOCATE ALL on connection return.
Register a connection pool hook (e.g., HikariCP's connectionInitSql) to run DEALLOCATE ALL or DISCARD ALL whenever a connection is checked back into the pool. This ensures a clean state for the next borrower.

2. Match your driver settings to your connection pool mode.
If you use PgBouncer in transaction mode, always set prepareThreshold=0 (JDBC) or avoid server-side prepared statements at the driver level. Only use server-side prepared statements when your pool operates in session mode. Document this decision in your infrastructure runbook to prevent configuration drift during deployments.

Related Errors

  • 26000 invalid_sql_statement_name — The opposite problem: executing or deallocating a name that does not exist.
  • 25P02 in_failed_sql_transaction — Often a precursor; errors mid-transaction leave prepared statements dangling, leading to 42P05 on retry.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)