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
addressedsince.<br /><br />Any chance of getting this on to to-do list?<br /><div class="gmail_quote"><div dir="ltr">On
Sat,1 Nov 2014 at 07:45, Simon Riggs <<a href="mailto:simon@2ndquadrant.com">simon@2ndquadrant.com</a>> wrote:<br
/></div><blockquoteclass="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">On 31
October2014 17:46, Michael Banck <<a href="mailto:michael.banck@credativ.de"
target="_blank">michael.banck@credativ.de</a>>wrote:<br /><br /> > I wonder whether that is pilot error (fair
enough),or whether something<br /> > could be done about this?<br /><br /> When originally written the constraints
weretighter, but have since<br /> been relaxed.<br /><br /> Even so a CIC waits until all snapshots that can see it
havegone. So<br /> what you observe is correct and known.<br /><br /><br /> Can it be changed? Maybe.<br /><br />
CREATEINDEX gets around the wait by using indcheckxmin to see whether<br /> the row is usable. So the command
completes,even if the index is not<br /> usable by all current sessions.<br /><br /> We perform the wait in a
completelydifferent way for CIC, for this<br /> reason (in comments)<br /><br />   We also need not set indcheckxmin
duringa concurrent index build,<br />   because we won't set indisvalid true until all transactions that care<br />  
aboutthe broken HOT chains are gone.<br /><br /> Reading that again, I can't see why we do it that way. If CREATE
INDEX<br/> can exit once the index is built, so could CONCURRENTLY.<br /><br /> ISTM that we could indcheckxmin into an
Xid,not a boolean<br />    For CREATE INDEX, set the indcheckxmin = xid of creating transaction<br />    For CREATE
INDEXCONCURRENTLY set the indcheckxmin = xid of the<br /> completing transaction<br /><br /> The apparent reason it
doesthis is that the Xmin value used currently<br /> is the Xmin of the index row. The index row is inserted prior to
the<br/> index being valid so that technique cannot work. So I am suggesting<br /> for CIC that we use the xid of the
transactionthat completes the<br /> index, not the xid that originally created the index row. Plus handle<br /> the
differencebetween valid and not.<br /><br /> --<br />  Simon Riggs                   <a
href="http://www.2ndQuadrant.com/"rel="noreferrer" target="_blank">http://www.2ndQuadrant.com/</a><br />  PostgreSQL
Development,24x7 Support, Training & Services<br /><br /><br /> --<br /> Sent via pgsql-hackers mailing list (<a
href="mailto:pgsql-hackers@postgresql.org"target="_blank">pgsql-hackers@postgresql.org</a>)<br /> To make changes to
yoursubscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-hackers" rel="noreferrer"
target="_blank">http://www.postgresql.org/mailpref/pgsql-hackers</a><br/></blockquote></div>