Message-ID: <65d08718-6f11-978a-4b5a-72b807d4c663@dalibo.com>
Date: Wed, 6 Apr 2022 16:48:57 +0200
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.7.0
Content-Language: en-US
From: =?UTF-8?Q?Fr=c3=a9d=c3=a9ric_Yhuel?= <frederic.yhuel@dalibo.com>
To: pgsql-hackers@lists.postgresql.org
Subject: REINDEX blocks virtually any queries but some prepared queries.
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/65d08718-6f11-978a-4b5a-72b807d4c663%40dalibo.com>
Precedence: bulk

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