Re: [HACKERS] 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(at)Yahoo(dot)com #

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