Re: Wraparound

From: Vijaykumar Jain <vijaykumarjain(dot)github(at)gmail(dot)com>
To: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
Cc: Rajesh Kumar <rajeshkumar(dot)dba09(at)gmail(dot)com>, Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: Re: Wraparound
Date: 2024-02-21 08:25:24
Message-ID: CAM+6J96bxVtzZWZ1V8NyJMt5W1WJEaDcP0bgZ5C8cZLNBcWgUg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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(dot)albe(at)cybertec(dot)at> wrote:

> On Tue, 2024-02-20 at 12:57 +0530, Rajesh Kumar wrote:
> > On Tue, 20 Feb 2024, 12:53 Laurenz Albe, <laurenz(dot)albe(at)cybertec(dot)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
>
>
>

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Fabrice Chapuis 2024-02-21 09:17:40 Fwd: pg_restore option --clean
Previous Message Rajesh Kumar 2024-02-21 03:37:44 Re: Wraparound