Re: HOT documentation README

On 9/4/07, Gregory Stark <stark(at)enterprisedb(dot)com> wrote:

>
>
> 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 latest patch (v15) posted does this for system tables. We still
error-out for non-system tables, just the way we do today.

> 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 ..."

Right.

> 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.
>
>
It would be great if I can achieve that. But we haven't been able to
find a solution that would work in all cases and yet not too complicated.
My guess is any such solution will involve breaking the existing
HOT chains.

Right now we support one of the most common use case where
a table is created, loaded with data and one or more indexes are
created - all in the same transaction. In this case, the index will
be usable in the same transaction. And may be we should be
able do something better for READ COMMITTED transactions.

Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com