Building Indexes Concurrently

前端之家收集整理的这篇文章主要介绍了Building Indexes Concurrently前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
  1. /*
  2. * We do a concurrent index build by first inserting the catalog entry for the
  3. * index via index_create(),marking it not indisready and not indisvalid.
  4. * Then we commit our transaction and start a new one,then we wait for all
  5. * transactions that could have been modifying the table to terminate. Now
  6. * we know that any subsequently-started transactions will see the index and
  7. * honor its constraints on HOT updates; so while existing HOT-chains might
  8. * be broken with respect to the index,no currently live tuple will have an
  9. * incompatible HOT update done to it. We now build the index normally via
  10. * index_build(),while holding a weak lock that allows concurrent
  11. * insert/update/delete. Also,we index only tuples that are valid
  12. * as of the start of the scan (see IndexBuildHeapScan),whereas a normal
  13. * build takes care to include recently-dead tuples. This is OK because
  14. * we won't mark the index valid until all transactions that might be able
  15. * to see those tuples are gone. The reason for doing that is to avoid
  16. * bogus unique-index failures due to concurrent UPDATEs (we might see
  17. * 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
  18. * does not contain any tuples added to the table while we built the index.
  19.  
  20.  
  21.  
  22. * For a concurrent build,it's important to make the catalog entries
  23. * visible to other transactions before we start to build the index. That
  24. * will prevent them from making incompatible HOT updates.The new index
  25. * will be marked not indisready and not indisvalid,so that no one else
  26. * tries to either insert into it or use it for queries.
  27. */
  1.  


普通的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

猜你在找的Postgre SQL相关文章