Re: CREATE INDEX and HOT - revised design

On Thu, 2007-03-29 at 22:08 +0530, Pavan Deolasee wrote:
>
>
> On 3/29/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> It will replan at the first use of the plan after seeing the
> relcache
> inval sent by commit of the index-creating transaction. If
> you have
> two separate transactions to create an index and then mark it
> valid
> later, everything's fine because there are two inval events.
> However, if you design something where an index becomes usable
> due
> to the passage of time rather than an explicit mark-valid
> step,
> there's gonna be a problem. I'd suggest trying to stick to
> the
> way CREATE INDEX CONCURRENTLY does it...
>
>
> I had earlier proposed to do things CIC way. But there were objections
> to the additional wait introduced in CREATE INDEX, and I don't
> think they were unreasonable. May be if we can avoid waits if there
> are no HOT-chains in the table, but still we need agreement on that.
>
> OTOH ISTM that the pg_index:xcreate solution may work fine if
> we can keep index unusable to those transactions which started
> before CREATE INDEX could commit.

Pavan, ISTM you have misunderstood Tom slightly.

Having the index invisible to all current transactions is acceptable.

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.

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.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com