Re: REINDEX CONCURRENTLY 2.0

On 11/13/14, 3:50 PM, Andres Freund wrote:
> On November 13, 2014 10:23:41 PM CET, Peter Eisentraut <peter_e@gmx.net> wrote:
>> On 11/12/14 7:31 PM, Andres Freund wrote:
>>> Yes, it sucks. But it beats not being able to reindex a relation with
>> a
>>> primary key (referenced by a fkey) without waiting several hours by a
>>> couple magnitudes. And that's the current situation.
>>
>> That's fine, but we have, for better or worse, defined CONCURRENTLY :=
>> does not take exclusive locks.  Use a different adverb for an
>> in-between
>> facility.
>
> I think that's not actually a service to our users. They'll have to adapt their scripts and knowledge when we get
aroundto the more concurrent version. What exactly CONCURRENTLY means is already not strictly defined and differs
betweenthe actions.
 

It also means that if we ever found a way to get rid of the exclusive lock we'd then have an inconsistency anyway. Or
we'dalso create REINDEX CONCURRENT at that time, and then have 2 command syntaxes to support.
 

> I'll note that DROP INDEX CONCURRENTLY actually already  internally acquires an AEL lock. Although it's a bit harder
tosee the consequences of that.
 

Having been responsible for a site where downtime was a 6 figure dollar amount per hour, I've spent a LOT of time
worryingabout lock problems. The really big issue here isn't grabbing an exclusive lock; it's grabbing one at some
randomtime when no one is there to actively monitor what's happening. (If you can't handle *any* exclusive locks, that
alsomeans you can never do an ALTER TABLE ADD COLUMN either.) With that in mind, would it be possible to set this up so
thatthe time-consuming process of building the new index file happens first, and then (optionally) some sort of DBA
actionis required to actually do the relfilenode swap? I realize that's not the most elegant solution, but it's WAY
betterthan this feature not hitting 9.5 and people having to hand-code a solution.
 

Possible syntax:
REINDEX CONCURRENTLY -- Does what current patch does
REINDEX CONCURRENT BUILD -- Builds new files
REINDEX CONCURRENT SWAP -- Swaps new files in

This suffers from the syntax problems I mentioned above, but at least this way it's all limited to one command, and it
probablyallows a lot more people to use it.
 
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com