Обсуждение: BUG #10429: the same update return different result
The following bug has been logged on the website: Bug reference: 10429 Logged by: zhanglong Email address: chuisiniu@hotmail.com PostgreSQL version: 9.3.4 Operating system: window7 32bit Description: E:\postgresql-9.4beta1\db\bin>psql postgres psql (9.4beta1) Type "help" for help. postgres=# drop table t1; DROP TABLE postgres=# create table t1(c1 int primary key); CREATE TABLE postgres=# insert into t1 values (1);insert into t1 values (2);update t1 set c1 = c1 +1; INSERT 0 1 INSERT 0 1 ERROR: duplicate key value violates unique constraint "t1_pkey" DETAIL: Key (c1)=(2) already exists. postgres=# select * from t1; c1 ---- 1 2 (2 rows) postgres=# postgres=# drop table t1; DROP TABLE postgres=# create table t1(c1 int primary key); CREATE TABLE postgres=# insert into t1 values (2);insert into t1 values (1);update t1 set c1 = c1 +1; INSERT 0 1 INSERT 0 1 UPDATE 2 postgres=# select * from t1; c1 ---- 3 2 (2 rows) The data is all the same except the order I insert it,but the update get different result.From the source code, it seems that db update the tuple first found out.
El 23/05/2014 04:41 a.m., chuisiniu@hotmail.com escribió: > The following bug has been logged on the website: > > Bug reference: 10429 > Logged by: zhanglong > Email address: chuisiniu@hotmail.com > PostgreSQL version: 9.3.4 > Operating system: window7 32bit > Description: > > E:\postgresql-9.4beta1\db\bin>psql postgres > psql (9.4beta1) > Type "help" for help. > > postgres=# drop table t1; > DROP TABLE > postgres=# create table t1(c1 int primary key); > CREATE TABLE > postgres=# insert into t1 values (1);insert into t1 values (2);update t1 set > c1 = c1 +1; > INSERT 0 1 > INSERT 0 1 > ERROR: duplicate key value violates unique constraint "t1_pkey" > DETAIL: Key (c1)=(2) already exists. > postgres=# select * from t1; > c1 > ---- > 1 > 2 > (2 rows) > > > postgres=# > postgres=# drop table t1; > DROP TABLE > postgres=# create table t1(c1 int primary key); > CREATE TABLE > postgres=# insert into t1 values (2);insert into t1 values (1);update t1 set > c1 = c1 +1; > INSERT 0 1 > INSERT 0 1 > UPDATE 2 > postgres=# select * from t1; > c1 > ---- > 3 > 2 > (2 rows) > > The data is all the same except the order I insert it,but the update get > different result.From the source code, it seems that db update the tuple > first found out. > > the first case you insert row number 1 value 1 row number 2 value 2 when update row number 1 value 1 + 1=2 produces duplicate the second case you insert row number 1 value 2 row number 2 value 1 when update row number 1 value 2 + 1=3 row number 1 value 1 + 1=2 OK!!! perhaps you need define your constraint deferrable http://www.postgresql.org/docs/9.3/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.3/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. INITIALLY IMMEDIATE INITIALLY DEFERRED If a constraint is deferrable, this clause specifies the default time to check the constraint. If the constraint is INITIALLY IMMEDIATE, it is checked after each statement. This is the default. If the constraint is INITIALLY DEFERRED, it is checked only at the end of the transaction. The constraint check time can be altered with the SET CONSTRAINTS <http://www.postgresql.org/docs/9.3/static/sql-set-constraints.html> command.
Hi, >> postgres=3D# update t1 set c1 =3D c1 +1; > This controls whether the constraint can be deferred. A constraint = that is not deferrable will be checked immediately after every command. >=20 My understanding would have been, that "update t1 set c1=3Dc1+1;" is ONE = command, so even in the NOT DEFERRABLE case the check would occur only = after both values were changed and as such not find any violation of the = constraint.=20 claudio --=20 Claudio Nieder . mailto:claudio.nieder@inodes.ch . http://inodes.ch/ iNodes AG . Loogartenstrasse 14 . CH-8048 Z=FCrich . +41 43 960 = 0066
Claudio Nieder <claudio.nieder.lists@inodes.ch> writes: >>> postgres=# update t1 set c1 = c1 +1; >> This controls whether the constraint can be deferred. A constraint that is not deferrable will be checked immediatelyafter every command. >> > My understanding would have been, that "update t1 set c1=c1+1;" is ONE command, so even in the NOT DEFERRABLE case thecheck would occur only after both values were changed and as such not find any violation of the constraint. The important detail is a bit further down the page, in the "Compatibility" section: Non-deferred Uniqueness Constraints When a UNIQUE or PRIMARY KEY constraint is not deferrable, PostgreSQL checks for uniqueness immediately whenever a row is inserted or modified. The SQL standard says that uniqueness should be enforced only at the end of the statement; this makes a difference when, for example, a single command updates multiple key values. To obtain standard-compliant behavior, declare the constraint as DEFERRABLE but not deferred (i.e., INITIALLY IMMEDIATE). Be aware that this can be significantly slower than immediate uniqueness checking. regards, tom lane