Re: [HACKERS] getting status transaction error

On 2/6/07, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> Merlin Moncure wrote:
> > On 2/6/07, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> > >> actually, here is some more relevant bits from the log.
> > >> Feb  6 06:31:33 mojo postgres[1088]: [1-1] :: LOG:  autovacuum:
> > >> processing database "template0"
> > >> Feb  6 06:31:33 mojo postgres[1088]: [2-1] :: ERROR:  could not access
> > >> status of transaction 544441911
> > >> Feb  6 06:31:33 mojo postgres[1088]: [2-2] :: DETAIL:  could not open
> > >> file "pg_clog/0207": No such file or directory
> > >
> > Latest checkpoint's NextXID:         2162841139
> > 2^31:                                           2147483648
>
> I think the relevant arithmetic here is
>
> echo "2162841139 544441911 - p" | dc
> 1618399228
>
> That's a billion and a half transactions.  Autovacuum uses the formula
>
>         this_whole_db = (tmp->age >
>                          (int32) ((MaxTransactionId >> 3) * 3 - 100000));
>
> to determine whether it needs database-wide vacuum.
> (MaxTransactionId >> 3) is 536870911, so the calculation is
> 536870911 * 3 - 100000
>
> echo "536870911 3 * 100000 - p" | dc
> 1610512733
>
> which looks awfully close to the number above.  About 7 million
> transactions must have passed since the first time the error showed up
> -- does that sound likely?
>
> Well, scratch that -- what's the _current_ Xid? (not lastest
> checkpoint's)

I don't know any better way to get that than this:
postgres=# insert into foo default values;
INSERT 0 1
postgres=# select xmin,xmax from foo;
    xmin    | xmax
------------+------
 2163877346 |    0
(1 row)

merlin