CREATE INDEX CONCURRENTLY and HOT
От | Pavan Deolasee |
---|---|
Тема | CREATE INDEX CONCURRENTLY and HOT |
Дата | |
Msg-id | 2e78013d0703291022q431c57a5g2ce79710458fc69c@mail.gmail.com обсуждение исходный текст |
Список | 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>
В списке pgsql-hackers по дате отправления: