Re: CREATE INDEX CONCURRENTLY?

This just hit us today... Admittedly on an old cluster still running 9.2,
though I can't see any mention of it being addressed since.

Any chance of getting this on to to-do list?
On Sat, 1 Nov 2014 at 07:45, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:

> 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
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>