Hello Hackers,
The attached patch adds pl/pgsql versions of "tpcb-like" and
"simple-update" internal test scripts
The tests perform functionally exactly the same, but are generally
faster as they avoid most client-server latency.
The reason I'd like to have them as part of pgbench are two
1. so I don't have to create the script and function manually each
time I want to test mainly the database (instead of the
client-database system)
2. so that new users of PostgreSQL can easily see how much better OLTP
workloads perform when packaged up as a server-side function
The new user-visible functionalities are two new build-in scripts -b list :
$ pgbench -b list
Available builtin scripts:
tpcb-like: <builtin: TPC-B (sort of)>
plpgsql-tpcb-like: <builtin: TPC-B (sort of) as a pl/pgsql function>
simple-update: <builtin: simple update>
plpgsql-simple-update: <builtin: simple update as a pl/pgsql function>
select-only: <builtin: select only>
which one can run using the -b / --builtin= option
pgbench -b plpgsql-tpcb-like ...
or
pgbench -b plpgsql-simple-update ...
And a flag --no-functions which lets you not to create the functions at init
there are also character flags to -I / --init ,
-- Y to drop the functions and
-- y to create the functions. Creating is default behaviour, but can
be disabled fia long flag --no-functions )
I selected Yy as they were unused and can be thought of as "inverted
lambda symbol" :)
If there are no strong objections, I'll add it to the commitfest as well
-----
Hannu Krosing
Google Cloud - We have a long list of planned contributions and we are hiring.
Contact me if interested.
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index c4a44debeb..1edcec2f5c 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -172,8 +172,8 @@ typedef struct socket_set
/********************************************************************
* some configurable parameters */
-#define DEFAULT_INIT_STEPS "dtgvp" /* default -I setting */
-#define ALL_INIT_STEPS "dtgGvpf" /* all possible steps */
+#define DEFAULT_INIT_STEPS "dYtgvpy" /* default -I setting */
+#define ALL_INIT_STEPS "dYtgGvpfy" /* all possible steps */
#define LOG_STEP_SECONDS 5 /* seconds between log messages */
#define DEFAULT_NXACTS 10 /* default nxacts */
@@ -800,6 +800,15 @@ static const BuiltinScript builtin_script[] =
"INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);\n"
"END;\n"
},
+ {
+ "plpgsql-tpcb-like",
+ "<builtin: TPC-B (sort of) as a pl/pgsql function>",
+ "\\set aid random(1, " CppAsString2(naccounts) " * :scale)\n"
+ "\\set bid random(1, " CppAsString2(nbranches) " * :scale)\n"
+ "\\set tid random(1, " CppAsString2(ntellers) " * :scale)\n"
+ "\\set delta random(-5000, 5000)\n"
+ "SELECT 1 FROM pgbench_tpcb_like(:aid, :bid, :tid, :delta);\n"
+ },
{
"simple-update",
"<builtin: simple update>",
@@ -813,6 +822,15 @@ static const BuiltinScript builtin_script[] =
"INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);\n"
"END;\n"
},
+ {
+ "plpgsql-simple-update",
+ "<builtin: simple update as a pl/pgsql function>",
+ "\\set aid random(1, " CppAsString2(naccounts) " * :scale)\n"
+ "\\set bid random(1, " CppAsString2(nbranches) " * :scale)\n"
+ "\\set tid random(1, " CppAsString2(ntellers) " * :scale)\n"
+ "\\set delta random(-5000, 5000)\n"
+ "SELECT 1 FROM pgbench_simple_update(:aid, :bid, :tid, :delta);\n"
+ },
{
"select-only",
"<builtin: select only>",
@@ -885,6 +903,7 @@ usage(void)
" -q, --quiet quiet logging (one message each 5 seconds)\n"
" -s, --scale=NUM scaling factor\n"
" --foreign-keys create foreign key constraints between tables\n"
+ " --no-functions do not create pl/pgsql functions for internal scripts\n"
" --index-tablespace=TABLESPACE\n"
" create indexes in the specified tablespace\n"
" --partition-method=(range|hash)\n"
@@ -4836,6 +4855,54 @@ initTruncateTables(PGconn *con)
"pgbench_tellers");
}
+/*
+ * Create the functions needed for plpgsql-* builting scripts
+ */
+static void
+initCreateFuntions(PGconn *con)
+{
+ fprintf(stderr, "creating functions...\n");
+
+ executeStatement(con,
+ "CREATE FUNCTION pgbench_tpcb_like(_aid int, _bid int, _tid int, _delta int)\n"
+ "RETURNS void\n"
+ "LANGUAGE plpgsql\n"
+ "AS $plpgsql$\n"
+ "BEGIN\n"
+ " UPDATE pgbench_accounts SET abalance = abalance + _delta WHERE aid = _aid;\n"
+ " PERFORM abalance FROM pgbench_accounts WHERE aid = _aid;\n"
+ " UPDATE pgbench_tellers SET tbalance = tbalance + _delta WHERE tid = _tid;\n"
+ " UPDATE pgbench_branches SET bbalance = bbalance + _delta WHERE bid = _bid;\n"
+ " INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (_tid, _bid, _aid, _delta, CURRENT_TIMESTAMP);\n"
+ "END;\n"
+ "$plpgsql$;\n");
+ executeStatement(con,
+ "CREATE FUNCTION pgbench_simple_update(_aid int, _bid int, _tid int, _delta int)\n"
+ "RETURNS void\n"
+ "LANGUAGE plpgsql\n"
+ "AS $plpgsql$\n"
+ "BEGIN\n"
+ " UPDATE pgbench_accounts SET abalance = abalance + _delta WHERE aid = _aid;\n"
+ " PERFORM abalance FROM pgbench_accounts WHERE aid = _aid;\n"
+ " INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (_tid, _bid, _aid, _delta, CURRENT_TIMESTAMP);\n"
+ "END;\n"
+ "$plpgsql$;\n");
+}
+
+/*
+ * Remove old pgbench functions, if any exist
+ */
+static void
+initDropFunctions(PGconn *con)
+{
+ fprintf(stderr, "dropping old functions...\n");
+
+ executeStatement(con,
+ "DROP FUNCTION IF EXISTS pgbench_tpcb_like(_aid int, _bid int, _tid int, _delta int);\n");
+ executeStatement(con,
+ "DROP FUNCTION IF EXISTS pgbench_simple_update(_aid int, _bid int, _tid int, _delta int);\n");
+}
+
/*
* Fill the standard tables with some data generated and sent from the client
*/
@@ -5169,6 +5236,14 @@ runInitSteps(const char *initialize_steps)
op = "foreign keys";
initCreateFKeys(con);
break;
+ case 'Y':
+ op = "drop functions";
+ initDropFunctions(con);
+ break;
+ case 'y':
+ op = "create functions";
+ initCreateFuntions(con);
+ break;
case ' ':
break; /* ignore */
default:
@@ -6006,7 +6081,7 @@ listAvailableScripts(void)
fprintf(stderr, "Available builtin scripts:\n");
for (i = 0; i < lengthof(builtin_script); i++)
- fprintf(stderr, " %13s: %s\n", builtin_script[i].name, builtin_script[i].desc);
+ fprintf(stderr, " %21s: %s\n", builtin_script[i].name, builtin_script[i].desc);
fprintf(stderr, "\n");
}
@@ -6556,6 +6631,7 @@ main(int argc, char **argv)
{"failures-detailed", no_argument, NULL, 13},
{"max-tries", required_argument, NULL, 14},
{"verbose-errors", no_argument, NULL, 15},
+ {"no-functions", no_argument, NULL, 16},
{NULL, 0, NULL, 0}
};
@@ -6563,6 +6639,7 @@ main(int argc, char **argv)
bool is_init_mode = false; /* initialize mode? */
char *initialize_steps = NULL;
bool foreign_keys = false;
+ bool no_functions = false;
bool is_no_vacuum = false;
bool do_vacuum_accounts = false; /* vacuum accounts table? */
int optindex;
@@ -6898,6 +6975,10 @@ main(int argc, char **argv)
benchmarking_option_set = true;
verbose_errors = true;
break;
+ case 16: /* no-functions */
+ initialization_option_set = true;
+ no_functions = true;
+ break;
default:
/* getopt_long already emitted a complaint */
pg_log_error_hint("Try \"%s --help\" for more information.", progname);
@@ -6992,6 +7073,15 @@ main(int argc, char **argv)
*p = ' ';
}
+ if (no_functions)
+ {
+ /* Remove create function step in initialize_steps */
+ char *p;
+
+ while ((p = strchr(initialize_steps, 'y')) != NULL)
+ *p = ' ';
+ }
+
if (foreign_keys)
{
/* Add 'f' to end of initialize_steps, if not already there */
