From: | "Mikheev, Vadim" <vmikheev(at)SECTORBASE(dot)COM> |
---|---|
To: | "'Jan Wieck'" <JanWieck(at)Yahoo(dot)com> |
Cc: | "'vitus(at)ice(dot)ru'" <vitus(at)ice(dot)ru>, "'pgsql-hackers(at)postgresql(dot)org'" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | RE: RE: [BUGS] Update is not atomic |
Date: | 2001-06-21 00:10:45 |
Message-ID: | 3705826352029646A3E91C53F7189E32016685@sectorbase2.sectorbase.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
> > > 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
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2001-06-21 01:00:59 | Re: Postgres Internals |
Previous Message | Peter Eisentraut | 2001-06-20 21:42:49 | Re: timestamp with/without time zone |