pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedREINDEX locking behavior seems inconsistent with the documentation
2+ messages / 1 participants
[nested] [flat]
* REINDEX locking behavior seems inconsistent with the documentation
@ 2019-01-08 23:02 PG Doc comments form <noreply@postgresql.org>
0 siblings, 0 replies; 2+ messages in thread
From: PG Doc comments form @ 2019-01-08 23:02 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: richyen3@gmail.com
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"
I've tested this with the following 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 abalance from pgbench_accounts limit 1" gets
blocked by the REINDEX, and doesn't return any values until the REINDEX is
completed, even though the SELECT query doesn't seem to access foo_idx. I'm
not sure if this is a bug in the code or a bug in the documentation--who
gets to make that call?
^ permalink raw reply [nested|flat] 2+ messages in thread
* REINDEX locking behavior seems inconsistent with the documentation
@ 2019-01-09 04:58 PG Doc comments form <noreply@postgresql.org>
0 siblings, 0 replies; 2+ messages in thread
From: PG Doc comments form @ 2019-01-09 04:58 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: richyen3@gmail.com
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2019-01-09 04:58 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-01-08 23:02 REINDEX locking behavior seems inconsistent with the documentation PG Doc comments form <noreply@postgresql.org>
2019-01-09 04:58 REINDEX locking behavior seems inconsistent with the documentation PG Doc comments form <noreply@postgresql.org>
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