From: | Torsten Förtsch <tfoertsch123(at)gmail(dot)com> |
---|---|
To: | Andres Freund <andres(at)anarazel(dot)de> |
Cc: | PostgreSQL mailing lists <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: backend_xmin in pg_stat_replication |
Date: | 2018-10-01 16:39:35 |
Message-ID: | CAKkG4_kf4SE=OmneBO+GH_WukXq6ZPoWcxUtuprqZNu8+hCAkQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)anarazel(dot)de> 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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Joe Conway | 2018-10-01 17:17:27 | Re: PostgreSQL FIPS 140-2 on Window |
Previous Message | Tom Lane | 2018-10-01 16:08:16 | Re: PostgreSQL FIPS 140-2 on Window |