Re: Online index builds

On Thu, 2006-12-07 at 18:51 -0500, Tom Lane wrote:
> Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> > After reading through the archives, it looks like Gregory Stark
> > suggested a REINDEX CONCURRENTLY, which would certainly solve the
> > awkwardness of maintenance on a primary key. I didn't see much
> > objection, maybe it's worth consideration for 8.3?
>
> That idea was bounced on the grounds that it requires a DROP INDEX to
> occur somewhere, and that can't be concurrent, and you'd surely not like
> to go through all the work of a CONCURRENTLY rebuild only to get a
> deadlock failure at the very end.
>

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?

I see it as the following logical operations:
(1) CREATE INDEX CONCURRENTLY tmp;
(2) swap the relfilenode of the old index and new index
(3) DROP INDEX tmp;

If this was all already hashed out on -hackers, you can point me to the
discussion if it's easier.

Regards,
Jeff Davis