bigint vs txid user confusion

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