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.
Definition by PostgreSQL version
pg_stat_database_conflicts (PostgreSQL 15)
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 14)
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 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.
- PostgreSQL 9.1
- added (commit 40d9e94b)
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