内容

pg_stat_database_conflicts

一个统计视图，显示由于各种类型的冲突而导致取消的查询的累积统计信息

pg_stat_database_conflicts 是一个 统计视图，显示备用服务器上由于各种类型的冲突而导致取消的查询的累积统计信息。

pg_stat_database_conflicts 在 PostgreSQL 9.1 中添加。

按 PostgreSQL 版本定义

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

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

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

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

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

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

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

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

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

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

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

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

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

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

变更历史

pg_stat_database_conflicts 自在 PostgreSQL 9.1 中添加以来，没有进行修改。

示例

在主服务器上，创建以下表

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 定义的间隔，默认为 30 秒）在备用服务器上，进一步尝试访问该表将导致

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 中记录的每个冲突递增。

分类

监控，统计/监控视图

另请参阅

pg_stat_database

反馈

提交任何关于 "pg_stat_database_conflicts" 的评论、建议或更正 此处。