From: | Victor Wagner <vitus(at)ice(dot)ru> |
---|---|
To: | <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Update is not atomic |
Date: | 2001-06-20 10:56:05 |
Message-ID: | Pine.LNX.4.30.0106201449540.19755-100000@party.ice.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Following problem is encountered in Postgersql 7.1.2 on Linux
(I haven't tested on Solaris, but it seems that it is
platform-independent)
If we are trying to update field with unique constraint on it
(for instance, primary key) update might fail depending on order
how individual rows are updated. This is clear violation of SQL standard,
which states that individual sql operators are atomic, and constraints
should be valid either before beginning or after completion of operator.
Problem can be demonstrated by following example
create table a (a numeric primary key);
insert into a values (1);
insert into a values (2);
insert into a values (3);
insert into a values (4);
update a set a=a+1 where a>2;
ERROR: Cannot insert a duplicate key into unique index a_pkey
Same statement sequence in Oracle (8.1.5.1) produces expected result:
2 rows updated
SQL> select * from a;
A
----------
1
2
4
5
--
Victor Wagner vitus(at)ice(dot)ru
Chief Technical Officer Office:7-(095)-748-53-88
Communiware.Net Home: 7-(095)-135-46-61
http://www.communiware.net http://www.ice.ru/~vitus
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2001-06-20 15:05:52 | Re: pg_ctl restart just appends to command line instead of regenerating original cmd |
Previous Message | Bruce Momjian | 2001-06-20 00:27:45 | Re: pg_ctl restart just appends to command line instead of regenerating original cmd |