From: | Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com> |
---|---|
To: | Peter Geoghegan <pg(at)bowt(dot)ie> |
Cc: | Jeremy Finzel <finzelj(at)gmail(dot)com>, Andres Freund <andres(at)anarazel(dot)de>, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>, Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: found xmin from before relfrozenxid on pg_catalog.pg_authid |
Date: | 2018-05-15 09:06:38 |
Message-ID: | CAK-MWwQUvbYNwO=u9Q9j=1uVQoKy5TuF2doHJDR1uR9i4puf7g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
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.
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.
--
Maxim Boguk
Senior Postgresql DBA
http://dataegret.com/ <http://www.postgresql-consulting.com/>
Phone RU: +7 985 433 0000
Phone UA: +380 99 143 0000
Phone AU: +61 45 218 5678
LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b
Skype: maxim.boguk
"Доктор, вы мне советовали так не делать, но почему мне по-прежнему больно
когда я так делаю ещё раз?"
From | Date | Subject | |
---|---|---|---|
Next Message | tango ward | 2018-05-15 09:10:41 | Control PhoneNumber Via SQL |
Previous Message | David G. Johnston | 2018-05-15 05:23:01 | Re: Query ID Values |
From | Date | Subject | |
---|---|---|---|
Next Message | Dmitry Dolgov | 2018-05-15 10:23:05 | Re: libpq compression |
Previous Message | Ideriha, Takeshi | 2018-05-15 08:46:12 | log_min_messages shows debug instead of debug2 |