Building Indexes Concurrently
来源:互联网 发布:怎么在淘宝买日本片 编辑:程序博客网 时间:2024/05/22 12:52
/* * 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
- Building Indexes Concurrently
- indexes
- indexes
- Performing Loop Iterations Concurrently
- Building indexes using HBase: mapping strings, numbers and dates onto bytes
- rocketmq cluster下concurrently重试机制实现
- Database Indexes
- Partitioned Indexes
- 11 Indexes
- Api development and mobile end development start concurrently
- Finding duplicate indexes
- reorganizing regenerated indexes
- Detecting duplicate Oracle indexes
- identifing unused indexes
- 什么是“Using Covering Indexes”
- 禁止 Myeclipse updating indexes
- SQL Trigger,indexes,Procedure
- SQL Server2008 Filtered Indexes
- 让你终身受益的小故事
- 仿android桌面左右滑动
- HOWTO: xorg/x11 without hald/dbus and with CTRL-ALT-BACKSPACE working
- 变量 转发
- 谁欠谁的幸福(高考满分作文)
- Building Indexes Concurrently
- activity-alias的使用
- btree
- java NIO处理大对象传输的例子
- query graph
- index
- JSON和JAVA的POJO的相互转换
- dictionary
- JS来判断文本框内容改变事件