Re: CREATE INDEX and HOT - revised design

On 3/23/07, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>
>
>
> The ShareLock taken by CREATE INDEX guarantees all transactions that
> wrote data to the table have completed and that no new data can be added
> until after the index build commits. So the end of the chain is visible
> to CREATE INDEX and won't change. As long as you index the latest
> committed version on each HOT chain, then I think it works.

Its slightly different for the HOT-chains created by this transaction which
is creating the index. We should index the latest version of the row which
is not yet committed. But thats ok because when CREATE INDEX commits
this latest version would also get committed.

Sounds like you'll need to store the Next TransactionId rather than the
> TransactionId of the CREATE INDEX.

Yes, I agree.

There is a slight hole in that SERIALIZABLE transactions won't be able
> to use any indexes they build during their transaction, since they may
> need to be able to see prior data, but I don't think anybody is going to
> complain about that restriction. Anyone?

Oh, I did not see that. If there are no HOT-chains in the table, we can
set the xid to InvalidTransactionId so that the index is usable immediately
after it is created in the current transaction, as well as those
transactions
which were started before CREATE INDEX. We can possibly further
improve it by checking if there are no HOT-chains except those created
by this transaction and set xid to InvalidTransactionId. IMO with that we
shall address most of the use cases. There are few which might
still get impacted, but even for them there won't be any correctness
problem.

Thanks,
Pavan

--

EnterpriseDB http://www.enterprisedb.com