public inbox for [email protected]
help / color / mirror / Atom feedFrom: Mihail Nikalayeu <[email protected]>
To: Antonin Houska <[email protected]>
Cc: Alvaro Herrera <[email protected]>
Cc: Pg Hackers <[email protected]>
Cc: Robert Treat <[email protected]>
Subject: Re: Adding REPACK [concurrently]
Date: Sat, 7 Feb 2026 15:16:27 +0100
Message-ID: <CADzfLwWNv5QDn6qmxCRV-p_ijSTGwNcEZFCOXt09+RmpSG2=+w@mail.gmail.com> (raw)
In-Reply-To: <27597.1770395398@localhost>
References: <[email protected]>
<11247.1767609087@localhost>
<11558.1767609632@localhost>
<141054.1767891540@localhost>
<CADzfLwU-OmxW3t3AoQo9=K7uq4G1yZ-txcetzW3jbcVxV_pJew@mail.gmail.com>
<137668.1768235610@localhost>
<CADzfLwUJSHKGxYw+vMUZ_Hr2YeuxO2Q5w13HKgUUN1725tjY5Q@mail.gmail.com>
<CADzfLwXJ+4s1tJuG9injcxAUP3urj9D6dUAPOCaX33UeiUxrRQ@mail.gmail.com>
<74802.1769071060@localhost>
<CADzfLwVZ_DeU_3avD=G4ZHFJJgZ0EOFzxnmWxwyB23zsS-uxjA@mail.gmail.com>
<CADzfLwUEH5+LjCN+6kRfSsXwuou8rKXyVV42Wi-O_TG0360Kug@mail.gmail.com>
<3901.1769412880@localhost>
<88003.1769511456@localhost>
<CADzfLwXdaJh4awQstc2PpBz=EBBc6tMA50wYLqMoEtY5B+WUnA@mail.gmail.com>
<57210.1769801636@localhost>
<CADzfLwUukiGOPoUkDgf6oEB-Y0TnNy6UFUN4obnU-AN5W1N=sw@mail.gmail.com>
<8029.1770024929@localhost>
<CADzfLwVf6jB5QBXR3nM838LV6oyqAGJ5b5tXc5aZdovxHPj_kg@mail.gmail.com>
<27597.1770395398@localhost>
Hi!
> Indeed, the server log seems to indicate relationship to
> VACUUM:
> 2026-02-01 16:44:58.878 UTC autovacuum worker[22589] LOG: automatic
vacuum of table "postgres.pg_catalog.pg_class": index scans: 1
O, it's a good clue!
I have added some vacuum calls for pg_class in a stress test - and now it
fails much more often (check attachment).
It is "ERROR: cache lookup failed for relation" - but I think it may share
the cause with "attempted to overwrite invisible tuple.
See:
https://cirrus-ci.com/build/4852126532239360 - with "Use multiple snapshots
to copy the data."
https://cirrus-ci.com/build/6429084491710464 - with "Use background worker
to do logical decoding."
But I am unable to reproduce the issue with only "Add CONCURRENTLY option
to REPACK command."
https://cirrus-ci.com/build/6467070524653568
Best regards,
Mikhail.
Attachments:
[application/x-patch] nocfbot-vX-0001-stress-tests-for-repack-concurrently.patch (6.7K, 3-nocfbot-vX-0001-stress-tests-for-repack-concurrently.patch)
download | inline diff:
From cbb3fc402e8a0ce14abee46f329b15df6157b7f2 Mon Sep 17 00:00:00 2001
From: Mikhail Nikalayeu <[email protected]>
Date: Tue, 27 Jan 2026 21:41:56 +0100
Subject: [PATCH vX 1/3] stress tests for repack concurrently
---
contrib/amcheck/meson.build | 2 +
contrib/amcheck/t/007_repack_concurrently.pl | 116 +++++++++++++++++++
contrib/amcheck/t/008_repack_concurrently.pl | 106 +++++++++++++++++
3 files changed, 224 insertions(+)
create mode 100644 contrib/amcheck/t/007_repack_concurrently.pl
create mode 100644 contrib/amcheck/t/008_repack_concurrently.pl
diff --git a/contrib/amcheck/meson.build b/contrib/amcheck/meson.build
index d5137ef691d..f726db2ffe0 100644
--- a/contrib/amcheck/meson.build
+++ b/contrib/amcheck/meson.build
@@ -50,6 +50,8 @@ tests += {
'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',
],
},
}
diff --git a/contrib/amcheck/t/007_repack_concurrently.pl b/contrib/amcheck/t/007_repack_concurrently.pl
new file mode 100644
index 00000000000..fb110b0b57d
--- /dev/null
+++ b/contrib/amcheck/t/007_repack_concurrently.pl
@@ -0,0 +1,116 @@
+
+# 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;
+
+ \\set v random(1, 300)
+ \\ if :v = 1
+ VACUUM ANALYZE pg_catalog.pg_class;
+ \\ endif
+
+ 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();
diff --git a/contrib/amcheck/t/008_repack_concurrently.pl b/contrib/amcheck/t/008_repack_concurrently.pl
new file mode 100644
index 00000000000..329e6d7ed47
--- /dev/null
+++ b/contrib/amcheck/t/008_repack_concurrently.pl
@@ -0,0 +1,106 @@
+
+# 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=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
+ 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
+ \\set v random(1, 300)
+ \\ if :v = 1
+ VACUUM ANALYZE pg_catalog.pg_class;
+ \\ endif
+
+ 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();
--
2.43.0
view thread (31+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Adding REPACK [concurrently]
In-Reply-To: <CADzfLwWNv5QDn6qmxCRV-p_ijSTGwNcEZFCOXt09+RmpSG2=+w@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox