>>> On Sat, Dec 16, 2006 at 5:15 PM, in message
<8738(dot)1166310917(at)sss(dot)pgh(dot)pa(dot)us>,
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "" <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.
This really should be documented in the CLUSTER command. Having been
falsely reassured by the following statement in the documentation, I
have clustered tables in live use.
"When a table is being clustered, an ACCESS EXCLUSIVE lock is acquired
on it. This prevents any other database operations (both reads and
writes) from operating on the table until the CLUSTER is finished."
We have crontab jobs to do daily clusters on some of our small tables
with high update rates. The databases are 24/7 with fairly high usage
(query requests in the 10s of millions per day). Should we be load
shifting off of a server before doing the sub-second CLUSTER on a small,
high update table?
> 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.
Ouch! I don't suppose there is any reasonable way to cover this with
locks?
-Kevin