Re: CREATE INDEX and HOT - revised design

On 3/23/07, Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com> wrote:
>
>
>
> 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.
>
>
Just when I thought we have nailed down CREATE INDEX, I realized
that there something more to worry. The problem is with the HOT-chains
created by our own transaction which is creating the index. We thought
it will be enough to index the tuple at the head-of-the-chain since that
would be the visible copy once the transaction commits. We thought
of keeping the index unavailable for queries in pre-existing transactions
by setting a new "xid" attribute in pg_index. The question is what value
to assign to "xid". I though we would assign ReadNewTransactionId().

Now, a new transaction can start before we commit and hence have
transaction_id > xid. This transaction can still see the old tuple
(because the transaction creating the index is not yet committed)
which we did not index while creating the index. Once the transaction
creating the index commits, the index is also available to this new
transaction and we are in trouble at that point.

I think Tom had already seen this, but his comment got overlooked
in the flow discussion. If thats the case, I regret that.

Any idea how to handle this case ? One ugly hack I can think of
is to remember all those indexes created in the transaction for which
we had seen DELETE_IN_PROGRESS tuples while building
the index. At the commit time, we (somehow) stop new transactions
to start, note the next transaction id and set it in pg_index and commit
the transaction. New transactions are then enabled again.

I know it looks ugly. Is there something better that we can do ?

Thanks,
Pavan

--

EnterpriseDB http://www.enterprisedb.com