Re: BUG or strange behaviour of update on primary key - Mailing list pgsql-hackers
From | desmodemone |
---|---|
Subject | Re: BUG or strange behaviour of update on primary key |
Date | |
Msg-id | CAEs9oF=Coa_1sq-QohASDD2rtqV74kx1KQUR_Uo4WA_6n+9xiA@mail.gmail.com Whole thread Raw |
In response to | Re: BUG or strange behaviour of update on primary key (Robert Haas <robertmhaas@gmail.com>) |
Responses |
Re: BUG or strange behaviour of update on primary key
(desmodemone <desmodemone@gmail.com>)
|
List | pgsql-hackers |
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 />
pgsql-hackers by date: