BUG or strange behaviour of update on primary key - Mailing list pgsql-hackers

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