public inbox for [email protected]
help / color / mirror / Atom feedFrom: Frédéric Yhuel <[email protected]>
To: [email protected]
Subject: REINDEX blocks virtually any queries but some prepared queries.
Date: Wed, 6 Apr 2022 16:48:57 +0200
Message-ID: <[email protected]> (raw)
Hello,
From the documentation
(https://www.postgresql.org/docs/current/sql-reindex.html#id-1.9.3.162.7),
it sounds like REINDEX won't block read queries that don't need the
index. But it seems like the planner wants to take an ACCESS SHARE lock
on every indexes, regardless of the query, and so REINDEX actually
blocks any queries but some prepared queries whose plan have been cached.
I wonder if it is a bug, or if the documentation should be updated. What
do you think?
Here is a simple demo (tested with postgres 10 and master):
Session #1
===========================================================
srcpg@postgres=# CREATE TABLE flights (id INT generated always as
identity, takeoff DATE);
CREATE TABLE
srcpg@postgres=# INSERT INTO flights (takeoff) SELECT date '2022-03-01'
+ interval '1 day' * i FROM generate_series(1,1000) i;
INSERT 0 1000
srcpg@postgres=# CREATE INDEX ON flights(takeoff);
CREATE INDEX
srcpg@postgres=# BEGIN;
BEGIN
srcpg@postgres=# REINDEX INDEX flights_takeoff_idx ;
REINDEX
Session #2
===========================================================
srcpg@postgres=# SELECT pg_backend_pid();
pg_backend_pid
----------------
4114695
srcpg@postgres=# EXPLAIN SELECT id FROM flights;
--> it blocks
Session #3
===========================================================
srcpg@postgres=# SELECT locktype, relname, mode, granted FROM pg_locks
LEFT JOIN pg_class ON (oid = relation) WHERE pid = 4114695;
locktype | relname | mode | granted
------------+---------------------+-----------------+---------
virtualxid | ∅ | ExclusiveLock | t
relation | flights_takeoff_idx | AccessShareLock | f
relation | flights | AccessShareLock | t
view thread (2+ messages)
reply
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: [email protected]
Cc: [email protected], [email protected]
Subject: Re: REINDEX blocks virtually any queries but some prepared queries.
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox