default_transaction_read_only

A parameter determining whether transactions are read-only by default

default_transaction_read_only is a configuration parameter determining whether transactions are by default read-only.

default_transaction_read_only was added in PostgreSQL 7.4.

Default value

The default value for is default_transaction_read_only is: off.

Usage

default_transaction_read_only defaults to off on a primary. On a standby it is always on.

libpq

default_transaction_read_only is one of the parameters reported to the libpq connection and can be queried with:

PQparameterStatus(conn, "default_transaction_read_only")

Change history

Examples

Basic usage example for default_transaction_read_only:

postgres=# SHOW transaction_read_only;
 transaction_read_only 
-----------------------
 off
(1 row)

postgres=# BEGIN;
BEGIN

postgres=*# SET TRANSACTION READ ONLY;
SET

postgres=*# SHOW transaction_read_only;
 transaction_read_only 
-----------------------
 on
(1 row)

This is equivalent (on a primary) to:

postgres=# BEGIN ;
BEGIN

postgres=*# SET transaction_read_only = on;
SET

postgres=*# SHOW transaction_read_only;
 transaction_read_only 
-----------------------
 on
(1 row)

postgres=*# INSERT INTO foo values(1);
ERROR:  cannot execute INSERT in a read-only transaction

On a standby, default_transaction_read_only is always on:

postgres=# SELECT pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 t
(1 row)

postgres=# SHOW transaction_read_only;
 transaction_read_only 
-----------------------
 on
(1 row)

It is not possible to set default_transaction_read_only on a standby:

postgres=# SET transaction_read_only = off;
ERROR:  cannot set transaction read-write mode during recovery

Categories

GUC configuration item, Transactions

See also

SET TRANSACTION