Thread: Update is not atomic

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

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

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

> 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

We use uniq index for UK/PK but shouldn't. Jan?

Vadim

Mikheev, Vadim wrote:
> > 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
>
> We use uniq index for UK/PK but shouldn't. Jan?
   What  else  can  you  use  than  an  index? A "deferred until   statement end" trigger checking for  duplicates?
Think it'd   have a real bad performance impact.
 
   Whatever  the  execution order might be, the update of '3' to   '4' will see the other '4' as existent WRT the scan
commandId  and  given snapshot - right? If we at the time we now fire up   the ERROR add the key, the  index  and  heap
to  a  list  of   "possible dupkeys", that we'll check at the end of the actual   command, the above would work. The
check at  statement  end   would have to increment the commandcounter and for each entry   do an index scan with the
key,counting the number of  found,   valid heap tuples.
 
   Well,  with  some  million rows doing a "set a = a + 1" could   run out of memory. So this would be something that'd
work in   the  sandbox  and  for non-broken applications (tm). Maybe at   some level (when we escalate the lock to a
fulltable  lock?)   we  simply  forget  about  single  keys, but have a new index   access function that checks the
entireindex for  uniqueness.
 


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com