Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
#!/bin/bash
SCALE=100
LOOPS=3
DURATION=7200
TEST_NAME=pgbench_default_vs_ins_3_rows
export PGDATABASE=postgres
export PGHOST=localhost
export PGPORT=5432
export PGUSER=$USER
for loop in $(seq 1 ${LOOPS}) ; do
echo "doing loop $loop ..."
###################
# pgbench default
###################
echo "pgbench -i -s $SCALE &>/dev/null"
pgbench -i -s $SCALE $PGDATABASE &>/dev/null
psql -c "checkpoint"
pgbench -T $DURATION --random-seed=2018 $PGDATABASE &> ${TEST_NAME}_run_${loop}_no_trg.log
###################
# 3 plpgsql insert triggers
###################
echo "pgbench -i -s $SCALE &>/dev/null"
pgbench -i -s $SCALE $PGDATABASE &>/dev/null
psql -c "checkpoint"
TRG_FUNC=$(cat <<HERE
CREATE OR REPLACE FUNCTION trg_audit_accounts() RETURNS TRIGGER
AS
\$SQL\$
BEGIN
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (NULL, NEW.bid, NEW.aid, NEW.abalance, CURRENT_TIMESTAMP);
RETURN NEW;
end;
\$SQL\$ LANGUAGE plpgsql;
HERE
)
psql -c "$TRG_FUNC"
psql -c "CREATE TRIGGER audit BEFORE UPDATE ON pgbench_accounts FOR EACH ROW EXECUTE PROCEDURE trg_audit_accounts();"
psql -c "DROP TABLE IF EXISTS pgbench_branches_history"
psql -c "CREATE TABLE pgbench_branches_history (bid int, bbalance int, mtime timestamp)"
TRG_FUNC=$(cat <<HERE
CREATE OR REPLACE FUNCTION trg_audit_branches() RETURNS TRIGGER
AS
\$SQL\$
BEGIN
INSERT INTO pgbench_branches_history (bid, bbalance, mtime) VALUES (NEW.bid, NEW.bbalance, CURRENT_TIMESTAMP);
RETURN NEW;
end;
\$SQL\$ LANGUAGE plpgsql;
HERE
)
psql -c "$TRG_FUNC"
psql -c "CREATE TRIGGER audit BEFORE UPDATE ON pgbench_branches FOR EACH ROW EXECUTE PROCEDURE trg_audit_branches();"
psql -c "DROP TABLE IF EXISTS pgbench_tellers_history"
psql -c "CREATE TABLE pgbench_tellers_history (tid int, bid int, tbalance int, mtime timestamp)"
TRG_FUNC=$(cat <<HERE
CREATE OR REPLACE FUNCTION trg_audit_tellers() RETURNS TRIGGER
AS
\$SQL\$
BEGIN
INSERT INTO pgbench_tellers_history (tid, bid, tbalance, mtime) VALUES (NEW.tid, NEW.bid, NEW.tbalance, CURRENT_TIMESTAMP);
RETURN NEW;
end;
\$SQL\$ LANGUAGE plpgsql;
HERE
)
psql -c "$TRG_FUNC"
psql -c "CREATE TRIGGER audit BEFORE UPDATE ON pgbench_tellers FOR EACH ROW EXECUTE PROCEDURE trg_audit_tellers();"
pgbench -T $DURATION --random-seed=2018 $PGDATABASE &> ${TEST_NAME}_run_${loop}_plpgsql_trg.log
done
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment