Simon Riggs wrote:
On Thu, 2007-03-22 at 16:16 -0400, Tom Lane wrote:
"Simon Riggs" <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
