nocfbot-stress_tests_for_repack_concurrently.patch

Subject: [PATCH] stress tests for repack concurrently
---
Index: contrib/amcheck/meson.build
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/contrib/amcheck/meson.build b/contrib/amcheck/meson.build
--- a/contrib/amcheck/meson.build	(revision 26b7a7cbedd8c0848c1056ef820bed2ddb5f522c)
+++ b/contrib/amcheck/meson.build	(revision 14bf8dbc0ac0d52a6121be2484f652fc0c04732d)
@@ -50,6 +50,8 @@
       't/004_verify_nbtree_unique.pl',
       't/005_pitr.pl',
       't/006_verify_gin.pl',
+      't/007_repack_concurrently.pl',
+      't/008_repack_concurrently.pl',
     ],
   },
 }
Index: contrib/amcheck/t/007_repack_concurrently.pl
===================================================================
diff --git a/contrib/amcheck/t/007_repack_concurrently.pl b/contrib/amcheck/t/007_repack_concurrently.pl
new file mode 100644
--- /dev/null	(revision 14bf8dbc0ac0d52a6121be2484f652fc0c04732d)
+++ b/contrib/amcheck/t/007_repack_concurrently.pl	(revision 14bf8dbc0ac0d52a6121be2484f652fc0c04732d)
@@ -0,0 +1,111 @@
+
+# Copyright (c) 2021-2025, PostgreSQL Global Development Group
+
+# Test REPACK CONCURRENTLY with concurrent modifications
+use strict;
+use warnings FATAL => 'all';
+
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+
+use Test::More;
+
+my $node;
+
+#
+# Test set-up
+#
+$node = PostgreSQL::Test::Cluster->new('CIC_test');
+$node->init;
+$node->append_conf('postgresql.conf',
+	'lock_timeout = ' . (1000 * $PostgreSQL::Test::Utils::timeout_default));
+$node->append_conf(
+	'postgresql.conf', qq(
+wal_level = logical
+max_worker_processes = 32
+));
+
+my $n=1000;
+my $no_hot = int(rand(2));
+
+$node->start;
+$node->safe_psql('postgres', q(CREATE TABLE tbl(i int PRIMARY KEY, j int)));
+
+if ($no_hot)
+{
+	$node->safe_psql('postgres', q(CREATE INDEX test_idx ON tbl(j);));
+}
+else
+{
+	$node->safe_psql('postgres', q(CREATE INDEX test_idx ON tbl(i);));
+}
+
+
+# Load amcheck
+$node->safe_psql('postgres', q(CREATE EXTENSION amcheck));
+
+# Insert $n rows into tbl
+$node->safe_psql('postgres', qq(
+	INSERT INTO tbl SELECT i, i FROM generate_series(1,$n) i
+));
+
+my $sum = $node->safe_psql('postgres', q(
+	SELECT SUM(j) AS sum FROM tbl
+));
+
+
+$node->pgbench(
+'--no-vacuum --client=15 --jobs=4 --exit-on-abort --transactions=5000',
+0,
+[qr{actually processed}],
+[qr{^$}],
+'concurrent operations with REPACK CONCURRENTLY',
+{
+	'concurrent_ops' => qq(
+		SELECT pg_try_advisory_lock(42)::integer AS gotlock \\gset
+		\\if :gotlock
+			REPACK (CONCURRENTLY) tbl USING INDEX tbl_pkey;
+			SELECT bt_index_parent_check('tbl_pkey', heapallindexed => true);
+			SELECT bt_index_parent_check('test_idx', heapallindexed => true);
+			\\sleep 10 ms
+
+			REPACK (CONCURRENTLY) tbl USING INDEX test_idx;
+			SELECT bt_index_parent_check('tbl_pkey', heapallindexed => true);
+			SELECT bt_index_parent_check('test_idx', heapallindexed => true);
+			\\sleep 10 ms
+
+			REPACK (CONCURRENTLY) tbl;
+			SELECT bt_index_parent_check('tbl_pkey', heapallindexed => true);
+			SELECT bt_index_parent_check('test_idx', heapallindexed => true);
+			\\sleep 10 ms
+
+			SELECT pg_advisory_unlock(42);
+		\\else
+			\\set num_a random(1, $n)
+			\\set num_b random(1, $n)
+			\\set diff random(1, 10000)
+			BEGIN;
+			UPDATE tbl SET j = j + :diff WHERE i = :num_a;
+			\\sleep 1 ms
+			UPDATE tbl SET j = j - :diff WHERE i = :num_b;
+			\\sleep 1 ms
+			COMMIT;
+
+			BEGIN
+			--TRANSACTION ISOLATION LEVEL REPEATABLE READ
+			;
+			SELECT 1;
+			\\sleep 1 ms
+			SELECT COALESCE(SUM(j), 0) AS sum FROM tbl \\gset p_
+			\\if :p_sum != $sum
+				COMMIT;
+				SELECT (:p_sum) / 0;
+			\\endif
+
+			COMMIT;
+		\\endif
+	)
+});
+
+$node->stop;
+done_testing();
Index: contrib/amcheck/t/008_repack_concurrently.pl
===================================================================
diff --git a/contrib/amcheck/t/008_repack_concurrently.pl b/contrib/amcheck/t/008_repack_concurrently.pl
new file mode 100644
--- /dev/null	(revision 14bf8dbc0ac0d52a6121be2484f652fc0c04732d)
+++ b/contrib/amcheck/t/008_repack_concurrently.pl	(revision 14bf8dbc0ac0d52a6121be2484f652fc0c04732d)
@@ -0,0 +1,102 @@
+
+# Copyright (c) 2021-2025, PostgreSQL Global Development Group
+
+# Test REPACK CONCURRENTLY with concurrent modifications
+use strict;
+use warnings FATAL => 'all';
+
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+
+use Test::More;
+
+my $node;
+
+#
+# Test set-up
+#
+$node = PostgreSQL::Test::Cluster->new('CIC_test');
+$node->init;
+$node->append_conf('postgresql.conf',
+	'lock_timeout = ' . (1000 * $PostgreSQL::Test::Utils::timeout_default));
+$node->append_conf(
+	'postgresql.conf', qq(
+wal_level = logical
+max_worker_processes = 32
+));
+
+my $no_hot = int(rand(2));
+
+$node->start;
+$node->safe_psql('postgres', q(CREATE TABLE tbl(i SERIAL PRIMARY KEY, j int)));
+if ($no_hot)
+{
+	$node->safe_psql('postgres', q(CREATE INDEX test_idx ON tbl(j);));
+}
+else
+{
+	$node->safe_psql('postgres', q(CREATE INDEX test_idx ON tbl(i);));
+}
+
+# Load amcheck
+$node->safe_psql('postgres', q(CREATE EXTENSION amcheck));
+
+my $sum = $node->safe_psql('postgres', q(
+	SELECT SUM(j) AS sum FROM tbl
+));
+
+$node->safe_psql('postgres', q(CREATE UNLOGGED SEQUENCE last_j START 1 INCREMENT 1;));
+
+
+$node->pgbench(
+'--no-vacuum --client=15 --jobs=4 --exit-on-abort --transactions=1000',
+0,
+[qr{actually processed}],
+[qr{^$}],
+'concurrent operations with REPACK CONCURRENTLY',
+{
+	'concurrent_ops' => qq(
+		SELECT pg_try_advisory_lock(42)::integer AS gotlock \\gset
+		\\if :gotlock
+			REPACK (CONCURRENTLY) tbl USING INDEX tbl_pkey;
+			SELECT bt_index_parent_check('tbl_pkey', heapallindexed => true);
+			SELECT bt_index_parent_check('test_idx', heapallindexed => true);
+			\\sleep 10 ms
+
+			REPACK (CONCURRENTLY) tbl USING INDEX test_idx;
+			SELECT bt_index_parent_check('tbl_pkey', heapallindexed => true);
+			SELECT bt_index_parent_check('test_idx', heapallindexed => true);
+			\\sleep 10 ms
+
+			REPACK (CONCURRENTLY) tbl;
+			SELECT bt_index_parent_check('tbl_pkey', heapallindexed => true);
+			SELECT bt_index_parent_check('test_idx', heapallindexed => true);
+			\\sleep 10 ms
+
+			SELECT pg_advisory_unlock(42);
+		\\else
+			SELECT pg_advisory_lock(43);
+				BEGIN;
+				INSERT INTO tbl(j) VALUES (nextval('last_j')) RETURNING j \\gset p_
+				COMMIT;
+			SELECT pg_advisory_unlock(43);
+			\\sleep 1 ms
+
+			BEGIN
+			--TRANSACTION ISOLATION LEVEL REPEATABLE READ
+			;
+			SELECT 1;
+			\\sleep 1 ms
+			SELECT COUNT(*) AS count FROM tbl WHERE j <= :p_j \\gset p_
+			\\if :p_count != :p_j
+				COMMIT;
+				SELECT (:p_count) / 0;
+			\\endif
+
+			COMMIT;
+		\\endif
+	)
+});
+
+$node->stop;
+done_testing();