Re: BUG #2830: Wrong results for prepared statements while clustering target table

"" <martin(dot)pihlak(at)gmail(dot)com> 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