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)
