Lazy Diary @ Hatena Blog

PowerShell / Java / miscellaneous things about software development, Tips & Gochas. CC BY-SA 4.0/Apache License 2.0

An error in pgbench: invalid command in command "setrandom"

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;