Re: CREATE INDEX and HOT - revised design

On Thu, 2007-03-22 at 16:16 -0400, Tom Lane wrote:
> "Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
> > There is a slight hole in that SERIALIZABLE transactions won't be able
> > to use any indexes they build during their transaction, since they may
> > need to be able to see prior data, but I don't think anybody is going to
> > complain about that restriction. Anyone?
>
> Practically every statement I've seen in this thread that used the
> phrase "SERIALIZABLE transaction" was wrong to some extent, and this
> one is no different.
>
> The issue is not whether the whole transaction is serializable or not,
> it's how old is the oldest still-live snapshot, a thing that CREATE
> INDEX can't tell with any certainty in READ COMMITTED mode. So if your
> solution involves any explicit dependence on the transaction
> serializability mode, it's probably wrong. I'm not totally sure if you
> are expecting to be able to tell that, but I do know that the planner
> has no idea what snapshots a plan it makes will be used with.

Thanks for correcting me.

Reworded: There is a slight hole in that snapshots older than the CREATE
INDEX must never be allowed to use the index. That means that
SERIALIZABLE transactions and some other situations will need to be
restricted. Personally, I would argue that such a restriction was an
acceptable loss of functionality, since I can't think of a situation
where such a thing would need to occur, though one may turn up.

Currently, I don't know how to prevent this from happening. We'll need
to examine this in more detail to see if there is a way.

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