The following bug has been logged online:

Bug reference: 2830
Logged by:
Email address: martin.pihlak (AT) gmail (DOT) com
PostgreSQL version: 8.2.0, older
Operating system: Ubuntu 6.10 2.6.17-10-386, libc6 2.4-1ubuntu12
Description: Wrong results for prepared statements while clustering
target table
Details:

Prepared SELECT/UPDATE/DELETE statements produce wrong results if executed
while target table is being clustered. Only seems to happens when CLUSTER
and statement are executed in different sessions. Tested on 8.0.6, 8.0.8,
8.1.4 and 8.2.0, reproducible with the following test case:

create table test(i integer);
-- generate large enough table, so that clustering takes more than 1 sec
insert into test select * from generate_series(1,1000000);
create index test_idx on test(i);

prepare c as select count(*) from test;

\! psql -c "cluster test_idx on test" &
-- sleep to make sure we stay behind cluster
\! sleep 1

-- should produce same values
execute c;
select count(1) from test;

Executing 'c' after CLUSTER returns 0 instead of 1000000.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

"" <martin.pihlak (AT) gmail (DOT) com> writes:
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

environment. And
unfortunately there aren't any good alternatives either.

Ok, makes sense. The same reasoning probably applies to INSERT and UPDATE as
well. Still the problem remains - how to cluster a table on a busy
system without losing
data or getting wrong results. Perhaps the issue should be documented,
although a fix
would be preferrable

Martin

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq