Hello devs,
The attached patch does $SUBJECT, as a showcase for recently added
features, including advanced expressions (CASE...), \if, \gset, ending SQL
commands at ";"...
There is also a small fix to the doc which describes the tpcb-like
implementation but gets one variable name wrong: balance -> delta.
--
Fabien.
diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index ee2501be55..29f4168e76 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -347,7 +347,8 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
An optional integer weight after <literal>@</literal> allows to adjust the
probability of drawing the script. If not specified, it is set to 1.
Available built-in scripts are: <literal>tpcb-like</literal>,
- <literal>simple-update</literal> and <literal>select-only</literal>.
+ <literal>simple-update</literal>, <literal>select-only</literal> and
+ <literal>strict-tpcb</literal>.
Unambiguous prefixes of built-in names are accepted.
With special name <literal>list</literal>, show the list of built-in scripts
and exit immediately.
@@ -843,7 +844,7 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
<para>
The default built-in transaction script (also invoked with <option>-b tpcb-like</option>)
issues seven commands per transaction over randomly chosen <literal>aid</literal>,
- <literal>tid</literal>, <literal>bid</literal> and <literal>balance</literal>.
+ <literal>tid</literal>, <literal>bid</literal> and <literal>delta</literal>.
The scenario is inspired by the TPC-B benchmark, but is not actually TPC-B,
hence the name.
</para>
@@ -869,6 +870,14 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
If you select the <literal>select-only</literal> built-in (also <option>-S</option>),
only the <command>SELECT</command> is issued.
</para>
+
+ <para>
+ If you select the <literal>strict-tpcb</literal> built-in, the SQL commands are similar
+ to <literal>tpcb-like</literal>, but the <literal>delta</literal> amount is 6 digits,
+ the account branch has a 15% probability to be in the same branch as the teller (unless
+ there is only one branch in which case it is always the same), and the final account
+ balance is actually extracted by the script.
+ </para>
</refsect2>
<refsect2>
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index 99529de52a..039dc32393 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -566,6 +566,47 @@ static const BuiltinScript builtin_script[] =
"<builtin: select only>",
"\\set aid random(1, " CppAsString2(naccounts) " * :scale)\n"
"SELECT abalance FROM pgbench_accounts WHERE aid = :aid;\n"
+ },
+ {
+ /*---
+ * Standard conforming TPC-B benchmark script:
+ *
+ * The account branch has a 15% probability to be the same as the
+ * teller branch.
+ * The amount is in [-999999, 999999].
+ * The final account balance is actually extracted by the driver.
+ */
+ "strict-tpcb",
+ "<builtin: strict TPC-B>",
+ /* choose teller and compute its branch */
+ "\\set tid random(1, " CppAsString2(ntellers) " * :scale)\n"
+ "\\set btid 1 + (:tid - 1) / (" CppAsString2(ntellers) " / " CppAsString2(nbranches) ")\n"
+ /* choose account branch: 85% different from teller unless there is only one branch */
+ "\\if :scale = 1 or random(1, 100) > 85\n"
+ "\\set bid :btid\n"
+ "\\else\n"
+ "\\set bid random(1, " CppAsString2(nbranches) " * :scale - 1)\n"
+ "\\set bid :bid + case when :bid >= :btid then 1 else 0 end\n"
+ "\\endif\n"
+ /* choose account within branch */
+ "\\set lid random(1, 100000)\n"
+ "\\set aid (:bid - 1) * " CppAsString2(naccounts) "/" CppAsString2(nbranches) " + :lid\n"
+ "\\set delta random(-999999, 999999)\n"
+ /* it should probably be combined, but that breaks -M prepared */
+ "BEGIN;\n"
+ "INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)\n"
+ " VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);\n"
+ "UPDATE pgbench_accounts\n"
+ " SET abalance = abalance + :delta\n"
+ " WHERE aid = :aid\n"
+ " RETURNING abalance\\gset\n"
+ "UPDATE pgbench_tellers\n"
+ " SET tbalance = tbalance + :delta\n"
+ " WHERE tid = :tid;\n"
+ "UPDATE pgbench_branches\n"
+ " SET bbalance = bbalance + :delta\n"
+ " WHERE bid = :bid;\n"
+ "END;\n"
}
};
