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
- PostgreSQL 14
- marked as
GUC_REPORT(commit d16f8c8e)
- marked as
- PostgreSQL 7.4
- added (commit b65cd562)
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
References
- PostgreSQL documentation: default_transaction_read_only