Re: CREATE INDEX and HOT - revised design

On 3/28/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> "Florian G. Pflug" <fgp(at)phlo(dot)org> writes:
> > Couldn't you store the creating transaction's xid in pg_index, and
> > let other transaction check that against their snapshot like they
> > would for any tuple's xmin or xmax?
>
> What snapshot? I keep having to remind people that system catalog
> operations are SnapshotNow. In the particular context here, the
> place where doing something else would break down is that the planner
> has no idea when it makes a plan what snapshot(s) the plan might later
> be used with.
>
>
>

Tom, please correct me if I am wrong. But ISTM that this idea might
work in this context. In get_relation_info(), we would check if "xcreate"
xid stored in pg_index for the index under consideration is seen
committed with respect to the snapshot at that point of time.
Even if the snapshot changes later and index becomes valid, we
might not replan and hence not use index. But that doesn't seem
like a big problem to me.

So in get_relation_info():

We get the transaction snapshot. If its a serializable transaction,
it can't change later. If its a read-commited transaction, we anyways
don't care because the transaction can only see the last committed
version of the tuple in the table and we have indexed that. And so
we are safe.

- if xcreate < snapshot->xmin, index is valid
- if xcreate > snapshot->xmax, index is invalid
- if snapshot->xmax > xcreate > snapshot->xmin, we check xids in the
snapshot
to determine validity of the index.

I might have got something wrong here, but I hope I made the point.

Am I missing something here ?

Thanks,
Pavan

--

EnterpriseDB http://www.enterprisedb.com