Re: Error on vacuum: xmin before relfrozenxid

Hello,

2018-05-22 18:49 GMT+02:00 Andres Freund <andres(at)anarazel(dot)de>:

> Hi,
>
> On 2018-05-22 16:18:20 +0200, Paolo Crosato wrote:
> > PostgreSQL version number you are running:
> >
> > PostgreSQL 10.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
> > 20150623 (Red Hat 4.8.5-28), 64-bit
> >
> > How you installed PostgreSQL:
> >
> > From the pgdg yum repositories.
> >
> > Changes made to the settings in the postgresql.conf file: see Server
> > Configuration for a quick way to list them all.
> > May 20 16:06:04 ubipgsql01 postgres[26739]: [1380-1] user=,db=,client=
> > ERROR: found xmin 2889675859 from before relfrozenxid 400011439
> > May 20 16:06:04 ubipgsql01 postgres[26739]: [1380-2] user=,db=,client=
> > CONTEXT: automatic vacuum of table "postgres.pg_catalog.pg_authid"
> >
> > postgres=# select xmin from pg_authid ;
> > xmin
> > ------------
> > 1
> > 1
> > 1
> > 1
> > 1
> > 557
> > 7216348
> > 110077819
> > 110511334
> > 3031994631
> > 3032044199
> > 3032044199
> > 3032044199
> > 3032070282
> > (14 rows)
> >
> > postgres=# select relfrozenxid from pg_class where relname='pg_authid';
> > relfrozenxid
> > --------------
> > 400011439
> > (1 row)
> >
> > postgres=#
> >
> > Is this a sympthom of data corruption or transaction wraparound due to
> the
> > long running transaction that we killed weeks ago?
>
> That shouldn't be the sole cause.
>
>
> > This is the only table in the whole cluster that has this error. We
> > are monitoring transactions wraparound with the the check_postgres.pl
> > script, the check is still running fine and no alert was given at all
> > since the cluster has been running.
> >
> > Thank you in advance for any answer.
>
> Could you report the result of
> select ctid, xmin, xmax from pg_authid ;
>

This is the result:

postgres=# select ctid, xmin, xmax from pg_authid ;
ctid | xmin | xmax
--------+------------+------
(0,1) | 1 | 0
(0,2) | 1 | 0
(0,3) | 1 | 0
(0,4) | 1 | 0
(0,5) | 1 | 0
(0,6) | 557 | 0
(0,11) | 7216348 | 0
(0,12) | 110077819 | 0
(0,13) | 110511334 | 0
(0,16) | 3031994631 | 0
(0,17) | 3032044199 | 0
(0,18) | 3032044199 | 0
(0,19) | 3032044199 | 0
(0,20) | 3032070282 | 0
(14 rows)

postgres=#

> and
> CREATE EXTENSION pageinspect;
> SELECT * FROM heap_page_items(get_raw_page('pg_authid', 0));
>

This is the output of the second query:

