ERROR: cannot execute INSERT in a read-only transaction (SQLSTATE 25006) came, in the
tests here, from a transaction marked read only by SET TRANSACTION READ ONLY or
by default_transaction_read_only = on. Run SELECT pg_is_in_recovery()
first: if it says t you are on a standby, a case not tested here; if
f, find where the setting is on and switch it off there.
Run SHOW default_transaction_read_only; and
SELECT pg_is_in_recovery();. In the test the first said on and the
second f, and the setting turned out to be the cause. A
database-level setting needed SET default_transaction_read_only = off in the
session before ALTER DATABASE ... RESET was accepted.
The error
BEGIN;
BEGIN
SET TRANSACTION READ ONLY;
SET
INSERT INTO booking VALUES (1, 'Aiko Tanaka');
psql:/tmp/f25006.sql:5: ERROR: 25006: cannot execute INSERT in a read-only transaction
LOCATION: PreventCommandIfReadOnly, utility.c:407The same error with the setting at database level, in a new session:
SHOW default_transaction_read_only;
default_transaction_read_only
-------------------------------
on
(1 row)
INSERT INTO booking VALUES (5, 'David Chen');
psql:/tmp/f25006b.sql:3: ERROR: 25006: cannot execute INSERT in a read-only transaction
LOCATION: PreventCommandIfReadOnly, utility.c:407
ALTER DATABASE lab RESET default_transaction_read_only;
psql:/tmp/f25006b.sql:4: ERROR: 25006: cannot execute ALTER DATABASE in a read-only transaction
LOCATION: PreventCommandIfReadOnly, utility.c:407Why it happens
A read-only transaction refuses writes, and the message names the statement it refused:
INSERT, and in the second case ALTER DATABASE too. The transaction
got that mark in two ways in the test. SET TRANSACTION READ ONLY marked one
transaction. ALTER DATABASE lab SET default_transaction_read_only = on made the next
session on lab start read only: SHOW said on in that
fresh connection.
The trap in the second case is that the repair statement is itself a write. The first
ALTER DATABASE ... RESET failed with the same code, because the session that
ran it was read only as well.
The fix
SET default_transaction_read_only = off;
SET
ALTER DATABASE lab RESET default_transaction_read_only;
ALTER DATABASEA new session then showed the setting off and the insert went through:
SHOW default_transaction_read_only;
default_transaction_read_only
-------------------------------
off
(1 row)
INSERT INTO booking VALUES (6, 'Maria Garcia');
INSERT 0 1
For the per-transaction case, end the read-only transaction with ROLLBACK and
start a new one without READ ONLY. In the test, BEGIN READ WRITE;
also won over SET LOCAL default_transaction_read_only = on inside it, and the
insert in that transaction committed. A server where pg_is_in_recovery()
returns t is a case this test did not cover.
How it was reproduced
A postgres:18 container (PostgreSQL 18.4, Debian 18.4-1.pgdg13+1) on Docker
Engine 29.9.2, database lab, table booking (id int, guest text) with
fictional names. Scripts f25006.sql, f25006b.sql and
f25006c.sql ran with
docker exec fix8-pg3 sh -c "psql -U postgres -d lab -e -f /tmp/f25006.sql 2>&1"
(and the same for the other two files), after
ALTER DATABASE lab SET default_transaction_read_only = on; for the second one.
\set VERBOSITY verbose at the top of the first two adds the SQLSTATE. The
container name is the test's own.
Frequently asked
- How do I turn off read-only mode in PostgreSQL?
- In the test, SET default_transaction_read_only = off in the session, then ALTER DATABASE lab RESET default_transaction_read_only. A new session then showed off and accepted the insert.
- Why does ALTER DATABASE fail with cannot execute in a read-only transaction?
- The session running it was read only too. ALTER DATABASE lab RESET failed with 25006 until SET default_transaction_read_only = off ran first in the same session.
- How do I see why a PostgreSQL session is read only?
- Run SHOW default_transaction_read_only and SHOW transaction_read_only. Both said on in the test after SET default_transaction_read_only = on, and SELECT pg_is_in_recovery() said f.
More decoded errors in the Fixes category.