Re: BUG #12330: ACID is broken for unique constraints - Mailing list pgsql-hackers

<div style="color:#000; background-color:#fff; font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida
Grande,sans-serif;font-size:16px"><div id="yiv4165093533"><div id="yui_3_16_0_1_1419370979543_2610776"><div
id="yui_3_16_0_1_1419370979543_2610775"style="color:#000;background-color:#fff;font-family:HelveticaNeue, Helvetica
Neue,Helvetica, Arial, Lucida Grande, sans-serif;font-size:16px;">Merlin Moncure <mmoncure@gmail.com> wrote:<br
class="yiv4165093533"clear="none" style="" />> On Fri, Dec 26, 2014 at 12:38 PM, Kevin Grittner
<kgrittn@ymail.com>wrote:<br class="yiv4165093533" clear="none" style="" />>> Tom Lane
<tgl@sss.pgh.pa.us>wrote:<br class="yiv4165093533" clear="none" style="" />>><br class="yiv4165093533"
clear="none"style="" />>>> Just for starters, a 40XXX error report will fail to provide the<br
class="yiv4165093533"clear="none" style="" />>>> duplicated key's value.  This will be a functional
regression,<brclass="yiv4165093533" clear="none" style="" />>><br class="yiv4165093533" clear="none" style=""
/>>>Not if, as is normally the case, the transaction is retried from<br class="yiv4165093533" clear="none"
style=""/>>> the beginning on a serialization failure.  Either the code will<br class="yiv4165093533"
clear="none"style="" />>> check for a duplicate (as in the case of the OP on this thread) and<br
class="yiv4165093533"clear="none" style="" />>> they won't see the error, *or* the the transaction which
created<brclass="yiv4165093533" clear="none" style="" />>> the duplicate key will have committed before the start
ofthe retry<br class="yiv4165093533" clear="none" style="" />>> and you will get the duplicate key error.<br
class="yiv4165093533"clear="none" style="" />><br class="yiv4165093533" clear="none" style="" />> I'm not buying
that;that argument assumes duplicate key errors are<br class="yiv4165093533" clear="none" style="" />> always
'upsert'driven.  Although OP's code may have checked for<br class="yiv4165093533" clear="none" style="" />>
duplicatesit's perfectly reasonable (and in many cases preferable) to<br class="yiv4165093533" clear="none" style=""
/>>force the transaction to fail and report the error directly back to<br class="yiv4165093533" clear="none"
style=""/>> the application.  The application will then switch on the error code<br class="yiv4165093533"
clear="none"style="" />> and decide what to do: retry for deadlock/serialization or abort for<br
class="yiv4165093533"clear="none" style="" />> data integrity error.  IOW, the error handling semantics are<br
class="yiv4165093533"clear="none" style="" />> fundamentally different and should not be mixed.<br
class="yiv4165093533"clear="none" style="" /><br class="yiv4165093533" clear="none" style="" />I think you might be
agreeingwith me without realizing it.  Right <br class="yiv4165093533" clear="none" style="" />now you get "duplicate
keyerror" even if the duplication is caused <br class="yiv4165093533" clear="none" style="" />by a concurrent
transaction-- it is not possible to check the <br class="yiv4165093533" clear="none" style="" />error code (well,
SQLSTATE,technically) to determine whether this <br class="yiv4165093533" clear="none" style="" />is fundamentally a
serializationproblem.  What we're talking about <br class="yiv4165093533" clear="none" style="" />is returning the
serializationfailure return code for the cases <br class="yiv4165093533" clear="none" style="" />where it is a
concurrenttransaction causing the failure and <br class="yiv4165093533" clear="none" style="" />continuing to return
theduplicate key error for all other cases.<br class="yiv4165093533" clear="none" style="" /><br class="yiv4165093533"
clear="none"style="" />Either I'm not understanding what you wrote above, or you seem to<br class="yiv4165093533"
clear="none"style="" />be arguing for being able to distinguish between errors caused by<br class="yiv4165093533"
clear="none"style="" />concurrent transactions and those which aren't.<div class="qtdSeparateBR"><br /><br /></div><div
class="yiv4165093533yqt9921025714"id="yiv4165093533yqtfd55019"><br class="yiv4165093533" clear="none" style="" />--<br
class="yiv4165093533"clear="none" style="" />Kevin Grittner</div>EDB: http://www.enterprisedb.com<br
class="yiv4165093533"clear="none" style="" />The Enterprise PostgreSQL Company<div class="yiv4165093533yqt9921025714"
id="yiv4165093533yqtfd07562"><brclass="yiv4165093533" clear="none" style="" /><br class="yiv4165093533" clear="none"
style=""/></div></div></div></div></div>