Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On Mon, Mar 19, 2018 at 2:41 PM, Andres Freund <andres(at)anarazel(dot)de> wrote:

> On 2018-03-19 14:37:24 -0500, Jeremy Finzel wrote:
> > We upgraded to 9.5.5, and today we are running 9.5.11. And actually we
> > upgraded from 9.3, not 9.4. We are still trying to figure out which
> point
> > release we were on at 9.3.
>
> Ok. IIRC there used to be a bug a few years back that sometimes lead to
> highly contended pages being skipped during vacuum, and we'd still
> update relfrozenxid. IIRC it required the table to be extended at the
> same time or something?
>
>
> >
> > > - Can you install the pageinspect extension? If so, it might be a
> > > CREATE EXTENSION pageinspect;
> > > CREATE OR REPLACE FUNCTION check_rel(rel regclass, OUT blockno int8,
> OUT
> > > lp int2, OUT xmin xid)
> > > RETURNS SETOF RECORD
> > > LANGUAGE SQL
> > > AS $$
> > > SELECT blockno, lp, t_xmin
> > > FROM
> > > generate_series(0, pg_relation_size($1::text) / 8192 - 1)
> blockno,
> > > -- every block in the relation
> > > heap_page_items(get_raw_page($1::text, blockno::int4)) --
> every
> > > item on the page
> > > WHERE
> > > t_xmin IS NOT NULL -- filter out empty items
> > > AND t_xmin != 1 -- filter out bootstrap
> > > AND t_xmin != 2 -- filter out frozen transaction id
> > > AND (t_infomask & ((x'0100' | x'0200')::int)) != ((x'0100' |
> > > x'0200')::int) -- filter out frozen rows with xid present
> > > AND age(t_xmin) > age((SELECT relfrozenxid FROM pg_class WHERE
> oid
> > > = $1)) -- xid cutoff filter
> > > $$;
> > > SELECT * FROM check_rel('pg_authid') LIMIT 100;
> > >
> >
> > Small note - Needs to be this because != is not supported for xid:
> >
> > AND NOT t_xmin = 1 -- filter out bootstrap
> > AND NOT t_xmin = 2 -- filter out frozen transaction id
>
> Only on older releases ;). But yea, that looks right.
>
>
>
> > > and then display all items for one of the affected pages like
> > > SELECT * FROM heap_page_items(get_raw_page('pg_authid', 34343));
> > >
> > >
> > > Alvaro:
> > > - Hm, we talked about code adding context for these kind of errors,
> > > right? Is that just skipped for csvlog?
> > > - Alvaro, does the above check_rel() function make sense?
> > >
> > > Greetings,
> > >
> > > Andres Freund
> > >
> >
> > The function does NOT show any issue with either of those tables.
>
> Uh, huh? Alvaro, do you see a bug in my query?
>
> Greetings,
>
> Andres Freund
>

FWIW, if I remove the last filter, I get these rows and I believe row 7/57/
2906288382 is the one generating error:

SELECT * FROM check_rel('pg_authid') LIMIT 100;
blockno | lp | xmin
---------+----+------------
7 | 4 | 2040863716
7 | 5 | 2040863716
7 | 8 | 2041172882
7 | 9 | 2041172882
7 | 12 | 2041201779
7 | 13 | 2041201779
7 | 16 | 2089742733
7 | 17 | 2090021318
7 | 18 | 2090021318
7 | 47 | 2090021898
7 | 48 | 2090021898
7 | 49 | 2102749003
7 | 50 | 2103210571
7 | 51 | 2103210571
7 | 54 | 2154640913
7 | 55 | 2163849781
7 | 56 | 2295315714
7 | 57 | 2906288382
7 | 58 | 2906329443
7 | 60 | 3131766386
8 | 1 | 2089844462
8 | 2 | 2089844462
8 | 3 | 2089844463
8 | 6 | 2089844463
8 | 9 | 2295318868
(25 rows)