Обсуждение: Wraparound

On Tue, 2024-02-20 at 10:02 +0530, Rajesh Kumar wrote:
> Is autovacuum wraparound issue is when transaction id reaches
> autovacuum_freeze_max_age  or if transaction Reaches 2^32.
>
> My autovacuum_freeze_age setting is 200million.
>
> Db size is 150gb. 

2^31 is the magical number at which very old transactions become
transactions from the future, which would cause data loss unless
all old rows have been frozen.

Yours,
Laurenz Albe

On Tue, 2024-02-20 at 12:57 +0530, Rajesh Kumar wrote:
> On Tue, 20 Feb 2024, 12:53 Laurenz Albe, <laurenz.albe@cybertec.at> wrote:
> > On Tue, 2024-02-20 at 10:02 +0530, Rajesh Kumar wrote:
> > > Is autovacuum wraparound issue is when transaction id reaches
> > > autovacuum_freeze_max_age  or if transaction Reaches 2^32.
> > >
> > > My autovacuum_freeze_age setting is 200million.
> > >
> > > Db size is 150gb. 
> >
> > 2^31 is the magical number at which very old transactions become
> > transactions from the future, which would cause data loss unless
> > all old rows have been frozen.
>
> Sorry, I could not understand. If possible, please elaborate. Otherwise, no issues

The (32-bit) transaction numbers are stored in "xmin" and "xmax" in each row.
You have to imagine these numbers as a closed ring: if you get past 2^32-1,
you wrap around to 0 and continue.

Now the 2^31 numbers before the current 32-bit transaction id are the past,
and the 2^31 numbers after it are the future.  This includes wraparound, so
initially numbers close to 2^32 will also be past.

As the current transaction id crosses 2^31, very old transactions suddenly
become future transactions.  All rows with a low "xmin" will suddenly appear
to have been created in the future and become invisible.  Some rows with a
low "xmax" that have been deleted long ago can suddenly rise from the dead,
since the transaction that deleted them now appears to be in the future.

Perhaps you will find this article entertaining:
https://www.cybertec-postgresql.com/en/transaction-id-wraparound-a-walk-on-the-wild-side/

Yours,
Laurenz Albe

On Tue, 2024-02-20 at 12:57 +0530, Rajesh Kumar wrote:
> On Tue, 20 Feb 2024, 12:53 Laurenz Albe, <laurenz.albe@cybertec.at> wrote:
> > On Tue, 2024-02-20 at 10:02 +0530, Rajesh Kumar wrote:
> > > Is autovacuum wraparound issue is when transaction id reaches
> > > autovacuum_freeze_max_age  or if transaction Reaches 2^32.
> > >
> > > My autovacuum_freeze_age setting is 200million.
> > >
> > > Db size is 150gb. 
> >
> > 2^31 is the magical number at which very old transactions become
> > transactions from the future, which would cause data loss unless
> > all old rows have been frozen.
>
> Sorry, I could not understand. If possible, please elaborate. Otherwise, no issues

The (32-bit) transaction numbers are stored in "xmin" and "xmax" in each row.
You have to imagine these numbers as a closed ring: if you get past 2^32-1,
you wrap around to 0 and continue.

Now the 2^31 numbers before the current 32-bit transaction id are the past,
and the 2^31 numbers after it are the future.  This includes wraparound, so
initially numbers close to 2^32 will also be past.

As the current transaction id crosses 2^31, very old transactions suddenly
become future transactions.  All rows with a low "xmin" will suddenly appear
to have been created in the future and become invisible.  Some rows with a
low "xmax" that have been deleted long ago can suddenly rise from the dead,
since the transaction that deleted them now appears to be in the future.

Perhaps you will find this article entertaining:
https://www.cybertec-postgresql.com/en/transaction-id-wraparound-a-walk-on-the-wild-side/

Yours,
Laurenz Albe

this might help explain what laurenz tried to put in words.

although this is actual wraparound trigger when db will be blocked for activity but helps understand the setup, hopefully.

https://tuple-freezing-demo.angusd.com/

On Tue, Feb 20, 2024, 1:12 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

On Tue, 2024-02-20 at 12:57 +0530, Rajesh Kumar wrote:
> On Tue, 20 Feb 2024, 12:53 Laurenz Albe, <laurenz.albe@cybertec.at> wrote:
> > On Tue, 2024-02-20 at 10:02 +0530, Rajesh Kumar wrote:
> > > Is autovacuum wraparound issue is when transaction id reaches
> > > autovacuum_freeze_max_age  or if transaction Reaches 2^32.
> > >
> > > My autovacuum_freeze_age setting is 200million.
> > >
> > > Db size is 150gb. 
> >
> > 2^31 is the magical number at which very old transactions become
> > transactions from the future, which would cause data loss unless
> > all old rows have been frozen.
>
> Sorry, I could not understand. If possible, please elaborate. Otherwise, no issues

The (32-bit) transaction numbers are stored in "xmin" and "xmax" in each row.
You have to imagine these numbers as a closed ring: if you get past 2^32-1,
you wrap around to 0 and continue.

Now the 2^31 numbers before the current 32-bit transaction id are the past,
and the 2^31 numbers after it are the future.  This includes wraparound, so
initially numbers close to 2^32 will also be past.

As the current transaction id crosses 2^31, very old transactions suddenly
become future transactions.  All rows with a low "xmin" will suddenly appear
to have been created in the future and become invisible.  Some rows with a
low "xmax" that have been deleted long ago can suddenly rise from the dead,
since the transaction that deleted them now appears to be in the future.

Perhaps you will find this article entertaining:
https://www.cybertec-postgresql.com/en/transaction-id-wraparound-a-walk-on-the-wild-side/

Yours,
Laurenz Albe