On 4/6/22 17:03, Peter Geoghegan wrote:
On Wed, Apr 6, 2022 at 7:49 AM Frédéric Yhuel <frederic.yh...@dalibo.com> wrote:
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?
I've always thought that the docs for REINDEX, while technically
accurate, are very misleading in practice.
Maybe something along this line? (patch attached)
From 4930bb8de182b78228d215bce1ab65263baabde7 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Fr=C3=A9d=C3=A9ric=20Yhuel?= <frederic.yh...@dalibo.com>
Date: Thu, 7 Apr 2022 13:30:59 +0200
Subject: [PATCH] Doc: Elaborate locking considerations for REINDEX
---
doc/src/sgml/ref/reindex.sgml | 6 +++++-
1 file changed, 5 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml
index e6b25ee670..06c223d4a3 100644
--- a/doc/src/sgml/ref/reindex.sgml
+++ b/doc/src/sgml/ref/reindex.sgml
@@ -275,7 +275,11 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
considerations are rather different. <command>REINDEX</command> locks out writes
but not reads of the index's parent table. It also takes an
<literal>ACCESS EXCLUSIVE</literal> lock on the specific index being processed,
- which will block reads that attempt to use that index. In contrast,
+ which will block reads that attempt to use that index. In particular,
+ the PostgreSQL query planner wants to take an <literal>ACCESS SHARE</literal>
+ lock on every indexes of the table, regardless of the query, and so
+ <command>REINDEX</command> blocks virtually any queries but some prepared queries
+ whose plan have been cached and which don't use this very index. In contrast,
<command>DROP INDEX</command> momentarily takes an
<literal>ACCESS EXCLUSIVE</literal> lock on the parent table, blocking both
writes and reads. The subsequent <command>CREATE INDEX</command> locks out
--
2.30.2
