From: PG Doc comments form <noreply@postgresql.org>
To: pgsql-docs@lists.postgresql.org
Cc: richyen3@gmail.com
Subject: REINDEX locking behavior seems inconsistent with the documentation
Date: Wed, 09 Jan 2019 04:58:30 +0000
Message-ID: <154700991083.22747.12622776286489231337@wrigleys.postgresql.org> (raw)
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/11/sql-reindex.html
Description:
Paragraph in question:
REINDEX is similar to a drop and recreate of the index in that the index
contents are rebuilt from scratch. However, the locking considerations are
rather different. REINDEX locks out writes but not reads of the index's
parent table. It also takes an exclusive lock on the specific index being
processed, which will block reads that attempt to use that index. In
contrast, DROP INDEX momentarily takes an exclusive lock on the parent
table, blocking both writes and reads. The subsequent CREATE INDEX locks out
writes but not reads; since the index is not there, no read will attempt to
use it, meaning that there will be no blocking but reads might be forced
into expensive sequential scans.
"REINDEX locks out writes but not reads of the index's parent table" -- It
seems that REINDEX locks out both reads and writes on the parent table.
"also takes an exclusive lock on the specific index being processed" -- The
SELECT that I provided performs a seqscan and doesn't use "the specific
index being processed"
My test case:
> pgbench -i -s 100
> psql -c "create index foo_idx on pgbench_accounts (bid)"
> psql -c "reindex index foo_idx" & psql -c "select abalance from
pgbench_accounts limit 1" & psql -c "select * from pg_stat_activity where
state notnull"; psql -c "select * from pg_locks";
It seems that the SELECT is blocked and doesn't finish until the REINDEX is
done, even though the SELECT doesn't touch any of the columns indexed by
foo_idx. Am I understanding the documentation wrong? If there's indeed an
inconsistency between the documentation and actual behavior, who should I
talk to re. getting something patched (i.e., code fix or doc fix)?
Thanks,
--Richard
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-docs@postgresql.org
Cc: noreply@postgresql.org, pgsql-docs@lists.postgresql.org, richyen3@gmail.com
Subject: Re: REINDEX locking behavior seems inconsistent with the documentation
In-Reply-To: <154700991083.22747.12622776286489231337@wrigleys.postgresql.org>
* 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