BUG or strange behaviour of update on primary key
Hello there,
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 this
behavior .
I explain now ( begin transaction or auto commit is the same):
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.
by the way :
test=# update testup set a=a-1 ;
UPDATE 2
SUCCESFUL
-- REVERSE ORDER --
Now create the same table with rows in reverse physical order:
create table testup2 ( a int ) ;
alter table testup2 add primary key (a ) ;
insert into testup2 values (2) ;
insert into testup2 values (1);
update testup2 set a=a+1 ;
UPDATE 2
SUCCESFUL
by the way :
test=# update testup2 set a=a-1 ;
ERROR: duplicate key value violates unique constraint "testup2_pkey"
DETTAGLI: Key (a)=(1) already exists.
I have tested in Oracle 11gR1 and 11gR2 without the same behaviour :
Oracle :
SQL> create table a ( b number ) ;
Tabella creata.
SQL> alter table a add primary key (b) ;
Tabella modificata.
SQL> insert into a values (1 ) ;
Creata 1 riga.
SQL> insert into a values (2) ;
Creata 1 riga.
SQL> commit ;
Commit completato.
SQL> update a set b=b+1 ;
Aggiornate 2 righe.
SQL> commit ;
Commit completato.
SQL> update a set b=b-1;
Aggiornate 2 righe.
SQL> commit;
Commit completato.
In MySQL 5.1.58 with InnoDB the behaviour is more strange (always for +1
and indipendent from the reverse order O_o) :
mysql> create table testup ( a int ) engine innodb ;
Query OK, 0 rows affected (0.21 sec)
mysql> alter table testup add primary key (a) ;
Query OK, 0 rows affected (0.34 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into testup values (1) ;
Query OK, 1 row affected (0.12 sec)
mysql> insert into testup values (2) ;
Query OK, 1 row affected (0.15 sec)
mysql> commit ;
Query OK, 0 rows affected (0.00 sec)
mysql> update testup set a=a+1 ;
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY' (-- like
postgres!)
mysql> update testup set a=a-1 ;
Query OK, 2 rows affected (0.16 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> commit ;
Query OK, 0 rows affected (0.00 sec)
mysql> update testup set a=a+1 ;
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> select * from testup ;
+---+
| a |
+---+
| 0 |
| 1 |
+---+
2 rows in set (0.00 sec)
-- REVERSE ORDER --
mysql> truncate table testup ;
Query OK, 0 rows affected (0.11 sec)
mysql> insert into testup values (2) ;
Query OK, 1 row affected (0.12 sec)
mysql> insert into testup values (1) ;
Query OK, 1 row affected (0.17 sec)
mysql> update testup set a=a+1 ; (-- O_O is tottaly different from
postgres!)
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
mysql> update testup set a=a-1 ;
Query OK, 2 rows affected (0.16 sec)
Rows matched: 2 Changed: 2 Warnings: 0
In MySql with Myisam is tottaly different and similar to Oracle :
mysql> create table testup_myisam ( a int ) engine myisam ;
Query OK, 0 rows affected (0.17 sec)
mysql> insert into testup_myisam values (2) ;
Query OK, 1 row affected (0.00 sec)
mysql> insert into testup_myisam values (1) ;
Query OK, 1 row affected (0.00 sec)
mysql> update testup_myisam set a=a+1 ;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> update testup_myisam set a=a-1 ;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
-- REVERSE ORDER --
mysql> truncate table testup_myisam ;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into testup_myisam values (1) ;
Query OK, 1 row affected (0.00 sec)
mysql> insert into testup_myisam values (2) ;
Query OK, 1 row affected (0.00 sec)
mysql> update testup_myisam set a=a+1 ;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> update testup_myisam set a=a-1 ;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
The problem for us is when we develop or migrate applications between
different databases.
By the way I think is not right that an update on the same set of rows will
be successful or failed if the rows are ordered or not, no?
I think it is something in correlation with visibility of rows in MVCC
(update=>insert + delete tuple).
What do you think about?
See you soon
Regards, Mat
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
2011/10/18 Tom Lane <tgl@sss.pgh.pa.us>
Show quoted text
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
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 2commit;
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,
I could workaround the behavior with deferred constraint, and
it's ok, but as I show, I have different behavior for constraint with the
same definition in two rdbms and Postgresql depends on the physical order of
row (with the same definition of constraint NOT DEFERRABLE INITIALLY
IMMEDIATE) , or better Postgresql seems to check for every row, even if the
command is one (I am doing one update on all of rows) , right? .
Moreover , in documentation the definition says that a not deferrable
constraints will check after "every command" , not after every row of the
command:
http://www.postgresql.org/docs/9.1/static/sql-createtable.html
DEFERRABLE
NOT DEFERRABLE
This controls whether the constraint can be deferred.* A constraint that is
not deferrable will be checked immediately after every command*. Checking of
constraints that are deferrable can be postponed until the end of the
transaction (using the SET
CONSTRAINTS<http://www.postgresql.org/docs/9.0/static/sql-set-constraints.html>
command). NOT DEFERRABLE is the default. Currently, only UNIQUE, PRIMARY
KEY, EXCLUDE, and REFERENCES (foreign key) constraints accept this clause. NOT
NULL and CHECK constraints are not deferrable.
---------------
If this is "historical buggy behavior for performance" , I think we have to
change the definition of NOT DEFERRABLE in documentation,
because Postgresql is not checking at end of a dml, but for every row
modified by the command or there is something needs a patch.
Regards, Mat
2011/10/18 Robert Haas <robertmhaas@gmail.com>
Show quoted text
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
2011/10/18 Robert Haas <robertmhaas@gmail.com>
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
2011/10/18 desmodemone <desmodemone@gmail.com>
Hi there,
I could workaround the behavior with deferred constraint, and
it's ok, but as I show, I have different behavior for constraint with the
same definition in two rdbms and Postgresql depends on the physical order of
row (with the same definition of constraint NOT DEFERRABLE INITIALLY
IMMEDIATE) , or better Postgresql seems to check for every row, even if
the command is one (I am doing one update on all of rows) , right? .Moreover , in documentation the definition says that a not deferrable
constraints will check after "every command" , not after every row of the
command:http://www.postgresql.org/docs/9.1/static/sql-createtable.html
DEFERRABLE
NOT DEFERRABLEThis controls whether the constraint can be deferred.* A constraint that
is not deferrable will be checked immediately after every command*.
Checking of constraints that are deferrable can be postponed until the end
of the transaction (using the SET CONSTRAINTS<http://www.postgresql.org/docs/9.0/static/sql-set-constraints.html>
command). NOT DEFERRABLE is the default. Currently, only UNIQUE, PRIMARY
KEY, EXCLUDE, and REFERENCES (foreign key) constraints accept this clause.
NOT NULL and CHECK constraints are not deferrable.
---------------If this is "historical buggy behavior for performance" , I think we have to
change the definition of NOT DEFERRABLE in documentation,
because Postgresql is not checking at end of a dml, but for every row
modified by the command or there is something needs a patch.Regards, Mat
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/msg00151.php>
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