Hello,
2018-05-22 18:49 GMT+02:00 Andres Freund <andres@xxxxxxxxxxx>:
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"
>
That shouldn't be the sole cause.> 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?
> 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
