Re: INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

On Thu, Jan 16, 2014 at 12:35 AM, Heikki Linnakangas
<hlinnakangas(at)vmware(dot)com> wrote:
> Makes sense. Can you extract that into a separate patch, please?

Okay.

On an unrelated note, here are results for a benchmark that compares
the two patches for an insert heavy workload:

http://postgres-benchmarks.s3-website-us-east-1.amazonaws.com/insert-heavy-cmp/

I should point out that this is a sympathetic case for the exclusion
approach; there is only one unique index involved, and the heap tuples
were relatively wide:

pg(at)gerbil:~/pgbench-tools/tests$ cat tpc-b-upsert.sql
\set nbranches 1000000000
\set naccounts 1000000000
\setrandom aid 1 :naccounts
\setrandom bid 1 :nbranches
\setrandom delta -5000 5000
with rej as(insert into pgbench_accounts(aid, bid, abalance, filler)
values(:aid, :bid, :delta, 'filler') on duplicate key lock for update
returning rejects aid, abalance) update pgbench_accounts set abalance
= pgbench_accounts.abalance + rej.abalance from rej where
pgbench_accounts.aid = rej.aid;

(This benchmark used an unlogged table, if only because to do
otherwise would severely starve this particular server of I/O).
--
Peter Geoghegan