Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On 2018-05-15 11:06:38 +0200, Maxim Boguk wrote:
> ​Hi everyone,
>
> I just got the same issue on 9.6.8:
>
> 2018-05-15 11:52:01 MSK 33558 @ from [vxid:317/92895305 txid:0] [] ERROR:
> found xmin 2808837517 from before relfrozenxid 248712603
> 2018-05-15 11:52:01 MSK 33558 @ from [vxid:317/92895305 txid:0] []
> CONTEXT: automatic vacuum of table "template0.pg_catalog.pg_authid"
>
> Additional details:
>
> 1) bt_index_check - no errors on both indexes.
>
> 2) SELECT pg_truncate_visibility_map('pg_authid'::regclass); + vacuum
> freeze - doesn't help.

Yea, too late.

> 3)Total 6 rows affected:
> template1=# select oid, ctid, xmin, xmax, cmin, cmax from pg_authid where
> xmin::text::bigint > (select relfrozenxid::text::bigint from pg_class where
> relname='pg_authid');
> oid | ctid | xmin | xmax | cmin | cmax
> -----------+--------+------------+------+------+------
> 183671986 | (0,90) | 3039161773 | 0 | 0 | 0
> 183106699 | (1,48) | 2576823237 | 0 | 0 | 0
> 183921770 | (1,50) | 3265971811 | 0 | 0 | 0
> 183921914 | (1,52) | 3266122344 | 0 | 0 | 0
> 187988173 | (1,58) | 4258893789 | 0 | 0 | 0
> 182424977 | (1,62) | 2808837517 | 0 | 0 | 0
> on total two pages.
>
> 4) template1=# select relfrozenxid from pg_catalog.pg_class where relname =
> 'pg_authid';
> relfrozenxid
> --------------
> 2548304492
>
>
> 5)Rows itself looks pretty valid and correspond to the actual database
> users.
> 7)No database/server crash happened last few years, no disk errors/problems.
>
> I feel it could be related with vacuum skip locked pages patch + freeze +
> shared catalog combination, but cannot prove it yet.
> ​
> Looking for possible course of action.
> Probably simplest fix - drop and recreate these 6 affected users, but so
> far I willing spent some time research into this issue.

Could you use pageinspect to get the infomasks for the affected tuples?

Greetings,

Andres Freund