PostgreSQL 25006 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 25006: READ_ONLY_SQL_TRANSACTION Explained

PostgreSQL error code 25006 (READ_ONLY_SQL_TRANSACTION) is raised when you attempt to execute a data-modifying operation — such as INSERT, UPDATE, DELETE, or DDL — inside a read-only transaction. This typically happens when a transaction is explicitly set to read-only mode, when connected to a Hot Standby replica, or when the session/database has default_transaction_read_only enabled. Understanding the root cause is essential for a quick fix.

Top 3 Causes and Fixes

1. Explicitly Declared READ ONLY Transaction

A developer or ORM framework may have started a transaction with SET TRANSACTION READ ONLY, preventing any writes within that transaction block.

-- Reproducing the error
BEGIN;
SET TRANSACTION READ ONLY;
INSERT INTO orders (product, qty) VALUES ('Widget', 10);
-- ERROR: cannot execute INSERT in a read-only transaction

ROLLBACK;

-- Fix: Use READ WRITE mode explicitly
BEGIN READ WRITE;
INSERT INTO orders (product, qty) VALUES ('Widget', 10);
COMMIT;

-- Or reset at session level
SET default_transaction_read_only = off;

2. Writing to a Hot Standby (Replica) Server

In streaming replication setups, standby servers are read-only by design. If your application mistakenly routes write queries to a replica — due to misconfigured load balancers or connection pools — you will hit this error immediately.

-- Check if the current server is a standby
SELECT pg_is_in_recovery();
-- Returns TRUE  → Standby (read-only, writes not allowed)
-- Returns FALSE → Primary (writes allowed)

-- View replication receiver status on standby
SELECT * FROM pg_stat_wal_receiver;

-- On the primary, check connected replicas
SELECT client_addr, state, sync_state
FROM pg_stat_replication;

Fix: Always route write connections to the Primary server. Use tools like PgBouncer or HAProxy with explicit read/write split rules.

3. default_transaction_read_only Set to ON

The default_transaction_read_only parameter may be enabled at the server, database, or user level — silently making every new transaction read-only without explicit declaration.

-- Check current setting
SHOW default_transaction_read_only;

-- Fix for current session only
SET default_transaction_read_only = off;

-- Fix for a specific user (requires superuser)
ALTER USER app_user SET default_transaction_read_only = off;

-- Fix for a specific database
ALTER DATABASE mydb SET default_transaction_read_only = off;

-- Verify the change
SELECT name, setting, source
FROM pg_settings
WHERE name = 'default_transaction_read_only';

Quick Fix Summary

Cause	Fix
Explicit `READ ONLY` in transaction	Use `BEGIN READ WRITE` or `SET TRANSACTION READ WRITE`
Connected to Standby server	Check `pg_is_in_recovery()` and redirect to Primary
Session/DB parameter enabled	`SET default_transaction_read_only = off`

Prevention Tips

1. Always verify server role before writes.
Add a pre-flight check in your application startup or deployment pipeline using pg_is_in_recovery(). This ensures your write data source is always pointed at the Primary node, preventing accidental writes to replicas.

-- Simple health check query before accepting write traffic
SELECT CASE
    WHEN pg_is_in_recovery() THEN 'STANDBY - writes not allowed'
    ELSE 'PRIMARY - writes allowed'
END AS server_role;

2. Audit transaction settings regularly.
Periodically audit user and database-level configuration to catch any unintentional read_only settings before they cause production issues.

-- Audit all roles with custom transaction settings
SELECT rolname, rolconfig
FROM pg_roles
WHERE rolconfig IS NOT NULL;

-- Audit all databases with custom settings
SELECT datname, datconfig
FROM pg_database
WHERE datconfig IS NOT NULL;

Related Errors

25001 (ACTIVE_SQL_TRANSACTION): Command not allowed within an active transaction block.
25P02 (IN_FAILED_SQL_TRANSACTION): Command issued after a transaction has already failed and is awaiting rollback.
55000 (OBJECT_NOT_IN_PREREQUISITE_STATE): Object state does not meet the prerequisites for the requested operation, often seen alongside replication-related issues.

📖 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