Re: CREATE INDEX and HOT - revised design

Simon Riggs wrote:
> 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.
>
I have seen and used transactions that create indexes inside a
transaction, use them for the life of the transaction, and then drop
them at the end.
I think this is an acceptable solution to not be able to use the index
in the current transaction if the table you are building the index on is
HOT enabled. That way it is not really a functionality loss, it's just
a restriction put in place if you are using a certain feature. We do
not want to go breaking existing code.

However HOT is enabled by default on tables, then we have a different
situation. And if the expectation is that HOT will be enabled by
default in future releases, then this needs to be considered now.

Regards

Russell Smith