Thread: RE: [BUGS] Update is not atomic

RE: [BUGS] Update is not atomic

From
"Mikheev, Vadim"
Date:
> 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


Re: RE: [BUGS] Update is not atomic

From
Jan Wieck
Date:
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