Re: [BUGS] BUG #2830: Wrong results for prepared statements

#1

Quote:

On Sat, Dec 16, 2006 at 5:15 PM, in message
8738.1166310917 (AT) sss (DOT) pgh.pa.us>,

Tom Lane <tgl (AT) sss (DOT) pgh.pa.us> wrote:

Quote:

"" <martin.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?

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: [BUGS] BUG #2830: Wrong results for prepared statements

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

Re: [BUGS] BUG #2830: Wrong results for prepared statements - 12-18-2006 , 09:54 AM