Building Indexes Concurrently

/*
 * We do a concurrent index build by first inserting the catalog entry for the
 * index via index_create(),marking it not indisready and not indisvalid.
 * Then we commit our transaction and start a new one,then we wait for all
 * transactions that could have been modifying the table to terminate.	Now
 * we know that any subsequently-started transactions will see the index and
 * honor its constraints on HOT updates; so while existing HOT-chains might
 * be broken with respect to the index,no currently live tuple will have an
 * incompatible HOT update done to it.	We now build the index normally via
 * index_build(),while holding a weak lock that allows concurrent
 * insert/update/delete.  Also,we index only tuples that are valid
 * as of the start of the scan (see IndexBuildHeapScan),whereas a normal
 * build takes care to include recently-dead tuples.  This is OK because
 * we won't mark the index valid until all transactions that might be able
 * to see those tuples are gone.  The reason for doing that is to avoid
 * bogus unique-index failures due to concurrent UPDATEs (we might see
 * different versions of the same row as being valid when we pass over them,* if we used HeapTupleSatisfiesVacuum).  This leaves us with an index that
 * does not contain any tuples added to the table while we built the index.



 
 * For a concurrent build,it's important to make the catalog entries
 * visible to other transactions before we start to build the index. That
 * will prevent them from making incompatible HOT updates.The new index
 * will be marked not indisready and not indisvalid,so that no one else
 * tries to either insert into it or use it for queries.
 
 */

普通的create index是当建index时，不充许对表进行更改，postgresql支持的Concurrent创建index，是充分在建索引时同时对表进行更改。

共用了三个事务。

第一个事务，只是在系统表中增加索引定义，不建索引，索引不能insert,也不能select

第二个事务，创建索引，创建过程中充许对表insert/update/delete，提交时，索引可以insert，不能select.

第三个事务，把第二个事务过程中，没加入索引中的，合并进索引。

http://www.postgresql.org/docs/9.1/static/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!