Re: CREATE INDEX and HOT - revised design

On 3/30/07, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>
>
> Pavan, ISTM you have misunderstood Tom slightly.

Oh, yes. Now that I re-read Tom's comment, his plan invalidation
design and code, I understand things better.

> Having the index invisible to all current transactions is acceptable.

Ok.

> However, the other backends will not receive an invalidation event,
> which means even when they start new transactions they will still not
> see it, which is not acceptable.

Agree.

ISTM that the run-another-transaction-afterwards idea is the only one
> that does everything I think we need. I really do wish we could put in a
> wait, like CIC, but I just think it will break existing programs.

ISTM that the run-another-transaction-afterwards idea would have same
problem with plan invalidation. When the second transaction commits,
the relcache invalidation event is generated. The event may get consumed
by other backends, but the index may still not be usable to them because
their xid < xcreat. If no more relcache invalidation events are generated
after that, the backends would continue to use the cached plan, even
if index becomes usable to them later.

How about storing the snapshot which we used during planning in
CachedPlanSource, if at least one index was seen unusable because
its CREATE INDEX transaction was seen as in-progress ? In
RevalidateCachedPlan(), we check if snapshot is set in
CachedPlanSource and check if we are now using a different snapshot.
This triggers plan invalidation and re-planning. This would also help us
to use index early in read-committed transactions even if the transaction
was started before CREATE INDEX committed.

Does this sound good ?

Thanks,
Pavan

--

EnterpriseDB http://www.enterprisedb.com