Background:
I tried to run the following transaction script on pgbench
contained in PostgreSQL 12:
\set nbranches 1 * :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 BEGIN; UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; SELECT abalance FROM pgbench_accounts WHERE aid = :aid; UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); END;
This script is equivalent of the TPC-B benchmark embedded in pgbench
.
Problem
I got following error when I tried to run the previous script with pgbench
command:
> .\pgbench.exe -U postgres -c 10 -t 1000 -f C:\tmp\insert.pgbench test C:\tmp\insert.pgbench:4: invalid command in command "setrandom" \setrandom aid 1 :naccounts
Cause
setrandom
meta command is not found in the document of PostgreSQL 9.6 *1 or later (the document of PostgreSQL 9.5 has a description *2 ). So it seems obsoleted in PostgreSQL 9.6.
Solution
Use random(min,max)
instead of \setrandom
. The following script is the equivalent for PostgreSQL 12:
\set nbranches 1 * :scale \set ntellers 10 * :scale \set naccounts 100000 * :scale -- \setrandom aid 1 :naccounts \set aid random(1,:naccounts) -- \setrandom bid 1 :nbranches \set bid random(1,:nbranches) -- \setrandom tid 1 :ntellers \set tid random(1,:ntellers) -- \setrandom delta -5000 5000 \set delta random(-5000,5000) BEGIN; UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; SELECT abalance FROM pgbench_accounts WHERE aid = :aid; UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); END;