"" <[EMAIL PROTECTED]> writes:
> Prepared SELECT/UPDATE/DELETE statements produce wrong results if executed
> while target table is being clustered.
The short answer is "don't CLUSTER while the table is in live use" ...
CLUSTER re-inserts all the rows in the table into a fresh table. This
means that all the rows appear to have been inserted by the CLUSTER
transaction, and therefore that a transaction that scans the table
afterward with a snapshot taken before the CLUSTER committed will not
see those rows.
It'd be better if CLUSTER preserved the rows' MVCC state but don't hold
your breath for that; any such change is certainly not going to get
back-patched into stable releases.
The difference between EXECUTE and SELECT behavior here is just a chance
matter of exactly where the snap is taken during the parse/execute code
path --- your SELECT works because it blocks for AccessShareLock on the
table before it sets the snap. But SELECT would fail just the same way
within a serializable transaction that had already set its snapshot.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
