Re: HOT documentation README - Mailing list pgsql-patches

"Bruce Momjian" <bruce@momjian.us> 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