Re: [HACKERS] getting status transaction error

On 2/6/07, Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
> Merlin Moncure wrote:
> > On 2/6/07, Alvaro Herrera <alvherre(at)commandprompt(dot)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