Re: backend_xmin in pg_stat_replication

[Torsten Förtsch <tfoertsch123@xxxxxxxxx>]

Thanks a lot! So, the correct calculation is like this:

select application_name,

       txid_snapshot_xmin(txid_current_snapshot()),

       backend_xmin::TEXT::BIGINT,

       (txid_snapshot_xmin(txid_current_snapshot())-backend_xmin::TEXT::BIGINT)%(2^32)::BIGINT

  from pg_stat_replication;

 application_name | txid_snapshot_xmin | backend_xmin | ?column?  
------------------+--------------------+--------------+----------
 xxxxxxxxxx       |         6960964080 |   2665996642 |      142

That makes more sense.

On Mon, Oct 1, 2018 at 5:32 PM Andres Freund <andres@xxxxxxxxxxx> wrote:

Hi,

On 2018-10-01 12:20:26 +0200, Torsten Förtsch wrote:
> if I understand it correctly, backend_xmin in pg_stat_replication is the
> xmin that's reported back by hot_standby_feedback. Given there are no
> long-running transactions on the replica, I presume that value should be
> pretty close to the xmin field of any recent snapshots on the master. This
> is true for all my databases but one:
>
> select application_name,
>        txid_snapshot_xmin(txid_current_snapshot()),
>        backend_xmin::TEXT::BIGINT,
>
>  txid_snapshot_xmin(txid_current_snapshot())-backend_xmin::TEXT::BIGINT
>   from pg_stat_replication;
>
>  application_name | txid_snapshot_xmin | backend_xmin |  ?column?
> ------------------+--------------------+--------------+------------
>  xxxxxxxxxx       |         6 957 042 833 |   2 662 075 435 | 4 294 967 398

I don't think the calculation you're doing here is correct.
backend_xmin is an xid (max 2^32-1), whereas txid_snapshot_xmin returns
an xid *with* epoch (max 2^64-1). What you're measuring here is simply
the fact that the xid counter has wrapped around.

Greetings,

Andres Freund