Re: Online index builds

On Thu, 2006-12-07 at 19:44 -0500, Tom Lane wrote:
> Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> > I don't understand. CREATE INDEX CONCURRENTLY can't be run in a
> > transaction block anyway, so a REINDEX CONCURRENTLY wouldn't either. So
> > how (or when) would you deadlock?
>
> The problem is you need to upgrade from a nonexclusive table lock to an
> exclusive one before you could drop the old index. If someone else
> is waiting to get a conflicting lock, boom ...
>

I think what I'm confused about is how these non-transactional commands
work (like VACUUM, etc). Are they still transactions, and just can't be
run in a block?

My original thinking was that the shared lock could be unlocked before
the exclusive lock is taken to switch the relfilenodes and to drop the
index. However, if it is a real transaction, clearly you can't unlock in
the middle.

Is it safe to manually run the sequence I previously suggested? If so it
seems like there could be a command to do it properly. I tried it and it
appeared to work.

Regards,
Jeff Davis