pg_stat_database_conflicts
pg_stat_database_conflicts is a statistics view showing cumulative statistics for queries cancelled due to various types of conflict on a standby server.
pg_stat_database_conflicts was added in PostgreSQL 9.1 (commit 40d9e94b).
Definition by PostgreSQL version
pg_stat_database_conflicts (PostgreSQL 13)
View "pg_catalog.pg_stat_database_conflicts"
Column | Type | Collation | Nullable | Default
------------------+--------+-----------+----------+---------
datid | oid | | |
datname | name | | |
confl_tablespace | bigint | | |
confl_lock | bigint | | |
confl_snapshot | bigint | | |
confl_bufferpin | bigint | | |
confl_deadlock | bigint | | |
Documentation: pg_stat_database_conflicts
pg_stat_database_conflicts (PostgreSQL 12)
View "pg_catalog.pg_stat_database_conflicts"
Column | Type | Collation | Nullable | Default
------------------+--------+-----------+----------+---------
datid | oid | | |
datname | name | | |
confl_tablespace | bigint | | |
confl_lock | bigint | | |
confl_snapshot | bigint | | |
confl_bufferpin | bigint | | |
confl_deadlock | bigint | | |
Documentation: pg_stat_database_conflicts
pg_stat_database_conflicts (PostgreSQL 11)
View "pg_catalog.pg_stat_database_conflicts"
Column | Type | Collation | Nullable | Default
------------------+--------+-----------+----------+---------
datid | oid | | |
datname | name | | |
confl_tablespace | bigint | | |
confl_lock | bigint | | |
confl_snapshot | bigint | | |
confl_bufferpin | bigint | | |
confl_deadlock | bigint | | |
Documentation: pg_stat_database_conflicts
pg_stat_database_conflicts (PostgreSQL 10)
View "pg_catalog.pg_stat_database_conflicts"
Column | Type | Collation | Nullable | Default
------------------+--------+-----------+----------+---------
datid | oid | | |
datname | name | | |
confl_tablespace | bigint | | |
confl_lock | bigint | | |
confl_snapshot | bigint | | |
confl_bufferpin | bigint | | |
confl_deadlock | bigint | | |
Documentation: pg_stat_database_conflicts
pg_stat_database_conflicts (PostgreSQL 9.6)
View "pg_catalog.pg_stat_database_conflicts"
Column | Type | Modifiers
------------------+--------+-----------
datid | oid |
datname | name |
confl_tablespace | bigint |
confl_lock | bigint |
confl_snapshot | bigint |
confl_bufferpin | bigint |
confl_deadlock | bigint |
Documentation: pg_stat_database_conflicts
pg_stat_database_conflicts (PostgreSQL 9.5)
View "pg_catalog.pg_stat_database_conflicts"
Column | Type | Modifiers
------------------+--------+-----------
datid | oid |
datname | name |
confl_tablespace | bigint |
confl_lock | bigint |
confl_snapshot | bigint |
confl_bufferpin | bigint |
confl_deadlock | bigint |
Documentation: pg_stat_database_conflicts
pg_stat_database_conflicts (PostgreSQL 9.4)
View "pg_catalog.pg_stat_database_conflicts"
Column | Type | Modifiers
------------------+--------+-----------
datid | oid |
datname | name |
confl_tablespace | bigint |
confl_lock | bigint |
confl_snapshot | bigint |
confl_bufferpin | bigint |
confl_deadlock | bigint |
Documentation: pg_stat_database_conflicts
pg_stat_database_conflicts (PostgreSQL 9.3)
View "pg_catalog.pg_stat_database_conflicts"
Column | Type | Modifiers
------------------+--------+-----------
datid | oid |
datname | name |
confl_tablespace | bigint |
confl_lock | bigint |
confl_snapshot | bigint |
confl_bufferpin | bigint |
confl_deadlock | bigint |
Documentation: pg_stat_database_conflicts
pg_stat_database_conflicts (PostgreSQL 9.2)
View "pg_catalog.pg_stat_database_conflicts"
Column | Type | Modifiers
------------------+--------+-----------
datid | oid |
datname | name |
confl_tablespace | bigint |
confl_lock | bigint |
confl_snapshot | bigint |
confl_bufferpin | bigint |
confl_deadlock | bigint |
Documentation: pg_stat_database_conflicts
pg_stat_database_conflicts (PostgreSQL 9.1)
View "pg_catalog.pg_stat_database_conflicts"
Column | Type | Modifiers
------------------+--------+-----------
datid | oid |
datname | name |
confl_tablespace | bigint |
confl_lock | bigint |
confl_snapshot | bigint |
confl_bufferpin | bigint |
confl_deadlock | bigint |
Documentation: pg_stat_database_conflicts
Change history
pg_stat_database_conflicts has not been modified since it was added in PostgreSQL 9.1.
Examples
On a primary server, create the following table:
postgres=# CREATE TABLE foo (id int) ; CREATE TABLE postgres=# INSERT INTO foo values(generate_series(1,100)); INSERT 0 100
On a standby, begin a transaction which holds a lock on the table, e.g.:
postgres=# BEGIN ; BEGIN postgres=*# SELECT * FROM foo WHERE id < 2; id ---- 1 (1 row)
On the primary server, drop the table:
postgres=# DROP TABLE foo; DROP TABLE
After a while (at most the interval defined by max_standby_streaming_delay, default 30s) on the standby a further attempt to access the table will result in:
postgres=*# SELECT * FROM foo WHERE id < 2;
FATAL: terminating connection due to conflict with recovery
DETAIL: User was holding a relation lock for too long.
HINT: In a moment you should be able to reconnect to the database and repeat your command.
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
This will be recorded in the confl_lock column:
postgres=# SELECT * FROM pg_stat_database_conflicts WHERE datname=CURRENT_DATABASE(); datid | datname | confl_tablespace | confl_lock | confl_snapshot | confl_bufferpin | confl_deadlock -------+----------+------------------+------------+----------------+-----------------+---------------- 13579 | postgres | 0 | 1 | 0 | 0 | 0 (1 row)
Additionally the conflicts field in pg_stat_database will be incremented for each conflict recorded in pg_stat_database_conflicts.
References
- PostgreSQL documentation: pg_stat_database_conflicts