-
Notifications
You must be signed in to change notification settings - Fork 10
/
tpcb-cockroach.sql
20 lines (18 loc) · 1.12 KB
/
tpcb-cockroach.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
\set aid random(1, 100000 * :scale)
\set bid random(1, 1 * :scale)
\set tid random(1, 10 * :scale)
\set delta random(-5000, 5000)
-- https://www.postgresql.org/docs/15/pgbench.html
-- What Is the “Transaction” Actually Performed in pgbench?
-- If you select the simple-update built-in (also -N), steps 4 and 5 aren't included in the transaction. This will avoid update contention on these tables, but it makes the test case even less like TPC-B.
-- If you select the select-only built-in (also -S), only the SELECT is issued.
WITH
update_pgbench_accounts AS
(UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid RETURNING abalance),
update_pgbench_tellers AS
(UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid RETURNING NULL),
update_pgbench_branches AS
(UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid RETURNING NULL),
insert_pgbench_history AS
(INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP) RETURNING NULL)
SELECT abalance FROM update_pgbench_accounts;