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)

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

"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


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org