I believe, the objections expressed in this thread miss a very important point of all this: the isolation property (the "I" in ACID) is violated.
Here’s a quote from the Wikipedia article on ACID <http://en.wikipedia.org/wiki/ACID>: The isolation property ensures that the concurrent execution of transactions results in a system state that would be obtained if transactions were executed serially, i.e., one after the other. The original example proves that it's violated. Such behaviour can neither be expected by a user, nor is even mentioned anywhere. Instead in the first paragraph of the “About” section of the Postgres site <http://www.postgresql.org/about/> it states: It is fully ACID compliant Which is basically just a lie, until issues like this one get dealt with. 2014-12-29 18:31 GMT+03:00 Merlin Moncure <mmonc...@gmail.com>: > On Mon, Dec 29, 2014 at 9:09 AM, Kevin Grittner <kgri...@ymail.com> wrote: > > Merlin Moncure <mmonc...@gmail.com> wrote: > >> In other words, the current behavior is: > >> txn A,B begin > >> txn A inserts > >> txn B inserts over A, locks, waits > >> txn A commits. B aborts with duplicate key error > > > > What I'm proposing is that for serializable transactions B would > > get a serialization failure; otherwise B would get a duplicate key > > error. If the retry of B looks at something in the database to > > determine what it's primary key should be it will get a new value > > on the retry, since it will be starting after the commit of A. If > > it is using a literal key, not based on something changed by A, it > > will get a duplicate key error on the retry, since it will be > > starting after the commit of A. > > > > It will either succeed on retry or get an error for a different > > reason. > > In that case: we don't agree. How come duplicate key errors would be > reported as serialization failures but not RI errors (for example, > inserting a record pointing to another record which a concurrent > transaction deleted)? > > IMO, serialization errors are an implementation artifact and should > not mask well defined errors in SQL under any circumstances (or if > they must, those cases should absolutely be as narrow as possible). > > merlin >
