BUG #15199: relfrozenxid related corruption in pg_authid - Mailing list pgsql-bugs

From PG Bug reporting form
Subject BUG #15199: relfrozenxid related corruption in pg_authid
Date
Msg-id 152646402633.27209.13793314872253669741@wrigleys.postgresql.org
Whole thread Raw
List pgsql-bugs
The following bug has been logged on the website:

Bug reference:      15199
Logged by:          Maxim Boguk
Email address:      mb@dataegret.com
PostgreSQL version: 9.6.8
Operating system:   Linux Ubuntu
Description:

Hi,

An initial problem description located in:

https://www.postgresql.org/message-id/flat/CAK-MWwQUvbYNwO%3Du9Q9j%3D1uVQoKy5TuF2doHJDR1uR9i4puf7g%40mail.gmail.com#CAK-MWwQUvbYNwO=u9Q9j=1uVQoKy5TuF2doHJDR1uR9i4puf7g@mail.gmail.com

There are quote from that post:
==============================================================
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.

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.
================================================

Since that I dropped all 6 problem users because without it autovacuum stuck
on whole cluster.
Now vacuum on table work without issues.

But:
Any attempt to add any new user lead to the following error:

template1=# CREATE ROLE "testrole";
CREATE ROLE
template1=# vacuum freeze pg_authid;
ERROR:  found xmin 2870599902 from before relfrozenxid 248712603
template1=# drop role "testrole";
DROP ROLE
template1=# vacuum freeze pg_authid;
VACUUM

In the same time:
template1=# select relfrozenxid::text::bigint from pg_class where
relname='pg_authid';
 relfrozenxid
--------------
   2870865063

I have no idea where value of " from before relfrozenxid 248712603" arise
from.

Vacuum full pg_authid - doesn't help as well.

Regards,
Maxim


pgsql-bugs by date:

Previous
From: PG Bug reporting form
Date:
Subject: BUG #15198: nextval() accepts tables/indexes when adding a default toa column
Next
From: PG Bug reporting form
Date:
Subject: BUG #15200: Support ANSI OFFSET .. FETCH syntax with bind variables