pgbench_default_vs_ins_log_3_rows.sh

#!/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