Re: BUG #10429: the same update return different result

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.