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.
|
Список | 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 по дате отправления: