Обсуждение: BUG or strange behaviour of update on primary key

Hello there,<br />                 two guys of our developer team ( Lorenzo and Federico ) have seen a strange
behaviour (in 8.4 and 9.1.1 ) on update, and I think is a bug or something really strange or I not understand correctly
thisbehavior .<br /><br />I explain now  ( begin transaction or auto commit is the same):<br /><br /> create table
testup( a int ) ;<br /><br /> alter table testup add primary key (a ) ;<br /><br /> insert into testup values (1);<br
/><br/> insert into testup values (2);<br /><br />  update  testup set a=a+1 ;<br /><span style="color: rgb(255, 0,
0);">ERROR: duplicate key value violates unique constraint "testup_pkey"</span><br style="color: rgb(255, 0, 0);"
/><spanstyle="color: rgb(255, 0, 0);">DETTAGLI: Key (a)=(2) already exists.</span><br /><br /><br />by  the way :<br
/><br/>test=# update  testup set a=a-1 ;<br />UPDATE 2<br /><span style="color: rgb(51, 204, 0);">SUCCESFUL</span><br
/><br/>-- REVERSE ORDER --<br /><br />Now create the same table with rows in  reverse physical order:<br /><br
/> createtable testup2  ( a int ) ;<br /><br /> alter table testup2  add primary key (a ) ;<br /><br />insert into
testup2 values (2) ;<br /><br /> insert into testup2  values (1);<br /><br /> update  testup2  set a=a+1 ;<br />UPDATE
2<br/>  SUCCESFUL<br /><br />by  the way :<br /><br />test=# update  testup2  set a=a-1 ;<br /><span style="color:
rgb(255,0, 0);">ERROR:  duplicate key value violates unique constraint "testup2_pkey"</span><br style="color: rgb(255,
0,0);" /><span style="color: rgb(255, 0, 0);">DETTAGLI: Key (a)=(1) already exists.</span><br /><br /><br />I have
testedin Oracle 11gR1 and 11gR2 without the same behaviour :<br /><br />Oracle :<br /><br /><br />SQL> create table
a( b number ) ;<br /><br />Tabella creata.<br /><br />SQL> alter table a add primary key   (b) ;<br /><br />Tabella
modificata.<br/><br />SQL> insert into a values (1 ) ;<br /><br />Creata 1 riga.<br /><br />SQL>  insert into a
values(2) ;<br /><br />Creata 1 riga.<br /><br />SQL> commit ;<br /><br />Commit completato.<br /><br />SQL>
updatea set b=b+1 ;<br /><br />Aggiornate 2 righe.<br /><br />SQL> commit ;<br /><br />Commit completato.<br /><br
/>SQL>update a set b=b-1;<br /><br />Aggiornate 2 righe.<br /><br />SQL> commit;<br /><br />Commit completato.<br
/><br/>In MySQL 5.1.58 with InnoDB  the behaviour is more strange (always for +1  and indipendent from the reverse
orderO_o)  :<br /><br /><br />mysql> create table testup ( a int ) engine innodb ;<br /> Query OK, 0 rows affected
(0.21sec)<br /><br />mysql> alter table testup add primary key (a) ;<br />Query OK, 0 rows affected (0.34 sec)<br
/>Records:0  Duplicates: 0  Warnings: 0<br /><br />mysql> insert into testup values (1) ;<br /> Query OK, 1 row
affected(0.12 sec)<br /><br />mysql> insert into testup values (2) ;<br />Query OK, 1 row affected (0.15 sec)<br
/><br/>mysql> commit ;<br />Query OK, 0 rows affected (0.00 sec)<br /><br />mysql> update  testup set a=a+1 ;<br
/><spanstyle="color: rgb(255, 0, 0);">ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'</span>  (-- like
postgres!)<br/>mysql> update  testup set a=a-1 ;<br /><span style="color: rgb(51, 204, 0);">Query OK, 2 rows
affected(0.16 sec)</span><br style="color: rgb(51, 204, 0);" /><span style="color: rgb(51, 204, 0);">Rows matched: 2 
Changed:2  Warnings: 0</span><br /><br />mysql> commit ;<br />Query OK, 0 rows affected (0.00 sec)<br /><br
/>mysql>update  testup set a=a+1 ;<br />ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'<br /> mysql>
select* from testup ;<br />+---+<br />| a |<br />+---+<br />| 0 |<br />| 1 |<br />+---+<br />2 rows in set (0.00
sec)<br/><br /> -- REVERSE ORDER --<br /><br />mysql> truncate table testup ;<br />Query OK, 0 rows affected (0.11
sec)<br/><br />mysql>  insert into testup values (2) ;<br />Query OK, 1 row affected (0.12 sec)<br /><br />mysql>
insertinto testup values (1) ;<br /> Query OK, 1 row affected (0.17 sec)<br /><br />mysql>  update  testup set a=a+1
;    (-- O_O  is tottaly different from postgres!)<br /><span style="color: rgb(255, 0, 0);">ERROR 1062 (23000):
Duplicateentry '2' for key 'PRIMARY'</span><br /> mysql> update  testup set a=a-1 ;<br /><span style="color: rgb(51,
204,0);">Query OK, 2 rows affected (0.16 sec)</span><br style="color: rgb(51, 204, 0);" /><span style="color: rgb(51,
204,0);">Rows matched: 2  Changed: 2  Warnings: 0</span><br /><br />In MySql with Myisam is tottaly different and
similarto Oracle :<br /><br />mysql> create table testup_myisam  ( a int ) engine myisam ;<br /> Query OK, 0 rows
affected(0.17 sec)<br /><br />mysql> insert into testup_myisam values (2) ;<br />Query OK, 1 row affected (0.00
sec)<br/><br />mysql>  insert into testup_myisam values (1) ;<br />Query OK, 1 row affected (0.00 sec)<br /><br
/>mysql>update testup_myisam  set a=a+1 ;<br />Query OK, 2 rows affected (0.00 sec)<br />Rows matched: 2  Changed:
2 Warnings: 0<br /><br />mysql>  update testup_myisam  set a=a-1 ;<br />Query OK, 2 rows affected (0.00 sec)<br />
Rowsmatched: 2  Changed: 2  Warnings: 0<br /><br />-- REVERSE ORDER --<br /><br />mysql> truncate table
testup_myisam;<br />Query OK, 0 rows affected (0.00 sec)<br /><br />mysql>  insert into testup_myisam values (1)
;<br/>Query OK, 1 row affected (0.00 sec)<br /><br />mysql>  insert into testup_myisam values (2) ;<br />Query OK, 1
rowaffected (0.00 sec)<br /><br />mysql> update testup_myisam  set a=a+1 ;<br />Query OK, 2 rows affected (0.00
sec)<br/>Rows matched: 2  Changed: 2  Warnings: 0<br /><br />mysql>  update testup_myisam  set a=a-1 ;<br />Query
OK,2 rows affected (0.00 sec)<br />Rows matched: 2  Changed: 2  Warnings: 0<br /><br /><br />The problem for us  is
whenwe develop or migrate  applications between different databases.<br /> By the way I think is not  right that an
updateon the same set of rows will be successful or failed if the rows are ordered or not, no?<br />I  think it is
somethingin correlation with visibility of rows in MVCC (update=>insert + delete tuple).<br /><br />What do you
thinkabout? <br /><br />See you soon<br /><br />Regards, Mat<br /><br /><br /><br /><br /> 

desmodemone <desmodemone@gmail.com> writes:
> create table testup ( a int ) ;

> alter table testup add primary key (a ) ;

> insert into testup values (1);

> insert into testup values (2);

>  update  testup set a=a+1 ;
> ERROR:  duplicate key value violates unique constraint "testup_pkey"
> DETTAGLI: Key (a)=(2) already exists.

If you want that to work reliably, you need to mark the primary key
constraint as deferred.  By default, uniqueness is checked immediately
when a row is inserted or updated --- and here, when you update 1 to 2,
it's not unique because the second row hasn't been visited yet.
        regards, tom lane

Hello there
                   Thanks Tom!
By the way I find something very funny :

Oracle 11gR2 :

SQL> create table testup ( a number ) ;

Tabella creata.

SQL> alter table testup add primary key (a) NOT DEFERRABLE INITIALLY IMMEDIATE ;

Tabella modificata.

SQL> insert into testup values (1 ) ;

Creata 1 riga.

SQL>  insert into testup values (2 ) ;

Creata 1 riga.

SQL> commit ;

Commit completato.

SQL> update testup set a=a+1 ;

Aggiornate 2 righe.   -->>> Oracle Bug ??

SQL> commit ;

Commit completato.


Postgresql :


create table  testup  ( a int ) ;

alter table testup add primary key(a) NOT DEFERRABLE INITIALLY IMMEDIATE ;

 insert into testup values (1);

insert into testup values (2);

update testup set a=a+1 ;
ERROR:  duplicate key value violates unique constraint "testup_pkey"




Like Tom correctly says :

alter table  testup  DROP   CONSTRAINT testup_pkey ;

alter table testup add primary key(a)  DEFERRABLE INITIALLY IMMEDIATE ;

 update testup set a=a+1 ;
UPDATE 2

commit;


Seems an Oracle bug not Postgresql one!

Regards, Mat

On Mon, Oct 17, 2011 at 7:30 PM, desmodemone <desmodemone@gmail.com> wrote:
> Seems an Oracle bug not Postgresql one!

I don't think it's a bug for it to work.  It'd probably work in
PostgreSQL too, if you inserted (2) first and then (1).  It's just
that, as Tom says, if you want it to be certain to work (rather than
depending on the order in which the rows are inserted), you need the
checks to be deferred.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 18/10/2011, at 1:00 PM, Robert Haas wrote:

> On Mon, Oct 17, 2011 at 7:30 PM, desmodemone <desmodemone@gmail.com> wrote:
>> Seems an Oracle bug not Postgresql one!
> 
> I don't think it's a bug for it to work.  It'd probably work in
> PostgreSQL too, if you inserted (2) first and then (1).  It's just
> that, as Tom says, if you want it to be certain to work (rather than
> depending on the order in which the rows are inserted), you need the
> checks to be deferred.

Do deferred checks such as this have a memory impact for bulk updates?

Royce Ausburn <royce.ml@inomial.com> writes:
> On 18/10/2011, at 1:00 PM, Robert Haas wrote:
>> I don't think it's a bug for it to work.  It'd probably work in
>> PostgreSQL too, if you inserted (2) first and then (1).  It's just
>> that, as Tom says, if you want it to be certain to work (rather than
>> depending on the order in which the rows are inserted), you need the
>> checks to be deferred.

> Do deferred checks such as this have a memory impact for bulk updates?

Yes indeed.  That's why immediate check is the default.
        regards, tom lane

On tis, 2011-10-18 at 01:30 +0200, desmodemone wrote:
> 
> alter table  testup  DROP   CONSTRAINT testup_pkey ;
> 
> alter table testup add primary key(a)  DEFERRABLE INITIALLY
> IMMEDIATE ;
> 
>  update testup set a=a+1 ;
> UPDATE 2
> 
> commit;
> 
> 
> Seems an Oracle bug not Postgresql one! 

Oracle's behavior is OK.  PostgreSQL's default behavior is wrong in the
sense that it checks the constraint even in invisible states *during*
the statement, rather than only after.  Marking the constraint
DEFERRABLE (which means deferrable to the end of the *transaction*, not
statement) is a red herring to get the system to do it right(er),
because there is no separate syntax to say deferrable to end of
statement.

Basically, this is maintaining historical buggy behavior for
performance.  If you want correct and slow behavior instead, you need to
tell explicitly.

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 /> 

Hello there,

I think I have find a limit of this workaround. Imagine I have two tables in Oracle or other rdbms with a foreign key between them :

testup3 ( a int)  primary key on a NOT DEFERRABLE INITIALLY IMMEDIATE ;

testup4 ( a int)  foreign key on a references testup3(a)  ;

For first table I could create this (to have a "normal" sql standard behavior on update with multiple rows) :

testup3 ( a int)  primary key on a DEFERRABLE INITIALLY IMMEDIATE ;

By the way I could not create a   foreign key on a DEFERRABLE constraint , in fact I obtain an  error like this :

ERROR:  cannot use a deferrable unique constraint for referenced table


So if I  have a normal ERD schema with FK , I could not use the workaround of "DEFERRABLE" constraints .
I found an old discussion on this  :

http://archives.postgresql.org/pgsql-hackers/2010-06/msg00168.php

In my opinion it could be a big limitation for who want  migrate applications or is developing  applications on different db.

Any suggest or idea ?

Regards, Mat