> 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

Wed, 20 Jun 2001 09:43:05 -0700
"Mikheev, Vadim" <vmikheev@SECTORBASE.COM>
RE: Update is not atomic
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 full table  lock?)
    we  simply  forget  about  single  keys, but have a new index
    access function that checks the entire index 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


Wed, 20 Jun 2001 17:27:20 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
Re: [HACKERS] RE: Update is not atomic