Re: HOT documentation README - Mailing list pgsql-patches

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.