On 4/7/22 14:40, Justin Pryzby wrote:
On Thu, Apr 07, 2022 at 01:37:57PM +0200, Frédéric Yhuel wrote:
Maybe something along this line? (patch attached)
Some language fixes.
Thank you Justin! I applied your fixes in the v2 patch (attached).
I didn't verify the behavior, but +1 to document the practical consequences.
I guess this is why someone invented REINDEX CONCURRENTLY.
Indeed ;) That being said, REINDEX CONCURRENTLY could give you an
invalid index, so sometimes you may be tempted to go for a simpler
REINDEX, especially if you believe that the SELECTs won't be blocked.From 0b6c7d6e466fabc0233b6960b8a33141d512652f 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 | 7 ++++++-
1 file changed, 6 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml
index e6b25ee670..d3c63c4deb 100644
--- a/doc/src/sgml/ref/reindex.sgml
+++ b/doc/src/sgml/ref/reindex.sgml
@@ -275,7 +275,12 @@ 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 tries to take an <literal>ACCESS SHARE</literal>
+ lock on every index of the table, regardless of the query, and so
+ <command>REINDEX</command> blocks virtually any queries except for some prepared
+ queries whose plan has 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
