Re: Online index builds

Jeff Davis <pgsql(at)j-davis(dot)com> writes:

> 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?

In the case of CREATE INDEX CONCURRENTLY it can't be run in a transaction
block because it itself consists of two transactions. First it builds an
index, then it has to commit that and start a second transaction that
completes the index.

> 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.

Well you can't play games with the relfilenode if it's concurrent or else
other transactions executing inserts and updates won't be updating your new
index.

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.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com