Re: REINDEX blocks virtually any queries but some prepared queries.

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.
I didn't verify the behavior, but +1 to document the practical consequences.
I guess this is why someone invented REINDEX CONCURRENTLY.

> From 4930bb8de182b78228d215bce1ab65263baabde7 Mon Sep 17 00:00:00 2001
> From: =?UTF-8?q?Fr=C3=A9d=C3=A9ric=20Yhuel?= <frederic(dot)yhuel(at)dalibo(dot)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>

s/wants/tries/

> + lock on every indexes of the table, regardless of the query, and so

every index

> + <command>REINDEX</command> blocks virtually any queries but some prepared queries

any query except for

> + whose plan have been cached and which don't use this very index. In contrast,

plan has

> <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
>