Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1o28fM-0003cH-SN for pgsql-admin@arkaria.postgresql.org; Fri, 17 Jun 2022 09:53:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1o28fK-0001xl-Pf for pgsql-admin@arkaria.postgresql.org; Fri, 17 Jun 2022 09:53:22 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1o28fJ-0001xa-RC for pgsql-admin@lists.postgresql.org; Fri, 17 Jun 2022 09:53:22 +0000 Received: from out4-smtp.messagingengine.com ([66.111.4.28]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1o28fB-0007ko-9b for pgsql-admin@lists.postgresql.org; Fri, 17 Jun 2022 09:53:21 +0000 Received: from compute4.internal (compute4.nyi.internal [10.202.2.44]) by mailout.nyi.internal (Postfix) with ESMTP id 100615C0183; Fri, 17 Jun 2022 05:53:10 -0400 (EDT) Received: from mailfrontend1 ([10.202.2.162]) by compute4.internal (MEProxy); Fri, 17 Jun 2022 05:53:10 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-transfer-encoding :content-type:date:date:feedback-id:feedback-id:from:from :in-reply-to:in-reply-to:message-id:mime-version:reply-to:sender :subject:subject:to:to:x-me-proxy:x-me-proxy:x-me-sender :x-me-sender:x-sasl-enc; s=fm2; t=1655459590; x=1655545990; bh=J a+8SE1vsk4loKW80iTGNEhCwBYWj/WVHrWR7578e9Q=; b=yLsUyRuk/SkH7sSiK pOxU/DuWuFXvhrGDLFPpRUCcB/AYMakqDshEyNldIyBRTPLXB/G6wMFv+HG89ZME ZkqFQHuSST190x6xSSz3El0ETC68O204I8j+K73C9CDym2O/HZArfqtdCcg+NyxB QnvjBbSaaVj5QO0qzPiolZ+m4JytcEZLZtkBhsZTTA4OF0NEfGUa7foG7uxchlSt u/piPf226afH1HCPoSPML2yLp/xVRm7pkz09nZx2yCIjRdcTNe2H+fwRZLvfjqdV cA/vXh8B1H6R0daiCPEotegTIPW0i78cy+IC6UTnJMMHWunQHAroqYbr7EI8/zAb O0c8w== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvfedruddvhedgudelucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepfffhvfevuffkgggtugfgjgesthekredttddtjeenucfhrhhomheplmhlvhgr rhhoucfjvghrrhgvrhgruceorghlvhhhvghrrhgvsegrlhhvhhdrnhhoqdhiphdrohhrgh eqnecuggftrfgrthhtvghrnhepvdehueffhefgueekueefheehgffhuefgfedukeejvedu ffegffekgeffffevgeehnecuffhomhgrihhnpegvnhhtvghrphhrihhsvggusgdrtghomh enucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegrlhhv hhgvrhhrvgesrghlvhhhrdhnohdqihhprdhorhhg X-ME-Proxy: Feedback-ID: ia2694551:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Fri, 17 Jun 2022 05:53:09 -0400 (EDT) Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id CF30034E; Fri, 17 Jun 2022 11:53:05 +0200 (CEST) Date: Fri, 17 Jun 2022 11:53:05 +0200 From: =?utf-8?Q?=C3=81lvaro?= Herrera To: Sergey Aleynikov Cc: pgsql-admin@lists.postgresql.org Subject: Re: Xmax precedes relation freeze threshold errors Message-ID: <20220617095305.dhu6yk5po7b4qrih@alvherre.pgsql> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 2022-Jun-16, Sergey Aleynikov wrote: > (verify_heapam before) > blkno | offnum | attnum | msg > -------+--------+--------+----------------------------------------------------------------- > 20365 | 8 | | xmax 1965224948 precedes relation freeze threshold 1:1914858032 > 20365 | 12 | | xmin 1965224948 precedes relation freeze threshold 1:1914858032 > (2 rows) Hmm, well, these errors make no sense, because 1965224948 is definitely not earlier than the stated threshold of 1914858032. There might be a bug in the amcheck conversion of the epochless XID value to FullXid, or in the comparison functions. For the record, the values for this page are below. For (20365,8) the infomask is 0x2102 which is HEAP_UPDATED | HEAP_XMIN_COMMITTED; there's no bit for Xmax. The next one in the chain is the other complained-about tuple, (20365,12) which has a matching Xmin (no surprise there) and the infomask is 0x2092 which is HEAP_UPDATED | HEAP_XMAX_LOCK_ONLY | HEAP_XMAX_KEYSHR_LOCK. So this is a tuple that was updated and then the new version was used by SELECT FOR KEY SHARE. Nothing strange here. 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 | 25 | 2 | 0 | | | | | | | | | | 2 | 4504 | 1 | 204 | 1965224930 | 1965224938 | 6 | (20365,8) | 57347 | 9474 | 24 | | | \016\301\177\005\000\000\000\000\300\271\261\265\215\204\002\000\220\002\000\000[[1,0,1655367415],[2,0,1655368111],[3,0,1655316514],[4,1,1345141307],[7,0,1655368111],[10,46,1655368114],[12,0,1655316561],[13,2,1602489365],[16,20,1655368127]] 3 | 8040 | 1 | 147 | 1852898199 | 0 | 38 | (20365,3) | 3 | 11010 | 24 | | | \205<\207\005\000\000\000\000\200\317f\023/\202\002\000\327[[1,0,1649925421],[2,3,1652762654],[4,1,1336885001],[7,0,1652762654],[10,49,1649925346],[16,0,1649925383]] 4 | 27 | 2 | 0 | | | | | | | | | | 5 | 7768 | 1 | 272 | 1964963817 | 1964963897 | 7 | (20365,5) | 40963 | 10690 | 24 | | | \035(\345\005\000\000\000\000\300\301\017\300\213\204\002\000\240\003\000\000[[1,0,1655356016],[2,0,1655355988],[3,0,1655359711],[4,1,1318680670],[6,1,1580191566],[7,0,1655355987],[8,4,1580191566],[9,3,1580191566],[10,39,1655358356],[12,1,1580280953],[13,3,1580191566],[14,0,1580191566],[16,0,1594455141]] 6 | 7488 | 1 | 273 | 1904288297 | 0 | 7 | (20365,6) | 32771 | 11010 | 24 | | | H\032\311\005\000\000\000\000\3007\252\326`\203\002\000\244\003\000\000[[1,1,1638359432],[2,8,1654075863],[3,0,1654075895],[4,1,1411368668],[6,0,1643527342],[7,0,1654075863],[8,4,1586241825],[9,3,1586241825],[10,48,1653909745],[12,1,1586241825],[13,3,1586241825],[14,0,1586241825],[16,30,1586241825]] 7 | 32 | 2 | 0 | | | | | | | | | | 8 | 4296 | 1 | 204 | 1965224938 | 1965224948 | 6 | (20365,12) | 57347 | 8450 | 24 | | | \016\301\177\005\000\000\000\000\300\271\261\265\215\204\002\000\220\002\000\000[[1,0,1655367415],[2,0,1655368111],[3,0,1655316514],[4,1,1345141307],[7,0,1655368111],[10,46,1655368114],[12,0,1655316561],[13,2,1602489365],[16,19,1655368127]] 9 | 6 | 2 | 0 | | | | | | | | | | 10 | 29 | 2 | 0 | | | | | | | | | | 11 | 7208 | 1 | 273 | 1965056934 | 1965056934 | 7 | (20365,11) | 32771 | 10642 | 24 | | | \377\232\310\005\000\000\000\000\200\271\334q\214\204\002\000\244\003\000\000[[1,0,1655361162],[2,5,1655361996],[3,0,1655362694],[4,1,1358874131],[6,0,1655055632],[7,0,1655361158],[8,4,1580366031],[9,3,1639915217],[10,46,1655361947],[12,1,1653134366],[13,3,1580366031],[14,0,1580366031],[16,30,1580366031]] 12 | 4088 | 1 | 204 | 1965224948 | 1965224948 | 6 | (20365,12) | 32771 | 8338 | 24 | | | \016\301\177\005\000\000\000\000\000\374\300\265\215\204\002\000\220\002\000\000[[1,0,1655367415],[2,0,1655368111],[3,0,1655316514],[4,1,1345141307],[7,0,1655368111],[10,46,1655368114],[12,0,1655316561],[13,2,1602489365],[16,18,1655368128]] 13 | 7160 | 1 | 43 | 1957335917 | 1964638027 | 1 | (20365,13) | 8195 | 2498 | 24 | | | \310jo\007\000\000\000\000\200\027k\264b\204\002\000\007[] 14 | 16 | 2 | 0 | | | | | | | | | | 15 | 0 | 0 | 0 | | | | | | | | | | 16 | 6880 | 1 | 273 | 1965223751 | 1965223751 | 8 | (20365,16) | 32771 | 10642 | 24 | | | \347\243\236\005\000\000\000\000\300h\214\263\215\204\002\000\244\003\000\000[[1,0,1655240440],[2,9,1655368091],[3,0,1654972875],[4,1,1397413995],[6,0,1654175940],[7,0,1655368091],[8,4,1582557307],[9,3,1582557307],[10,47,1654197675],[12,1,1629279335],[13,3,1587923475],[14,0,1582557307],[16,30,1648131697]] 17 | 0 | 0 | 0 | | | | | | | | | | 18 | 0 | 0 | 0 | | | | | | | | | | 19 | 0 | 0 | 0 | | | | | | | | | | 20 | 0 | 0 | 0 | | | | | | | | | | 21 | 0 | 0 | 0 | | | | | | | | | | 22 | 0 | 0 | 0 | | | | | | | | | | 23 | 0 | 0 | 0 | | | | | | | | | | 24 | 0 | 0 | 0 | | | | | | | | | | 25 | 6640 | 1 | 238 | 1490075747 | 0 | 5 | (20365,25) | 32771 | 11010 | 24 | | | \373\021J\007\000\000\000\000\300\207\236\331|z\002\000\030\003\000\000[[1,0,1644300599],[2,10,1643357339],[3,4,1643109769],[6,0,1643357339],[7,0,1643365735],[8,4,1643357339],[9,3,1643357339],[12,0,1643360113],[13,3,1643108769],[14,0,1643357339],[16,30,1643357339]] 26 | 0 | 0 | 0 | | | | | | | | | | 27 | 6360 | 1 | 273 | 1962697004 | 1962697004 | 7 | (20365,27) | 32771 | 10642 | 24 | | | \3342\251\005\000\000\000\000\200\020\230\276x\204\002\000\244\003\000\000[[1,1,1655183659],[2,9,1655278082],[3,4,1655217231],[4,1,1646965839],[6,0,1655275445],[7,0,1655278082],[8,4,1636371136],[9,3,1636371136],[10,46,1655182361],[12,1,1648303165],[13,3,1636371136],[14,0,1636371136],[16,30,1636371136]] 28 | 0 | 0 | 0 | | | | | | | | | | 29 | 6080 | 1 | 273 | 1964885708 | 1964885708 | 9 | (20365,29) | 32771 | 10642 | 24 | | | y\031\300\006\000\000\000\000\300=&.\213\204\002\000\244\003\000\000[[1,0,1655356874],[2,0,1655356785],[3,3,1655356828],[4,0,1607932548],[6,0,1593701110],[7,0,1655356785],[8,4,1589636764],[9,3,1589636764],[10,45,1655357263],[12,0,1655223006],[13,2,1633191479],[14,0,1589636764],[16,22,1655285002]] 30 | 5 | 2 | 0 | | | | | | | | | | 31 | 6032 | 1 | 43 | 1529256935 | 0 | 1 | (20365,31) | 3 | 2818 | 24 | | | z\315N\007\000\000\000\000\000\207\037)0{\002\000\007[] 32 | 5752 | 1 | 273 | 1964761973 | 1964761973 | 7 | (20365,32) | 32771 | 10642 | 24 | | | \377\020\322\005\000\000\000\000\000d\303+\212\204\002\000\244\003\000\000[[1,1,1635340514],[2,8,1655352336],[3,0,1655352928],[4,1,1324578627],[6,0,1652362638],[7,0,1655352336],[8,4,1580283680],[9,3,1580283680],[10,49,1655321899],[12,1,1623273443],[13,3,1580283680],[14,0,1580283680],[16,30,1580283680]] 33 | 0 | 0 | 0 | | | | | | | | | | 34 | 0 | 0 | 0 | | | | | | | | | | 35 | 5472 | 1 | 273 | 1962530750 | 1962530750 | 9 | (20365,35) | 32771 | 10642 | 24 | | | "\273o\005\000\000\000\000\200\024\276\206w\204\002\000\244\003\000\000[[1,0,1655272251],[2,6,1655272847],[3,0,1655110969],[4,1,1364884674],[6,0,1649045620],[7,0,1655272847],[8,4,1621844466],[9,3,1621844466],[10,49,1655272850],[12,1,1635832362],[13,3,1621821962],[14,0,1621844466],[16,30,1621844466]] 36 | 5264 | 1 | 204 | 1965224703 | 1965224917 | 6 | (20365,38) | 57347 | 9474 | 24 | | | \016\301\177\005\000\000\000\000\300\2477\265\215\204\002\000\220\002\000\000[[1,0,1655367415],[2,0,1655368111],[3,0,1655316514],[4,1,1345141307],[7,0,1655368111],[10,46,1655368114],[12,0,1655316561],[13,2,1602489365],[16,23,1655368119]] 37 | 5128 | 1 | 129 | 1964847563 | 1964847563 | 7 | (20365,37) | 32771 | 10642 | 24 | | | \201A\250\005\000\000\000\000\300\215\253\345\212\204\002\000\263[[1,0,1655355920],[2,6,1655355999],[3,0,1655356047],[7,0,1655355997],[10,46,1655356007]] 38 | 4920 | 1 | 204 | 1965224917 | 1965224925 | 6 | (20365,39) | 57347 | 9474 | 24 | | | \016\301\177\005\000\000\000\000\200w\242\265\215\204\002\000\220\002\000\000[[1,0,1655367415],[2,0,1655368111],[3,0,1655316514],[4,1,1345141307],[7,0,1655368111],[10,46,1655368114],[12,0,1655316561],[13,2,1602489365],[16,22,1655368126]] 39 | 4712 | 1 | 204 | 1965224925 | 1965224930 | 6 | (20365,2) | 57347 | 9474 | 24 | | | \016\301\177\005\000\000\000\000\200w\242\265\215\204\002\000\220\002\000\000[[1,0,1655367415],[2,0,1655368111],[3,0,1655316514],[4,1,1345141307],[7,0,1655368111],[10,46,1655368114],[12,0,1655316561],[13,2,1602489365],[16,21,1655368126]] 40 | 0 | 0 | 0 | | | | | | | | | | 41 | 0 | 0 | 0 | | | | | | | | | | 42 | 0 | 0 | 0 | | | | | | | | | | 43 | 0 | 0 | 0 | | | | | | | | | | 44 | 0 | 0 | 0 | | | | | | | | | | 45 | 35 | 2 | 0 | | | | | | | | | | 46 | 0 | 0 | 0 | | | | | | | | | | 47 | 37 | 2 | 0 | | | | | | | | | | 48 | 11 | 2 | 0 | | | | | | | | | | 49 | 0 | 0 | 0 | | | | | | | | | | 50 | 0 | 0 | 0 | | | | | | | | | | 51 | 36 | 2 | 0 | | | | | | | | | | (51 rows) -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/ "Always assume the user will do much worse than the stupidest thing you can imagine." (Julien PUYDT)