"Bruce Momjian" <[EMAIL PROTECTED]> writes: > I have taken this, and Pavan's documentation about CREATE INDEX, and > worked up an updated README. Comments? Corrections?
Oh, you I think the CREATE INDEX documentation you refer to was actually the one I suggested. A few tweaks: > (If we find any HOT-updated tuples with RECENTLY_DEAD or > DELETE_IN_PROGRESS we ignore it assuming that we will also come across > the _end_ of the update chain and index that instead.) There's more to this. We build a mapping telling us the Root tuple for each tuple in the page. Then when we scan tuples looking for the Head of each HOT chain (ie, a tuple that wasn't HOT updated) and index the corresponding Root from the map using the key value from the Head tuple. We treat DELETE_IN_PROGRESS the same way we treat RECENTLY_DEAD (and INSERT_IN_PROGRESS the same as LIVE) because we assume it's been deleted (or inserted) by our own transaction. So while it's not actually committed yet we can assume it is since if its transaction aborts the index creation itself will be aborted. Other transactions cannot be deleting or inserting tuples without having committed or aborted already because we have a lock on the table and the other transactions normally keep their locks until they exit. NOTE: This is something likely to change. Current discussions are leading towards handling DELETE_IN_PROGRESS and INSERT_IN_PROGRESS from other transactions. We would do this by waiting until the transactions owning those tuples exit. This would allow us to index tables being used by transactions which release their locks early to work. In particular this happens for system tables. > The tricky case arises with queries executed in the same transaction as > CREATE INDEX. In the case of a new table created within the same > transaction (such as with pg_dump), the index will be usable because > there will never be any HOT update chains so the indcreatexid will never > be set. This is unclear and perhaps misleading. I think it needs to be more like "In the case of a new table in which rows were inserted but none updated (such as with pg_dump) the index will be usable because ..." > Also in the case of a read-committed transaction new queries will be able to > use the index. A serializable transaction building an index on an existing > table with HOT updates cannot not use the index. I don't think this is clear and I'm not sure it's right. Currently the transaction that actually did the CREATE INDEX has to follow the same rules as other transactions. This means if there were any visible hot updated tuples and the index is therefore marked with our xid in indcreatexid we will *not* be able to use it in the same transaction as our xid is never in our serializable snapshot. This is true even if we're not in serializable mode as we cannot know what earlier snapshots are still in use and may be used with the new plan. NOTE: This again is something likely to change. In many cases it ought to be possible to have the transaction use the index it just built even if there were visible HOT updated tuples in it. In particular in READ COMMITTED transactions which have no outstanding commands using early snapshots then subsequent planning ought to be able to use the index. Even if outstanding commands are using old snapshots if we can be sure they can't use the new plan then it would still be safe to use the index in the new plan. Also in SERIALIZABLE mode those same statements hold for temporary tables. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster