CREATE INDEX CONCURRENTLY and HOT - Mailing list pgsql-hackers

<br />Sorry to start another thread while we are still discussing CREATE <br />INDEX design, but I need
help/suggestionsto finish the patch on<br />time for 8.3<br /><br />We earlier thought that CREATE INDEX CONCURRENTLY
(CIC)<br/> would be simpler to do because of the  existing waits in CIC.<br />But one major problem with CIC is that
UPDATEsare allowed<br />while we are building the index and these UPDATEs can create<br />HOT-chains which has
differentvalues for attributes on which <br />we are building the new index. To keep the HOT-chain semantic<br
/>consistentacross old and new indexes, we might be forced to<br />delete the old index entry and reinsert new one
duringthe<br />validate_index() phase. This is of course not easy. <br /><br clear="all" />May I propose the following
designwhich is less intrusive:<br /><br />We do CIC in three phases:<br /><br />In the first phase we just create the
catalogentry for the new<br />index, mark the index read-only and commit the transaction. <br />By read-only, I mean
thatthe index is not ready inserts, but<br />is consulted during UPDATEs to decide whether to do HOT<br />UPDATE or not
(justlike other existing indexes). We then<br />wait for all transactions conflicting on ShareLock to complete. <br
/>Thatwould guarantee that all the existing transactions which<br />can not see the new index catalog entry are
finished.<br/><br />A new transaction is started. We then build the index just the<br />way we do today. While we are
buildingthe index, no new <br />HOT-chains are be created where the index keys do not<br />match because the new index
isconsulted while deciding<br />whether to do HOT UPDATE or not.<br /><br />At the end of this step, the index is
markedready for<br />inserts, we once again wait for all transactions conflicting <br />on ShareLock to finish and
committhe transaction.<br /><br />In the third phase, we validate the index inserting any<br />missing index entries
fortuples which are not HEAP_ONLY.<br />For HEAP_ONLY tuples we already have the index entry <br />though it points to
theroot tuple. Thats OK because we<br />guarantee that all tuples in the chain share the same key<br />with respect to
oldas well as new indexes.<br /><br />We then mark the index "valid" and commit. <br /><br />In summary, this design
introducesone more transaction<br />and wait. But that should not be a problem because we<br />would anyways wait for
thosetransactions to finish today<br />though a bit later in the process.<br /><br />Comments/suggestions ?<br /><br
/>Thanks,<br/>Pavan<br /><br />-- <br /><br />EnterpriseDB     <a
href="http://www.enterprisedb.com">http://www.enterprisedb.com</a>