Re: BUG or strange behaviour of update on primary key

Hi there,<br />              I could workaround the behavior with deferred constraint, and it's ok, but as I show, I
havedifferent behavior for constraint with the same definition in two rdbms and Postgresql depends on the physical
orderof row (with the same definition of constraint NOT DEFERRABLE INITIALLY IMMEDIATE) , or better <span style="color:
rgb(255,0, 0);">Postgresql seems to check for every row, even if the command is one</span> (I am doing one update on
allof rows) , right?  .<br /><br />Moreover , in documentation the definition says that a not deferrable constraints
willcheck after "every command" , not after every row of the command:<br /><br /><a
href="http://www.postgresql.org/docs/9.1/static/sql-createtable.html">http://www.postgresql.org/docs/9.1/static/sql-createtable.html</a><br
/><br/><span class="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: 'Times New
Roman';font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal;
orphans:2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2;
word-spacing:0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px;
-webkit-text-decorations-in-effect:none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; font-size:
medium;"><span class="Apple-style-span" style="font-family: verdana, sans-serif; font-size: 12px; text-align: left;
"><dl><dt><ttclass="LITERAL" style="font-size: 1.2em; ">DEFERRABLE</tt><br /><tt class="LITERAL" style="font-size:
1.2em;">NOT DEFERRABLE</tt><dd><p style="font-size: 1em; line-height: 1.5em; margin: 1.2em 0em;">This controls whether
theconstraint can be deferred.<b style="color: rgb(255, 0, 0);"> A constraint that is not deferrable will be checked
immediatelyafter every command</b>. Checking of constraints that are deferrable can be postponed until the end of the
transaction(using the<span class="Apple-converted-space"> </span><a
href="http://www.postgresql.org/docs/9.0/static/sql-set-constraints.html"style="color: rgb(0, 102, 162);
text-decoration:underline; ">SET CONSTRAINTS</a><span class="Apple-converted-space"> </span>command).<span
class="Apple-converted-space"> </span><ttclass="LITERAL" style="font-size: 1.2em; ">NOT DEFERRABLE</tt><span
class="Apple-converted-space"> </span>isthe default. Currently, only<span class="Apple-converted-space"> </span><tt
class="LITERAL"style="font-size: 1.2em; ">UNIQUE</tt>,<span class="Apple-converted-space"> </span><tt class="LITERAL"
style="font-size:1.2em; ">PRIMARY KEY</tt>,<span class="Apple-converted-space"> </span><tt class="LITERAL"
style="font-size:1.2em; ">EXCLUDE</tt>, and<span class="Apple-converted-space"> </span><tt class="LITERAL"
style="font-size:1.2em; ">REFERENCES</tt><span class="Apple-converted-space"> </span>(foreign key) constraints accept
thisclause.<span class="Apple-converted-space"> </span><tt class="LITERAL" style="font-size: 1.2em; ">NOT
NULL</tt><spanclass="Apple-converted-space"> </span>and<span class="Apple-converted-space"> </span><tt class="LITERAL"
style="font-size:1.2em; ">CHECK</tt><span class="Apple-converted-space"> </span>constraints are not
deferrable.</dl></span>---------------<br/><br /></span>If this is "historical buggy behavior for performance" , I
thinkwe have to change  the definition of NOT DEFERRABLE in documentation,<br />because Postgresql is not checking at
endof a dml, but for every row  modified by the command or   there is something needs a patch.<br /><br /><br
/>Regards,Mat<br /><br /><div class="gmail_quote">2011/10/18 Robert Haas <span dir="ltr"><<a
href="mailto:robertmhaas@gmail.com">robertmhaas@gmail.com</a>></span><br/><blockquote class="gmail_quote"
style="margin:00 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;"><div class="im">On Mon, Oct 17, 2011 at 7:30 PM,
desmodemone<<a href="mailto:desmodemone@gmail.com">desmodemone@gmail.com</a>> wrote:<br /> > Seems an Oracle
bugnot Postgresql one!<br /><br /></div>I don't think it's a bug for it to work.  It'd probably work in<br />
PostgreSQLtoo, if you inserted (2) first and then (1).  It's just<br /> that, as Tom says, if you want it to be certain
towork (rather than<br /> depending on the order in which the rows are inserted), you need the<br /> checks to be
deferred.<br/><font color="#888888"><br /> --<br /> Robert Haas<br /> EnterpriseDB: <a
href="http://www.enterprisedb.com"target="_blank">http://www.enterprisedb.com</a><br /> The Enterprise PostgreSQL
Company<br/></font></blockquote></div><br />