Tom Lane <t...@sss.pgh.pa.us> wrote: > Kevin Grittner <kgri...@ymail.com> writes:
>> Are there any objections to generating a write conflict instead >> of a duplicate key error if the duplicate key was added by a >> concurrent transaction? > > Yes. This will deliver a less meaningful error code, That depends entirely on whether you care more about whether the problem was created by a concurrent transaction or exactly how that concurrent transaction created the problem. For those using serializable transactions to manage concurrency the former is at least an order of magnitude more important. This is not the first time getting a constraint violation SQLSTATE for the actions of a concurrent serializable transaction has been reported as a bug. Going from memory, I think this might be about the fifth time users have reported it as a bug or potential bug on these lists. People using serializable transactions normally run all queries through common code with will retry the transaction from the start if there is a SQLSTATE starting with '40' (or perhaps picking out the specific codes '40001' and '40P01'). Not doing so for *some* types of errors generated by concurrent transactions reduces the application's level of user-friendliness, and may introduce surprising bugs. In this particular case the OP wants to do one thing if a row with a paricular value for a unique index exists, and something different if it doesn't. If we generate the write conflict for the case that it is concurrently added, it can retry the transaction and do one or the other; if we don't pay attention to that, they need weird heuristics for "the third case". That really is not more meaningful or useful. > *and* break existing code that is expecting the current behavior. Possibly, but my experience is more that failure to behave the way I suggest is biting people and causing them a lot of extra work and pain. I would be fine with limiting the new behavior to serializable transactions, since that seems to be where people want this behavior. It would bring us closer to "the transaction will run as though it were the only transaction running or roll back with a serialization failure" without having to add caveats and exceptions. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers