RE: RE: [BUGS] Update is not atomic

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

AFAIR, standard requires "deffered" (until statement/transaction(?)
end) as default behaviour for RI (all?) constraints. But no matter
what is default, "deffered" *must* be available => uniq indices
must not be used.

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

Incrementing comand counter is not enough - dirty reads are required
to handle concurrent PK updates.

>     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

How is this different from (deffered) updates of million FK we allow
right now? Let's user decide what behaviour (deffered/immediate) he
need. The point is that now user has no ability to choose what's
right for him.

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

I wouldn't bother to implement this. User always has ability to excl.
lock table, drop constraints, update whatever he want and recreate
constraints again.

Vadim