Re: Online index builds

On Tue, 2006-12-12 at 18:08 -0500, Tom Lane wrote:
> Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> > You could create a whole new index concurrently, then in a completely new
> > (third) transaction drop the old one. The problem there is that there could be
> > other things (namely foreign key constraints) depending on the old index.
> > Fixing them all to depend on the new one may not be a problem or it may, I
> > haven't thought it through. Nor have I thought through whether it would be
> > possible to keep the original name.
>
> If the idea is to do REINDEX CONCURRENTLY then ISTM you could just swap
> the relfilenodes of the two indexes and then zap the new catalog entries
> (and old index contents). The problem is exactly the same as before,
> though: you need exclusive lock to do that.
>

My point was that, because we can run it in multiple transactions, can't
we drop the nonexclusive lock before acquiring the exclusive lock,
thereby eliminating the possibility of losing the index we just made to
a deadlock?

In other words, why would the following not work:

CREATE UNIQUE INDEX CONCURRENTLY foo_pkey_tmp ON foo (id);
BEGIN;
UPDATE pg_class SET relfilenode=<relfilenode_of_foo_pkey> WHERE
relname='foo_pkey_tmp';
UPDATE pg_class SET relfilenode=<relfilenode_of_foo_pkey_tmp> WHERE
relname='foo_pkey';
COMMIT;
DROP INDEX foo_pkey_tmp;

Or is there something more sophisticated we need to do to swap the
relfilenodes?

Regards,
Jeff Davis