Обсуждение: BUG #3455: order of rows should not significant

The following bug has been logged online:

Bug reference:      3455
Logged by:          YaYo
Email address:      yayooo@gmail.com
PostgreSQL version: 8.2.4
Operating system:   OpenBSD4.1 and Windows 5.2.3790
Description:        order of rows should not significant
Details:

test=# select version();
                                         version
----------------------------------------------------------------------------
-------------
 PostgreSQL 8.2.4 on i386-unknown-openbsd4.1, compiled by GCC cc (GCC) 3.3.5
(propolice)
(1 row)

test=# select version();
                                         version
----------------------------------------------------------------------------
--------------
 PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
(mingw-special)
(1 row)
/*I tested on two OS*/

test=# create table t9(id int2 primary key,var int2 not null unique);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t9_pkey" for
table "t9"
NOTICE: CREATE TABLE / UNIQUE will create implicit index "t9_var_key" for
table "t9"
CREATE TABLE
test=# truncate table t9;
TRUNCATE TABLE
test=# insert into t9 values(1,5),(2,3);
INSERT 0 2
test=# select * from t9;
id | var
----+-----
1 | 5
2 | 3
(2 rows)

test=# update t9 set var=var+2;
UPDATE 2
test=# select * from t9;
id | var
----+-----
1 | 7
2 | 5
(2 rows)

test=# truncate table t9;
TRUNCATE TABLE
test=# insert into t9 values(1,3),(2,5);
INSERT 0 2
test=# select * from t9;
id | var
----+-----
1 | 3
2 | 5
(2 rows)

test=# update t9 set var=var+2;
ERROR: duplicate key violates unique constraint "t9_var_key"
test=# select * from t9;
id | var
----+-----
1 | 3
2 | 5
(2 rows)

/*
1)5 first,3 latter: 5+2 => 7, no collision; 3+2 => 5, OK!
2)3 first,5 latter: 3+2 => 5, collision! transaction abort!
*/

test=# drop table t9;
DROP TABLE
test=# create table t9(var int2 not null unique); /*drop id column*/
NOTICE: CREATE TABLE / UNIQUE will create implicit index "t9_var_key" for
table "t9"
CREATE TABLE
test=# truncate table t9;
TRUNCATE TABLE
test=# insert into t9 values(5),(3);
INSERT 0 2
test=# select * from t9;
var
-----
5
3
(2 rows)

test=# update t9 set var=var+2;
UPDATE 2
test=# select * from t9;
var
-----
7
5
(2 rows)

test=# truncate table t9;
TRUNCATE TABLE
test=# insert into t9 values(3),(5);
INSERT 0 2
test=# select * from t9;
var
-----
3
5
(2 rows)

test=# update t9 set var=var+2;
ERROR: duplicate key violates unique constraint "t9_var_key" /*we get the
same conclusion*/
test=# select * from t9;
var
-----
3
5
(2 rows)

"YaYo" <yayooo@gmail.com> writes:

> test=# select * from t9;
> var
> -----
> 3
> 5
> (2 rows)
>
> test=# update t9 set var=var+2;
> ERROR: duplicate key violates unique constraint "t9_var_key" /*we get the
> same conclusion*/

Sorry, Postgres does not support deferred unique constraints which is what you
would need to get this to work. This is a TODO item but nobody has indicated
they wish to (or know how to) do it yet.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com