From: Vik Fearing <vik@2ndquadrant.fr>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: Satoshi Nagayasu <snaga@uptime.jp>
Cc: pgsql-docs@postgresql.org
Subject: Re: REINDEX and blocking SELECT queries
Date: Fri, 9 Sep 2016 15:52:20 +0200
Message-ID: <ecf2cf36-9fa9-e08c-e1f1-c603ae90426b@2ndquadrant.fr> (raw)
In-Reply-To: <19413.1473428496@sss.pgh.pa.us>
References: <CAA8sozcK9eOo0Cj0ufBpAYi=oL2cJ-T9T7JN-_CqpYSWZ0GDcg@mail.gmail.com>
<19413.1473428496@sss.pgh.pa.us>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-docs>
On 09/09/2016 03:41 PM, Tom Lane wrote:
> Satoshi Nagayasu <snaga@uptime.jp> writes:
>> According to the manual, running REINDEX does not take any locks
>> on the parent table which block read operations.
>> Actually, REINDEX blocks SELECT queries, maybe in the planning phase.
>
> Hm. REINDEX does take out only ShareLock on the table, which would not
> block DML, but it takes out AccessExclusiveLock on the index. That
> blocks the planner's attempts to acquire information about the table's
> indexes.
>
> In the case of an update query I think there's little we can do about
> this; the executor would have to update the index anyway. For a pure
> SELECT, you could imagine having the planner do a conditional lock acquire
> and ignore the index if that fails. Would that be better than blocking?
> Not sure. You could end up with a really bad plan if the index was
> critical for efficient processing of the query.
I agree, things could get awful without certain indexes (I'm thinking of
partial indexes in particular).
Also, the very next sentence after the one cited says that a SELECT will
block if it tries to use the index in question, so I'm not even sure
there's anything at all to do here.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: vik@2ndquadrant.fr, tgl@sss.pgh.pa.us, snaga@uptime.jp
Subject: Re: REINDEX and blocking SELECT queries
In-Reply-To: <ecf2cf36-9fa9-e08c-e1f1-c603ae90426b@2ndquadrant.fr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox