pg_stat_database_conflicts

A statistics view showing cumulative statistics for queries cancelled due to various types of conflict

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.

Categories

Monitoring, Statistics / monitoring view

See also

pg_stat_database