"Albe Laurenz" wrote: > See the example I concocted in > http://archives.postgresql.org/pgsql-hackers/2009-05/msg00316.php Sure, let's look at that example. Of course, *any* transaction run by itself won't show differences from true serializable behavior *regardless* of the mode in which it runs -- because it actually was serialized. Let's see how your example might work if the function was being run on two different backends at the same time with different personid values. Connection 1: ========== [Currently no highlander; the function does this for personid = 1] SELECT ishighlander INTO b FROM scots WHERE id=personid; IF b THEN RETURN; /* no need to do anything */ END IF; UPDATE scots SET ishighlander=TRUE WHERE id=personid; SELECT count(*) INTO n FROM scots WHERE ishighlander; IF (n > 1) THEN RAISE EXCEPTION 'There can be only one'; END IF; [Connection 1 now sees a highlander; not yet committed]
Connection 2: =========== [Currently no highlander according to this snapshot] [the function does exactly the same thing as on Connection 1, but for personid 2] [It doesn't see the work of Connection 1, so it's count shows the update is OK] Now they commit, in either order. You now have two highlanders in the database. You have just demonstrated another case of write skew, where snapshot isolation does not behave in a truly serializable fashion, allowing constraints enforced in application software or functions (including triggers) to be violated. With the changes I'm working on, one of these would be rolled back with a serialization error. > PS: Different from what Kevin claimed, Oracle also cannot grant > you strictly serializable transactions, because they also use > snapshot isolation. Apologies if that is still true. I don't use Oracle and one of the recent articles I recently read seemed to indicate otherwise. Thanks for the correction. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers