Re: CREATE INDEX and HOT - revised design

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. I coded a quick prototype
where I use ActiveSnapshot in get_relation_info() to test if the
CREATE INDEX transaction is seen as "in-progress" to the
transaction. If seen as in-progress, the index is not used (even
though the CREATE INDEX is committed and hence can be seen
by SnapshotNow).

If an index which was earlier seen unusable is marked as "valid" as
time progresses, could there be some trouble ? I mean, as long as
we don't make the index usable before all tuples which are not
indexed are DEAD, we should be fine.

Is there something I am missing ? Would it help to explain the idea
if I post the patch ?

CREATE INDEX and CREATE INDEX CONCURRENTLY turned
out to be much more difficult than I imagined earlier. While we are
discussing CREATE INDEX, I would post a design for CIC. I
restrained myself till now to avoid confusion, but with time running
out, it would be helpful to get agreement so that we can finish
the patch on time.

Thanks,
Pavan

--

EnterpriseDB http://www.enterprisedb.com