Skip site navigation (1) Skip section navigation (2)

Re: Support for REINDEX CONCURRENTLY

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
Cc: Andres Freund <andres(at)2ndquadrant(dot)com>, Greg Stark <stark(at)mit(dot)edu>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Support for REINDEX CONCURRENTLY
Date: 2012-10-05 20:03:29
Message-ID: 14757.1349467409@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Michael Paquier <michael(dot)paquier(at)gmail(dot)com> writes:
> OK, so the problem here is that the relcache, as the syscache, are relying
> on SnapshotNow which cannot be used safely as the false index definition
> could be read by other backends.

That's one problem.  It's definitely not the only one, if we're trying
to change an index's definition while an index-accessing operation is in
progress.

> I assume that the switch phase is not the longest phase of the concurrent
> operation, as you also need to build and validate the new index at prior
> steps. I am just wondering if it is acceptable to you guys to take a
> stronger lock only during this switch phase.

We might be forced to fall back on such a solution, but it's pretty
undesirable.  Even though the exclusive lock would only need to be held
for a short time, it can create a big hiccup in processing.  The key
reason is that once the ex-lock request is queued, it blocks ordinary
operations coming in behind it.  So effectively it's stopping operations
not just for the length of time the lock is *held*, but for the length
of time it's *awaited*, which could be quite long.

Note that allowing subsequent requests to jump the queue would not be a
good fix for this; if you do that, it's likely the ex-lock will never be
granted, at least not till the next system idle time.  Which if you've
got one, you don't need a feature like this at all; you might as well
just reindex normally during your idle time.

			regards, tom lane


In response to

Responses

pgsql-hackers by date

Next:From: Alvaro HerreraDate: 2012-10-05 21:07:03
Subject: Re: Support for REINDEX CONCURRENTLY
Previous:From: Tom LaneDate: 2012-10-05 16:15:01
Subject: Re: Deparsing DDL command strings

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group