PostgreSQL 42P05 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 42P05: duplicate prepared statement

PostgreSQL error 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 and persist independently of transaction boundaries, they can accumulate and cause naming conflicts, especially in connection-pooled environments.

Top 3 Causes

1. Re-executing PREPARE Without Deallocating First

The most common cause is running PREPARE with the same name multiple times in a long-lived or reused session without cleaning up first.

-- First call succeeds
PREPARE get_order (int) AS
    SELECT * FROM orders WHERE order_id = $1;

-- Second call in same session throws 42P05
PREPARE get_order (int) AS
    SELECT * FROM orders WHERE order_id = $1;
-- ERROR:  prepared statement "get_order" already exists

Fix: Always deallocate before re-preparing.

-- Safe pattern: deallocate if exists, then prepare
DO $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM pg_prepared_statements WHERE name = 'get_order'
    ) THEN
        DEALLOCATE get_order;
    END IF;
END;
$$;

PREPARE get_order (int) AS
    SELECT * FROM orders WHERE order_id = $1;

EXECUTE get_order(101);

2. Prepared Statements Surviving Transaction Rollbacks

Unlike most session objects, prepared statements are not rolled back when a transaction is rolled back. This surprises many developers who assume a failed transaction cleans everything up.

BEGIN;

PREPARE temp_query (text) AS
    SELECT * FROM users WHERE username = $1;

-- Something goes wrong...
ROLLBACK;

-- The prepared statement still exists!
-- This will throw 42P05:
PREPARE temp_query (text) AS
    SELECT * FROM users WHERE username = $1;
-- ERROR:  prepared statement "temp_query" already exists

-- Verify it survived the rollback
SELECT name, statement FROM pg_prepared_statements;

Fix: Use exception handling to catch and recover gracefully.

DO $$
BEGIN
    DEALLOCATE ALL;

    PREPARE temp_query (text) AS
        SELECT * FROM users WHERE username = $1;

EXCEPTION
    WHEN duplicate_prepared_statement THEN
        DEALLOCATE temp_query;
        PREPARE temp_query (text) AS
            SELECT * FROM users WHERE username = $1;
END;
$$;

3. Connection Poolers Reusing Backend Connections

Tools like PgBouncer in session mode reuse the same backend connection across different client connections. Any prepared statements registered by a previous client remain active and cause conflicts for the next client using the same backend.

-- Client A registers a prepared statement
PREPARE search_products (text) AS
    SELECT * FROM products WHERE category = $1;

-- Client A disconnects, but PgBouncer keeps the backend alive
-- Client B gets the same backend and tries the same PREPARE
PREPARE search_products (text) AS
    SELECT * FROM products WHERE category = $1;
-- ERROR:  prepared statement "search_products" already exists

-- Check all existing prepared statements
SELECT name, prepare_time, statement
FROM pg_prepared_statements
ORDER BY prepare_time;

-- Clean slate for connection reuse
DEALLOCATE ALL;
-- or
DISCARD ALL; -- resets everything: prepared statements, temp tables, etc.

Quick Fix Solutions

-- Option 1: Deallocate a specific statement
DEALLOCATE my_statement_name;

-- Option 2: Deallocate all prepared statements in the session
DEALLOCATE ALL;

-- Option 3: Full session reset (use with caution in production)
DISCARD ALL;

-- Option 4: Check what's currently prepared before acting
SELECT name, statement, prepare_time
FROM pg_prepared_statements;

Prevention Tips

1. Reset connections before returning them to the pool.
Configure your connection pooler to run a reset query when recycling connections. For PgBouncer, set the following in pgbouncer.ini:

-- Equivalent of server_reset_query in PgBouncer
-- Add to pgbouncer.ini: server_reset_query = DISCARD ALL
DISCARD ALL;

2. Monitor prepared statement buildup proactively.
Add regular checks on pg_prepared_statements to catch runaway accumulation before it becomes an incident.

-- Alert if any session has too many prepared statements
SELECT pid, usename, application_name
FROM pg_stat_activity
WHERE pid IN (
    SELECT pid FROM pg_prepared_statements -- session-level view
)
ORDER BY pid;

-- Routine cleanup query for application startup
DO $$
BEGIN
    IF (SELECT COUNT(*) FROM pg_prepared_statements) > 10 THEN
        DEALLOCATE ALL;
        RAISE NOTICE 'Cleared prepared statements on startup.';
    END IF;
END;
$$;

3. Use anonymous prepared statements or direct queries when connection pooling is involved and you cannot control the pool reset behavior, avoiding named prepared statements altogether in those contexts.

📖 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.

DEV Community