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
