Csaba Nagy <nagy@ecircle-ag.com> writes:
> > > I think you build a whole new index named something like ".temp-reindex" and
> > > then as the last step of the second transaction delete the old idnex and
> > > rename the new index.
> >
> > That would require getting exclusive lock on the table.
>
> Just out of curiosity, creating a new index concurrently (or online,
> whatever you call it) doesn't require to set an exclusive lock on the
> table ? I thought it would, at least swiftly at the end of the
> operation, after all it's modifying the table...
Nope.
As I understand it the step that fundamentally requires a table lock is
actually dropping the old index. You have to be sure nobody is actually using
it before you do anything that causes people to stop maintaining it.
We could do something like how the online index build creates the index but in
reverse. We mark the index invalid and then wait out any transactions that
could be using it. Then we can drop it safely.
But I think even that has some practical problems. Transactions that have that
index in their relcache structure for the table will try to maintain it and
get confused if it's gone.
It seems to me that taking a brief lock on the table at the end of the reindex
isn't actually much of a problem. It only needs to be held briefly and it can
be done in a separate transaction so there isn't a deadlock risk.
--
greg