This error occurs when attempting to execute write operations in a read-only subtransaction or savepoint, or when the access mode conflicts between a parent transaction and its branch.
The error code 25003 (inappropriate_access_mode_for_branch_transaction) indicates a mismatch between the access mode of a transaction and its subtransaction (also called a "branch" transaction). In PostgreSQL, subtransactions are created using SAVEPOINT commands or implicitly in PL/pgSQL exception blocks. When a parent transaction is set to READ ONLY mode, all subtransactions within it must also operate in read-only mode. Similarly, if you attempt to perform write operations (INSERT, UPDATE, DELETE) within a subtransaction when the parent transaction's access mode doesn't permit it, PostgreSQL raises this error. This error is part of the "Invalid Transaction State" error class (25xxx) and specifically relates to the hierarchical nature of PostgreSQL's nested transaction system. Subtransactions inherit constraints from their parent transactions, and violating these constraints triggers error 25003.
First, verify whether your transaction is in read-only mode:
SHOW transaction_read_only;
-- or
SELECT current_setting('transaction_read_only');If this returns 'on', your transaction is read-only and cannot execute write operations in subtransactions.
Check if your transaction was explicitly set to read-only:
-- Problematic pattern
BEGIN;
SET TRANSACTION READ ONLY;
SAVEPOINT my_savepoint;
INSERT INTO table_name VALUES (...); -- This will fail with error 25003If you need write operations, start the transaction without READ ONLY:
-- Correct pattern
BEGIN;
-- No READ ONLY restriction
SAVEPOINT my_savepoint;
INSERT INTO table_name VALUES (...); -- Works fine
COMMIT;Verify if read-only mode is set globally or for your session:
-- Check session default
SHOW default_transaction_read_only;
-- Check if database is in read-only mode
SHOW default_transaction_read_only;If set to 'on', you can override it for your session:
SET default_transaction_read_only = off;
BEGIN;
-- Now write operations work in subtransactions
SAVEPOINT sp1;
UPDATE my_table SET value = 123;
COMMIT;If using PL/pgSQL exception blocks (which create implicit subtransactions), ensure the parent transaction allows writes:
-- Problematic function
CREATE OR REPLACE FUNCTION update_with_exception()
RETURNS void AS $$
BEGIN
BEGIN
INSERT INTO log_table VALUES ('action'); -- Fails if outer transaction is read-only
EXCEPTION
WHEN OTHERS THEN
RAISE NOTICE 'Error occurred';
END;
END;
$$ LANGUAGE plpgsql;
-- Call in read-only transaction causes error
BEGIN READ ONLY;
SELECT update_with_exception(); -- Error 25003
COMMIT;Solution - call the function in a read-write transaction:
BEGIN; -- Read-write by default
SELECT update_with_exception(); -- Works
COMMIT;If your application performs both read and write operations, structure them into separate transactions:
-- Read-only analysis transaction
BEGIN READ ONLY;
SELECT * FROM large_table WHERE condition;
-- Perform read-only analysis
COMMIT;
-- Separate write transaction
BEGIN; -- Read-write
INSERT INTO results_table SELECT ...;
COMMIT;This approach prevents access mode conflicts and improves transaction clarity.
Subtransaction Performance Considerations: While resolving error 25003, be aware that subtransactions (created by SAVEPOINTs or exception blocks) have performance implications. Each write subtransaction requires a SubTransactionId (subxid), and having more than 64 active subtransactions can cause significant performance degradation due to cache overflow.
Read-Only Subtransaction Optimization: Read-only subtransactions don't receive subxids and have minimal overhead. Once a subtransaction performs a write operation, it transitions from read-only to read-write and gets assigned a subxid. This is why error 25003 appears precisely when the first write operation occurs.
Connection Poolers and default_transaction_read_only: If using connection poolers like PgBouncer, be cautious with session-level SET commands. The setting may persist across connection reuse, causing unexpected read-only behavior in subsequent transactions. Always explicitly set transaction characteristics when needed.
Hot Standby Read Replicas: On PostgreSQL read replicas (hot standby servers), all transactions are implicitly read-only. Attempting to create a subtransaction that writes will fail. This is a common source of error 25003 in applications that mistakenly route write traffic to read replicas.
Prepared Transactions Limitation: Prepared transactions (used in two-phase commit protocols) cannot contain certain DDL operations, and their access mode restrictions are stricter than regular transactions. This can manifest as error 25003 in distributed transaction scenarios.
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL