Horton

Thread

  1. Update is not atomic

    Victor Wagner <vitus@ice.ru> — 2001-06-20T10:56:05Z

    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@ice.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

  2. Re: Update is not atomic

    Peter Eisentraut <peter_e@gmx.net> — 2001-06-20T15:59:23Z

    Victor Wagner writes:

> 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

This is a known problem with unique contraints, but it's not easy to fix
it.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

  3. Re: Update is not atomic

    Victor Wagner <vitus@ice.ru> — 2001-06-20T16:02:06Z

    On Wed, 20 Jun 2001, Peter Eisentraut wrote:

> From: Peter Eisentraut <peter_e@gmx.net>
> Subject: Re: [BUGS] Update is not atomic
>
> Victor Wagner writes:
>
> > 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


> This is a known problem with unique contraints, but it's not easy to fix
> it.

Is there any known workaround?
Like specifying order  of updates?



-- 
Victor Wagner			vitus@ice.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