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 1nAPvn-0008UC-Ty for pgsql-committers@arkaria.postgresql.org; Thu, 20 Jan 2022 05:24:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nAPvl-0005Jo-QC for pgsql-committers@arkaria.postgresql.org; Thu, 20 Jan 2022 05:24:17 +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 1nAPvk-0005Je-Nc for pgsql-committers@lists.postgresql.org; Thu, 20 Jan 2022 05:24:17 +0000 Received: from wout2-smtp.messagingengine.com ([64.147.123.25]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nAPvf-0000Uk-JW for pgsql-committers@lists.postgresql.org; Thu, 20 Jan 2022 05:24:16 +0000 Received: from compute4.internal (compute4.nyi.internal [10.202.2.44]) by mailout.west.internal (Postfix) with ESMTP id 2D6B13202056; Thu, 20 Jan 2022 00:24:07 -0500 (EST) Received: from mailfrontend1 ([10.202.2.162]) by compute4.internal (MEProxy); Thu, 20 Jan 2022 00:24:07 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=anarazel.de; h= cc:cc:content-type:date:date:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:sender:subject :subject:to:to; s=fm3; bh=1BH2Kuqh4xX19v28mbGd/xXjdtBaaqsN7b2eOz YUN74=; b=GaqpV15tMARsbszfYTU6VX21D1JNSooFhBeZQzReAqRlLoRaJ5+rOu uYoOQBUt46cs4TrAyfLLIBEVl92c490TaGkq7EG2oPol4SoUaJNfj+5gMAOXxhGW 3wLAF1HB8UUYE0hKUgUuCpQZtc3UN07fpQH1WUfnNB882YgGDSDYE4FP/uWEKy8+ 8VghAmxnqr/yDaAmNzL6belJWjr8Nnri1UCouJ7hBAwZT+LbowkN/oisFzh9ZSKD vmbyFetewgYIDoWvvx2Ri13urIsbU5CS3aqTqmtMqsoibQw7Z1ro3oXQDovQS5BC DFy0W6JLdhwmDzriihCKuFzqfMkZywxQ== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-type:date:date:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:sender:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm1; bh=1BH2Kuqh4xX19v28m bGd/xXjdtBaaqsN7b2eOzYUN74=; b=kI709S/bHMBr2EjLgj7Cipyzx540fqvUG 2iF1wfxVuFGuJWycRcC9APqEcOYCsMr+GG8u+b/xfEW6iEKpFuCKYORy7pVIcZ7Q 6IquLHU/Vv6jkDL2UBwumJqOSfrLWPAR9qTRUUimmWA7uSmb9TFygTkC5h5RWRO0 xarvIEJUGTyOcOsqDyfX+j4dzO3GmD8rEVeHwBd6D4BtxX1avY/CJhKWi43veUoD D77w2Z9PYab9lTk26YfGpx4Y+wP799+Co0LW9cOwDE/pNBqNHPaTR8XGDLhfPj8t H9XjBs+LOvD4L5tkpne/K5mEzeRj256tkwKzJ9636vwlcHxY2XHvA== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvvddrudejgdekgecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenuc fjughrpeffhffvuffkfhggtggujgesthdtredttddtvdenucfhrhhomheptehnughrvghs ucfhrhgvuhhnugcuoegrnhgurhgvshesrghnrghrrgiivghlrdguvgeqnecuggftrfgrth htvghrnhepudekhfekleeugeevteehleffffejgeelueduleeffeeutdelffeujeffhfeu ffdunecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghilhhfrhhomheprg hnughrvghssegrnhgrrhgriigvlhdruggv X-ME-Proxy: Received: by mail.messagingengine.com (Postfix) with ESMTPA; Thu, 20 Jan 2022 00:24:05 -0500 (EST) Date: Wed, 19 Jan 2022 21:24:04 -0800 From: Andres Freund To: Thomas Munro Cc: Tom Lane , Michael Paquier , Thomas Munro , pgsql-committers Subject: Re: pgsql: Test replay of regression tests, attempt II. Message-ID: <20220120052404.sonrhq3f3qgplpzj@alap3.anarazel.de> References: <20220117203746.oj43254j5jurbneu@alap3.anarazel.de> <374643.1642536944@sss.pgh.pa.us> <20220118204012.qpfas4fmvc55g4ei@alap3.anarazel.de> <415981.1642544346@sss.pgh.pa.us> <20220118230838.amvazn2ig4cf56oy@alap3.anarazel.de> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi, On 2022-01-20 17:23:30 +1300, Thomas Munro wrote: > Having failed to reproduce this locally, I clicked on "re-run tests" > all afternoon on CI until eventually I captured a failure log[1] > there, with the smoking gun: Phew, finally. > Since this page doesn't require wraparound vacuuming, if we fail to > conditionally acquire the cleanup lock, this block skips the page. The most likely source for such a pin is bgwriter, I think? It's not actually *immediately* obviously why the page doesn't require wraparound, as VACUUM FREEZE should use a very aggressive one. The reason is presumably that there are concurrent backends holding back the horizon. Note that the oldest xmin in the log below (15356 for both vacuums) is older than the xids from the intentionally failed insert (15465). I wonder if the easiest way to make this test reliable would be to make the table a temporary one? That now uses very aggressive horizons, there's no bgwriter that could pin the page, etc. Regards, Andres 2022-01-20 03:58:31.966 UTC [32584][client backend] [pg_regress/reloptions][4/5416:0] LOG: statement: INSERT INTO reloptions_test VALUES (1, NULL), (NULL, NULL); 2022-01-20 03:58:31.967 UTC [32584][client backend] [pg_regress/reloptions][4/5416:15465] ERROR: null value in column "i" of relation "reloptions_test" violates not-null constraint 2022-01-20 03:58:31.967 UTC [32584][client backend] [pg_regress/reloptions][4/5416:15465] DETAIL: Failing row contains (null, null). 2022-01-20 03:58:31.967 UTC [32584][client backend] [pg_regress/reloptions][4/5416:15465] STATEMENT: INSERT INTO reloptions_test VALUES (1, NULL), (NULL, NULL); 2022-01-20 03:58:31.970 UTC [32584][client backend] [pg_regress/reloptions][4/5417:0] LOG: statement: VACUUM (FREEZE, DISABLE_PAGE_SKIPPING) reloptions_test; 2022-01-20 03:58:31.973 UTC [32584][client backend] [pg_regress/reloptions][4/5418:0] LOG: automatic aggressive vacuum of table "regression.public.reloptions_test": index scans: 0 pages: 0 removed, 1 remain, 1 skipped due to pins, 0 skipped frozen tuples: 0 removed, 0 remain, 0 are dead but not yet removable, oldest xmin: 15356 index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed avg read rate: 372.024 MB/s, avg write rate: 0.000 MB/s buffer usage: 3 hits, 6 misses, 0 dirtied WAL usage: 1 records, 0 full page images, 237 bytes system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s 2022-01-20 03:58:31.973 UTC [32584][client backend] [pg_regress/reloptions][4/5418:0] STATEMENT: VACUUM (FREEZE, DISABLE_PAGE_SKIPPING) reloptions_test; 2022-01-20 03:58:31.973 UTC [32584][client backend] [pg_regress/reloptions][4/5419:0] LOG: automatic aggressive vacuum of table "regression.pg_toast.pg_toast_35786": index scans: 0 pages: 0 removed, 0 remain, 0 skipped due to pins, 0 skipped frozen tuples: 0 removed, 0 remain, 0 are dead but not yet removable, oldest xmin: 15356 avg read rate: 790.028 MB/s, avg write rate: 0.000 MB/s buffer usage: 13 hits, 9 misses, 0 dirtied WAL usage: 0 records, 0 full page images, 0 bytes system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s 2022-01-20 03:58:31.973 UTC [32584][client backend] [pg_regress/reloptions][4/5419:0] STATEMENT: VACUUM (FREEZE, DISABLE_PAGE_SKIPPING) reloptions_test;