Re: Online index builds

On Wed, 2006-12-13 at 17:12, Tom Lane wrote:
> Csaba Nagy <nagy(at)ecircle-ag(dot)com> writes:
> > So an implementation which optimistically builds the new index
> > concurrently while holding no lock, and then hopes for the 3rd
> > transaction to be able to get the exclusive lock and be able to swap the
> > new index in the place of the old index, and error out if it can't - it
> > is perfectly acceptable.
>
> It would maybe be acceptable if there were a way to clean up the mess
> after a failure, but there wouldn't be ...

With the "mess" you refer to the new index, and the fact it is
impossible to delete it if not possible to replace the old one ? I fail
to see why... you WILL get an exclusive lock, so you should be able to
delete the index. The deadlock is not an issue if you release first the
shared locks you hold...

If "mess" means that it's impossible to tell that you can or can't
safely replace the index, then that's a problem, but I think the
scenarios you thought out and would break things are detectable, right ?
Then you: take the exclusive lock, check if you can still safely replace
the index, do it if yes, delete the new index otherwise or on failure to
swap (to cover unexpected cases). If you can't delete the new index
cause somebody changed it in the meantime (that must be a really strange
corner case), then bad luck, nobody is supposed to do that...

While I'm not familiar enough with how postgres handles locking,
wouldn't be also possible for DDLs to first also acquire a lock which
would only lock other DDLs and not DMLs ? In that case you could get
that lock first and hold it through the second phase, and make the
second phase also swap the indexes after also acquiring the full
exclusive lock. That could potentially still deadlock, but the chance to
do so would be a lot smaller.

I think the above is not clear enough... what I mean is to make all DDLs
get 2 locks:

- first an "DDL exclusive" lock which blocks other DDLs from getting
the same;
- second a full exclusive lock which blocks any other locks;

Between the 2 there could go some operation which is not blocking normal
operation but needs protection from other concurrent DDL. If only DDLs
do this and always in this order, there's no deadlock potential.
Disadvantage is the very need to place one more lock...

Cheers,
Csaba.