REINDEX blocks virtually any queries but some prepared queries.

Поиск
Список
Период
Сортировка
От Frédéric Yhuel
Тема REINDEX blocks virtually any queries but some prepared queries.
Дата
Msg-id 65d08718-6f11-978a-4b5a-72b807d4c663@dalibo.com
обсуждение исходный текст
Ответы Re: REINDEX blocks virtually any queries but some prepared queries.  (Peter Geoghegan <pg@bowt.ie>)
Список pgsql-hackers
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



В списке pgsql-hackers по дате отправления:

Предыдущее
От: Robert Haas
Дата:
Сообщение: Re: Practical Timing Side Channel Attacks on Memory Compression
Следующее
От: Andrew Dunstan
Дата:
Сообщение: Re: SQL/JSON: JSON_TABLE