Re: Bugs in CREATE/DROP INDEX CONCURRENTLY

On 2012-11-26 22:44:49 -0500, Tom Lane wrote:
> Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> > On 2012-10-05 19:56:40 -0400, Tom Lane wrote:
> >> I think this could possibly be fixed by using nontransactional
> >> update-in-place when we're trying to change indisvalid and/or
> >> indisready, but I've not really thought through the details.
>
> > I couldn't really think of any realistic method to fix this other than
> > update in place. I thought about it for a while and I think it should
> > work, but I have to say it makes me slightly uneasy.
>
> I looked through the code a bit, and I think we should be able to make
> this work, but note there are also places that update indcheckxmin using
> heap_update, and that's just as dangerous as changing the other two
> flags via heap_update, if you don't have exclusive lock on the table.
> This is going to need some careful thought, because we currently expect
> that such an update will set the pg_index row's xmin to the current XID,
> which is something an in-place update can *not* do. I think this is a
> non-problem during construction of a new index, since the xmin ought to
> be the current XID already anyway, but it's less clear what to do in
> REINDEX. In the short term there may be no problem since REINDEX takes
> exclusive lock on the parent table anyway (and hence could safely do
> a transactional update) but if we ever want REINDEX CONCURRENTLY we'll
> need a better answer.

Isn't setting indexcheckxmin already problematic in the case of CREATE
INDEX CONCURRENTLY? index_build already runs in a separate transaction
there.

I am really surprised that we haven't seen any evidence of a problem
with this. On a database with a busy & bigger catalog that ought to be
a real problem.

I wonder whether we couldn't fix it by introducing a variant/wrapper of
heap_fetch et al. that follows t_ctid if the tuple became invisible
"recently". That ought to be able to fix most of these issues in a
pretty general fashion.
That would make a nice implementation of REINDEX CONCURRENTLY easier as
well...

Btw, even if we manage to find a sensible fix for this I would suggest
postponing it after the next back branch release.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services