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
