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
Date: Fri, 28 Jan 2022 18:34:18 -0800
Message-ID: <20220129023418.GA2452893@rfd.leadboat.com> (raw)
In-Reply-To: <CAK-MWwQ7fboHz_s8aTn08i0W2aNH6QoELeWwmH-y80tOfsdDBQ@mail.gmail.com>
References: <17386-2227568627fb44ce@postgresql.org>
<CAK-MWwQ7fboHz_s8aTn08i0W2aNH6QoELeWwmH-y80tOfsdDBQ@mail.gmail.com>
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.
Attachments:repro-reindex-hot-v0.patch (text/plain, 2.1K) download
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.plindex 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();
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: pgsql-bugs@postgresql.org
Cc: noah@leadboat.com, maxim.boguk@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17386: btree index corruption after reindex concurrently on write heavy table
In-Reply-To: <20220129023418.GA2452893@rfd.leadboat.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox