merge-test-v1.tar

application/x-tar
Filename: merge-test-v1.tar
Type: application/x-tar
Part: 0
setup-function.sql0000644000076400007640000000212111456677242014154 0ustar  gsmithgsmithCREATE OR REPLACE FUNCTION change_account_balance(key INT, delta NUMERIC) RETURNS VOID AS
$$
BEGIN
    LOOP
        -- first try to update the key
        UPDATE pgbench_accounts SET abalance = abalance + delta WHERE aid = key;
        IF found THEN
            RETURN;
        END IF;
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
        BEGIN
            INSERT INTO pgbench_accounts(aid,bid,abalance) VALUES (key,1+(key / 100000)::integer,delta);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- do nothing, and loop to try the UPDATE again
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION merge_account_balance(key INT, delta NUMERIC) RETURNS VOID AS
$$
BEGIN
MERGE INTO pgbench_accounts t USING (SELECT * FROM pgbench_accounts WHERE aid = key) AS s ON t.aid=s.aid WHEN MATCHED THEN UPDATE SET abalance = s.abalance + delta WHEN NOT MATCHED THEN INSERT VALUES (key,1+(key / 100000)::integer,delta,'');
END;
$$
LANGUAGE plpgsql;

test-function.sh0000755000076400007640000000117411460466173013612 0ustar  gsmithgsmith#!/bin/bash
dropdb pgbench
createdb pgbench
pgbench -i -s 1 pgbench
psql -f setup-function.sql -d pgbench

# This should return 0 rows
psql -c "select * FROM pgbench_accounts WHERE aid > 100000 ORDER BY aid" -d pgbench

pgbench -f update-function.sql -t 10000 -c 8 -j 4 -s 2 pgbench
#psql -c "select * FROM pgbench_accounts WHERE NOT abalance=0 ORDER BY aid" -d pgbench
psql -c "select count(*) as updated FROM pgbench_accounts WHERE NOT abalance=0" -d pgbench
#psql -c "select * FROM pgbench_accounts WHERE aid > 100000 ORDER BY aid" -d pgbench
psql -c "select count(*) as inserted FROM pgbench_accounts WHERE aid > 100000" -d pgbench
test-merge.sh0000755000076400007640000000117111460466173013061 0ustar  gsmithgsmith#!/bin/bash
dropdb pgbench
createdb pgbench
pgbench -i -s 1 pgbench
psql -f setup-function.sql -d pgbench

# This should return 0 rows
psql -c "select * FROM pgbench_accounts WHERE aid > 100000 ORDER BY aid" -d pgbench

pgbench -f update-merge.sql -t 10000 -c 8 -j 4 -s 2 pgbench
#psql -c "select * FROM pgbench_accounts WHERE NOT abalance=0 ORDER BY aid" -d pgbench
psql -c "select count(*) as updated FROM pgbench_accounts WHERE NOT abalance=0" -d pgbench
#psql -c "select * FROM pgbench_accounts WHERE aid > 100000 ORDER BY aid" -d pgbench
psql -c "select count(*) as inserted FROM pgbench_accounts WHERE aid > 100000" -d pgbench
update-function.sql0000644000076400007640000000035211456654164014300 0ustar  gsmithgsmith\set nbranches :scale
\set ntellers 10 * :scale
\set naccounts 100000 * :scale
\setrandom aid 1 :naccounts
\setrandom bid 1 :nbranches
\setrandom tid 1 :ntellers
\setrandom delta -5000 5000
SELECT change_account_balance(:aid,:delta);
update-merge.sql0000644000076400007640000000070411460466173013547 0ustar  gsmithgsmith\set nbranches :scale
\set ntellers 10 * :scale
\set naccounts 100000 * :scale
\setrandom aid 1 :naccounts
\setrandom bid 1 :nbranches
\setrandom tid 1 :ntellers
\setrandom delta -5000 5000
MERGE INTO pgbench_accounts t USING (VALUES (:aid,1+(:aid / 1000000)::integer,:delta,'')) AS s(aid,bid,balance,filler) ON s.aid=t.aid WHEN MATCHED THEN UPDATE SET abalance=abalance + s.balance WHEN NOT MATCHED THEN INSERT VALUES(s.aid,s.bid,s.balance,s.filler);