postgres=# CREATE EXTENSION pageinspect;
CREATE EXTENSION
postgres=# SELECT * FROM heap_page_items(get_raw_page('pg_authid', 0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid
| t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |
t_data
----+--------+----------+--------+------------+--------+----------+--------+-------------+------------+--------+------------------+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 | 8080 | 1 | 108 | 1 | 0 | 0 | (0,1)
| 11 | 2313 | 32 | 1111111110000000 | 3373 |
\x70675f6d6f6e69746f720000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001000000000000ffffffff
2 | 7968 | 1 | 108 | 1 | 0 | 0 | (0,2)
| 11 | 2313 | 32 | 1111111110000000 | 3374 |
\x70675f726561645f616c6c5f73657474696e677300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001000000000000ffffffff
3 | 7856 | 1 | 108 | 1 | 0 | 0 | (0,3)
| 11 | 2313 | 32 | 1111111110000000 | 3375 |
\x70675f726561645f616c6c5f737461747300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001000000000000ffffffff
4 | 7744 | 1 | 108 | 1 | 0 | 0 | (0,4)
| 11 | 2313 | 32 | 1111111110000000 | 3377 |
\x70675f737461745f7363616e5f7461626c65730000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001000000000000ffffffff
5 | 7632 | 1 | 108 | 1 | 0 | 0 | (0,5)
| 11 | 2313 | 32 | 1111111110000000 | 4200 |
\x70675f7369676e616c5f6261636b656e6400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001000000000000ffffffff
6 | 7384 | 1 | 245 | 557 | 0 | 0 | (0,6)
| 11 | 11019 | 32 | 1111111111000000 | 24576 |
\x7265706c6963610000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001000001010000ffffffff24020000534352414d2d5348412d32353624343039363a305a6a336a39676d31314e473166455a6c75774a52413d3d2448394d446b2b445a4a723856544955702f684933664e4933376c777675696e49336731444a6672447863383d3a312f485442645265694c54306341724c47526f4d6d7739415a7351674c365a4c454a324e4e55306b5775413d
7 | 16 | 2 | 0 | | | |
| | | | | |
8 | 17 | 2 | 0 | | | |
| | | | | |
9 | 18 | 2 | 0 | | | |
| | | | | |
10 | 19 | 2 | 0 | | | |
| | | | | |
11 | 7136 | 1 | 245 | 7216348 | 0 | 0 | (0,11)
| 11 | 11019 | 32 | 1111111111000000 | 10 |
\x706f73746772657300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000101010101010100ffffffff24020000534352414d2d5348412d32353624343039363a72532f36303674706f523975465558797566392f6f413d3d2435412b4e745a444265333365397143556570422b324748664236567564717a65434b56427067576c2f41773d3a5a5863594e662f394f436744423345756f64514245564a526d39496361596d5a4b7856584e6a6d636554513d
12 | 6888 | 1 | 245 | 110077819 | 0 | 0 | (0,12)
| 11 | 11019 | 32 | 1111111111000000 | 432115 |
\x6261726d616e000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000101000001000000ffffffff24020000534352414d2d5348412d32353624343039363a7a716d737472687231416e376e39456f367a735677773d3d244d6351307473424e743466593169334d456e4c6a754866306e6c38774c44386f594236704630464e302b493d3a2b6336764a32372b68327832616d626c393554465168474867387270456a5a4a4952376d513044594e33553d
13 | 6640 | 1 | 245 | 110511334 | 0 | 0 | (0,13)
| 11 | 11019 | 32 | 1111111111000000 | 432127 |
\x73747265616d696e675f6261726d616e0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001000001010000ffffffff24020000534352414d2d5348412d32353624343039363a4e45496938767a714d7076752b594c585952377458413d3d245a39364b744d32316479687658716d3074616b7669717568794671574f45543647384174363954326e52413d3a5a4973674e71305537485344525168784f732b617364336d7559314d7365333373456956352b78694f52413d
14 | 20 | 2 | 0 | | | |
| | | | | |
15 | 0 | 3 | 0 | | | |
| | | | | |
16 | 6496 | 1 | 144 | 3031994631 | 0 | 0 | (0,16)
| 32779 | 10507 | 32 | 1111111111000000 | 675851 |
\x6e6167696f73000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001000001000000ffffffff496d64356333633236616163636439616665346437383061396239613464663634653639
17 | 6384 | 1 | 108 | 3032044199 | 0 | 0 | (0,17)
| 32779 | 10505 | 32 | 1111111110000000 | 774033 |
\x726561646163636573730000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001000000000000ffffffff
18 | 6136 | 1 | 245 | 3032044199 | 0 | 0 | (0,18)
| 32779 | 10507 | 32 | 1111111111000000 | 774134 |
\x74696572676173745f726f00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001000001000000ffffffff24020000534352414d2d5348412d32353624343039363a43335379382b507849324d4e4f4a67306e6f467a5a773d3d24373575643339325154422b46672f364949396a32576e41326e544b62535636666572447662657142432b343d3a5074736777516278566a7336352f4833554e6b484e58664335793578422f552f6973636b426269746159633d
19 | 5888 | 1 | 245 | 3032044199 | 0 | 0 | (0,19)
| 32779 | 10507 | 32 | 1111111111000000 | 8608367 |
\x666c6565745f6d6f62696c655f6170700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001000001000000ffffffff24020000534352414d2d5348412d32353624343039363a5069564870613054594247703075746f6a47365061413d3d247951577151776e64574d317a4651787050644936505662516a4246534f5131424632374f7a6162616d36413d3a64504a5144572b4258454f632f526554566c4a7678654541714e4d365878314b77744c667942674745334d3d
20 | 5640 | 1 | 245 | 3032070282 | 0 | 0 | (0,20)
| 32779 | 10507 | 32 | 1111111111000000 | 1661808 |
\x746965726761737400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001000001000000ffffffff24020000534352414d2d5348412d32353624343039363a7373427448544b574b68674232623265757634664e673d3d2453575a5253613578644a624951796649634244674875796e55684b436848676841475643767742504c50303d3a31656938464d354b30632b674a5a3233716e37314e46767978766d6978756a754c41666c4d445a676c63413d
(20 rows)

>
> Thanks.
> Greetings,
>
> Andres Freund
>

Thank you very much!

Best Regards

Paolo Crosato