Re: CREATE INDEX CONCURRENTLY?

On 31 October 2014 17:46, Michael Banck <michael(dot)banck(at)credativ(dot)de> wrote:

> I wonder whether that is pilot error (fair enough), or whether something
> could be done about this?

When originally written the constraints were tighter, but have since
been relaxed.

Even so a CIC waits until all snapshots that can see it have gone. So
what you observe is correct and known.

Can it be changed? Maybe.

CREATE INDEX gets around the wait by using indcheckxmin to see whether
the row is usable. So the command completes, even if the index is not
usable by all current sessions.

We perform the wait in a completely different way for CIC, for this
reason (in comments)

We also need not set indcheckxmin during a concurrent index build,
because we won't set indisvalid true until all transactions that care
about the broken HOT chains are gone.

Reading that again, I can't see why we do it that way. If CREATE INDEX
can exit once the index is built, so could CONCURRENTLY.

ISTM that we could indcheckxmin into an Xid, not a boolean
For CREATE INDEX, set the indcheckxmin = xid of creating transaction
For CREATE INDEX CONCURRENTLY set the indcheckxmin = xid of the
completing transaction

The apparent reason it does this is that the Xmin value used currently
is the Xmin of the index row. The index row is inserted prior to the
index being valid so that technique cannot work. So I am suggesting
for CIC that we use the xid of the transaction that completes the
index, not the xid that originally created the index row. Plus handle
the difference between valid and not.

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