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.
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
