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
