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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers