时间戳

首页 > 解决方案 > What does backend_xmin and backend_xid represent in pg_stat_activity?

postgresql - What does backend_xmin and backend_xid represent in pg_stat_activity?

问题描述

Postgresql exposes the view pg_stat_activity. As per the documentation,

backend_xid means Top-level transaction identifier of this backend, if any.

backend_xmin means The current backend's xmin horizon.

Let us take an example:

BEGIN;
# INSERT Statement1
# INSERT Statement2
COMMIT;

backend_xid represents the transaction identifier allotted to the full transaction while if pg_stat_activity shows the statement2 in its row, then backend_xmin corresponds to the xmin for statement2.

Is the understanding correct?

标签： postgresql

解决方案

These two values represent the snapshot of the transaction.

Whenever a query starts (or, with REPEATABLE READ isolation level, when the first query in the transaction starts), PostgreSQL takes a snapshot of the database. That means that the query (or the transaction) sees a certain state of the database, no matter what concurrent data modifications happened in the meantime.

Such a snapshot consists of the oldest transaction ID of any active transaction at the time (xmin), the transaction ID of the newest active transaction (xmax) and a list of the transaction IDs of all currently active transactions. With the aid of these numbers, PostgreSQL can determine if a tuple (a row version) is visible or not.

backend_xmin is relevant because (together with other values) the oldest one determines the xmin horizon, which is the cut-off point for dead tuples that autovacuum can safely remove.

推荐阅读