pg_stat_database_conflicts 是一个统计视图，显示了备用服务器上由于各种冲突类型而取消的查询的累积统计信息。
pg_stat_database_conflicts 在 PostgreSQL 9.1 中被添加。
按 PostgreSQL 版本定义
pg_stat_database_conflicts (PostgreSQL 19)
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 | | |
confl_active_logicalslot | bigint | | |
pg_stat_database_conflicts (PostgreSQL 18)
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 | | |
confl_active_logicalslot | bigint | | |
pg_stat_database_conflicts (PostgreSQL 17)
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 | | |
confl_active_logicalslot | bigint | | |
pg_stat_database_conflicts (PostgreSQL 16)
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 | | |
confl_active_logicalslot | bigint | | |
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 | | |
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 | | |
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 | | |
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 | | |
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 | | |
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 | | |
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 |
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 |
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 |
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 |
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 |
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 |
变更历史
pg_stat_database_conflicts 自从在 PostgreSQL 9.1 中添加以来没有被修改过。
- PostgreSQL 9.1
- 添加 (提交 40d9e94b)
示例
在主服务器上，创建以下表
postgres=# CREATE TABLE foo (id INT); CREATE TABLE postgres=# INSERT INTO foo VALUES(generate_series(1,100)); INSERT 0 100
在备用服务器上，开始一个持有表锁的事务，例如：
postgres=# BEGIN ; BEGIN postgres=*# SELECT * FROM foo WHERE id < 2; id ---- 1 (1 row)
在主服务器上，删除该表
postgres=# DROP TABLE foo; DROP TABLE
一段时间后 (最多由 max_standby_streaming_delay 定义的间隔，默认 30s)，在备用服务器上，进一步访问该表的尝试将导致：
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.
这将记录在 confl_lock 列中。
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)
此外，pg_stat_database 中的 conflicts 字段将为 pg_stat_database_conflicts 中记录的每个冲突增加。
参考资料
- PostgreSQL 文档: pg_stat_database_conflicts