Date: Fri, 28 Jan 2022 18:34:18 -0800
From: Noah Misch <noah@leadboat.com>
To: Maxim Boguk <maxim.boguk@gmail.com>
Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #17386: btree index corruption after reindex concurrently on
 write heavy table
Message-ID: <20220129023418.GA2452893@rfd.leadboat.com>
References: <17386-2227568627fb44ce@postgresql.org>
 <CAK-MWwQ7fboHz_s8aTn08i0W2aNH6QoELeWwmH-y80tOfsdDBQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="VS++wcV0S1rZb1Fb"
Content-Disposition: inline
In-Reply-To: 
 <CAK-MWwQ7fboHz_s8aTn08i0W2aNH6QoELeWwmH-y80tOfsdDBQ@mail.gmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Archived-At: 
 <https://www.postgresql.org/message-id/20220129023418.GA2452893%40rfd.leadboat.com>
Precedence: bulk


--VS++wcV0S1rZb1Fb
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Fri, Jan 28, 2022 at 04:17:28PM +0200, Maxim Boguk wrote:
> On Fri, Jan 28, 2022 at 3:42 PM PG Bug reporting form <noreply@postgresql.org> wrote:
> > PostgreSQL version: 14.1

> Seems something broken in combination of HOT update and REINDEX
> CONCURRENTLY.

Agreed.  Can you create a self-contained test case, perhaps by adapting
contrib/amcheck/t/002_cic.pl?  If this bug is like the ones fixed between 14.0
and 14.1, the base backup and WAL won't help us, unfortunately.  The transient
states are what matter.  I did try modifying the test to have elements of your
scenario, including HOT updates, KEY SHARE locks, and frozen tuples.  That
didn't reproduce the bug.  I'm attaching what I tried.

--VS++wcV0S1rZb1Fb
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="repro-reindex-hot-v0.patch"

Author:     Noah Misch <noah@leadboat.com>
Commit:     Noah Misch <noah@leadboat.com>

    
diff --git a/contrib/amcheck/t/002_cic.pl b/contrib/amcheck/t/002_cic.pl
index f6b91e8..831395f 100644
--- a/contrib/amcheck/t/002_cic.pl
+++ b/contrib/amcheck/t/002_cic.pl
@@ -21,8 +21,9 @@ $node->init;
 $node->append_conf('postgresql.conf', 'lock_timeout = 180000');
 $node->start;
 $node->safe_psql('postgres', q(CREATE EXTENSION amcheck));
-$node->safe_psql('postgres', q(CREATE TABLE tbl(i int)));
-$node->safe_psql('postgres', q(CREATE INDEX idx ON tbl(i)));
+$node->safe_psql('postgres', q(CREATE TABLE tbl(k int, v int)));
+$node->safe_psql('postgres', q(INSERT INTO tbl SELECT n, 0 FROM generate_series(0,9) t(n)));
+$node->safe_psql('postgres', q(CREATE INDEX idx ON tbl(k)));
 
 #
 # Stress CIC with pgbench.
@@ -32,33 +33,39 @@ $node->safe_psql('postgres', q(CREATE INDEX idx ON tbl(i)));
 # lock to ensure only one CIC runs at a time.
 #
 $node->pgbench(
-	'--no-vacuum --client=5 --transactions=100',
+	'--no-vacuum --client=5 --transactions=20000',
 	0,
 	[qr{actually processed}],
 	[qr{^$}],
 	'concurrent INSERTs and CIC',
 	{
-		'002_pgbench_concurrent_transaction' => q(
-			BEGIN;
-			INSERT INTO tbl VALUES(0);
-			COMMIT;
+		'hot_update_commit' => q(
+			UPDATE tbl SET v = v + 1 WHERE k = :client_id;
 		  ),
-		'002_pgbench_concurrent_transaction_savepoints' => q(
+		'hot_update_rollback' => q(
 			BEGIN;
-			SAVEPOINT s1;
-			INSERT INTO tbl VALUES(0);
-			COMMIT;
+			UPDATE tbl SET v = v + 1 WHERE k = :client_id;
+			ROLLBACK;
+		  ),
+		'vacuum' => q(
+			VACUUM FREEZE tbl;
+		  ),
+		'key_share' => q(
+			SELECT * FROM tbl FOR KEY SHARE;
 		  ),
 		'002_pgbench_concurrent_cic' => q(
 			SELECT pg_try_advisory_lock(42)::integer AS gotlock \gset
 			\if :gotlock
-				DROP INDEX CONCURRENTLY idx;
-				CREATE INDEX CONCURRENTLY idx ON tbl(i);
+				REINDEX INDEX CONCURRENTLY idx;
 				SELECT bt_index_check('idx',true);
 				SELECT pg_advisory_unlock(42);
 			\endif
 		  )
 	});
 
+print $node->safe_psql('postgres',
+					   q(SELECT * FROM pg_stat_all_tables where relname = 'tbl'),
+					   extra_params => ['--expanded']);
+
 $node->stop;
 done_testing();

--VS++wcV0S1rZb1Fb--