Hi all
I recently had a real world case of a user confused by the
(non)relationship between txid_current()'s output and that of the xid
type, specifically pg_stat_replication.backend_xmin .
I quote:
"
> What should we look for to determine normal? I thought maybe it would
> compare to txid_current(), but these numbers are not at all similar:
>
> XXX=> select txid_current();
> txid_current
> --------------
> 6311252596
> (1 row)
>
> XXX=> select client_addr, backend_xmin from pg_stat_replication;
> client_addr | backend_xmin
> --------------+--------------
> 192.168.X.Y |
> 192.168.X.Y | 2016096136
> 192.168.X.Y |
> 192.168.X.Y | 2016096136
> (4 rows)
This is a confusing user interface issue in PostgreSQL.
backend_xmin is of type 'xid'. txid_current(), though, returns a
bigint where the high bits are an epoch incremented once per xid
wrap-around, and the low bits are the 32-bit xid.
That's why this output is consistent with the user's two servers having
hot_standby_feedback, but the shown backend_xmin is 4295156460 XIDs
behind the master. That's greater than MAXUINT32 (4294967296)
difference, which seems impossible with a 32-bit transaction ID. It's
because your xid counter has wrapped around once, and
pg_stat_replication doesn't show that, but txid_current() does.
Rather than comparing against txid_current(), the simplest way to get
an indication of how far "behind" the master those XIDs are is to use
the age() function, e.g.
select client_addr, backend_xmin, age(backend_xmin) from
pg_stat_replication;
which will report the difference from the master's xid counter, taking
into account wrap-around etc.
Doing the comparison manually is a bit tricky in SQL.
PostgreSQL really should expose a function to strip the epoch and get
a txid (if the epoch is recent) or null (if the epoch is far in the
past) to make this easier. I submitted one as a part of the
txid_status() patch set and I'll get back to that soon. I just thought
this was relevant.
I really wish we could just change the pg_stat_activity and
pg_stat_replication xid fields to be epoch qualified in a 64-bit wide
'fullxid' type, or similar.
-- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services