Subject: Re: pgbench - adding pl/pgsql versions of tests

From: Hannu Krosing <[email-masked]>
Date: Sun, 6 Jul 2025 23:52:31 +0200

On Sat, Feb 3, 2024 at 8:54 AM Hannu Krosing <[email-masked]> wrote:
>
> My justification for adding pl/pgsql tests as part of the immediately available tests
> is that pl/pgsql itself is always enabled, so having a no-effort way to test its
> performance benefits would be really helpful.
> We also should have "tps-b-like as SQL function" to round up the "test what's available in server" set.

Finally got around to adding the tests for all out-of-the box
supported languages - pl/pgsql, and old and new SQL.

Also added the documentation.
From 72ede8812f61ed4879f5c005a18131c32ba2768b Mon Sep 17 00:00:00 2001
From: Hannu Krosing <[email-masked]>
Date: Sun, 6 Jul 2025 23:41:46 +0200
Subject: [PATCH v2] added new and old style SQL functions and documentation

---
 doc/src/sgml/ref/pgbench.sgml |  43 +++++++-
 src/bin/pgbench/pgbench.c     | 187 +++++++++++++++++++++++++++++++++-
 2 files changed, 223 insertions(+), 7 deletions(-)

diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index ab252d9fc74..6d733cff1af 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -105,8 +105,9 @@ pgbench -i <optional> <replaceable>other-options</replaceable> </optional> <repl
     <literal>pgbench -i</literal> creates four tables <structname>pgbench_accounts</structname>,
     <structname>pgbench_branches</structname>, <structname>pgbench_history</structname>, and
     <structname>pgbench_tellers</structname>,
-    destroying any existing tables of these names.
-    Be very careful to use another database if you have tables having these
+    destroying any existing tables of these names and their dependencies.
+    It also creates 6 functions starting with <structname>pgbench_</structname>.
+    Be very careful to use another database if you have tables or functions having these
     names!
    </para>
   </caution>
@@ -361,6 +362,15 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
       </listitem>
      </varlistentry>
 
+     <varlistentry id="pgbench-option-no-functions">
+      <term><option>--no-functions</option></term>
+      <listitem>
+       <para>
+        Do not create pl/pgsql and SQL functions for internal scripts.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry id="pgbench-option-partition-method">
       <term><option>--partition-method=<replaceable>NAME</replaceable></option></term>
       <listitem>
@@ -427,8 +437,35 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
         Available built-in scripts are: <literal>tpcb-like</literal>,
         <literal>simple-update</literal> and <literal>select-only</literal>.
         Unambiguous prefixes of built-in names are accepted.
+       </para>
+       <para>
+        Unless disabled with <literal>--no-functions</literal> option at database 
+        init the <literal>tpcb-like</literal> and <literal>simple-update</literal>
+        scripts are also implemented as User-Defined functions in the database which 
+        can be tested using scripts named <literal>plpgsql-tpcb-like</literal>, 
+        <literal>sqlfunc-tpcb-like</literal>, <literal>oldsqlf-tpcb-like</literal>, 
+        <literal>plpgsql-simple-update</literal>, <literal>sqlfunc-simple-update</literal>
+        and <literal>oldsqlf-simple-update</literal>.
+        The <literal>sqlfunc-*</literal> versions use the new SQL-standard SQL functions and 
+        the <literal>oldsqlf-*</literal> use the SQL functions defined using <literal>LANGUAGE SQL</literal>.
+        Use <literal>--show-script=scriptname</literal> to see what is actually run.
+       </para>
+       <para>
         With the special name <literal>list</literal>, show the list of built-in scripts
-        and exit immediately.
+        and exit immediately :
+<programlisting>
+$ pgbench -b list
+Available builtin scripts:
+              tpcb-like: <builtin: TPC-B (sort of)>
+      plpgsql-tpcb-like: <builtin: TPC-B (sort of) - pl/pgsql UDF>
+      sqlfunc-tpcb-like: <builtin: TPC-B (sort of) - 'BEGIN ATOMIC' SQL UDF>
+      oldsqlf-tpcb-like: <builtin: TPC-B (sort of) - LANGUAGE SQL UDF>
+          simple-update: <builtin: simple update>
+  plpgsql-simple-update: <builtin: simple update - pl/pgsql UDF>
+  sqlfunc-simple-update: <builtin: simple update - 'BEGIN ATOMIC' SQL UDF>
+  oldsqlf-simple-update: <builtin: simple update - LANGUAGE SQL UDF>
+            select-only: <builtin: select only>
+</programlisting>
        </para>
        <para>
         Optionally, write an integer weight after <literal>@</literal> to
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index 497a936c141..21d2fd64548 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -160,8 +160,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 */
@@ -796,6 +796,33 @@ 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) - pl/pgsql UDF>",
+		"\\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"
+	},
+	{
+		"sqlfunc-tpcb-like",
+		"<builtin: TPC-B (sort of) - 'BEGIN ATOMIC' SQL UDF>",
+		"\\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_sqlfunc(:aid, :bid, :tid, :delta);\n"
+	},
+	{
+		"oldsqlf-tpcb-like",
+		"<builtin: TPC-B (sort of) - LANGUAGE SQL UDF>",
+		"\\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_oldsqlfunc(:aid, :bid, :tid, :delta);\n"
+	},
 	{
 		"simple-update",
 		"<builtin: simple update>",
@@ -809,6 +836,33 @@ 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 - pl/pgsql UDF>",
+		"\\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"
+	},
+	{
+		"sqlfunc-simple-update",
+		"<builtin: simple update - 'BEGIN ATOMIC' SQL UDF>",
+		"\\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_sqlfunc(:aid, :bid, :tid, :delta);\n"
+	},
+	{
+		"oldsqlf-simple-update",
+		"<builtin: simple update - LANGUAGE SQL UDF>",
+		"\\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_oldsqlfunc(:aid, :bid, :tid, :delta);\n"
+	},
 	{
 		"select-only",
 		"<builtin: select only>",
@@ -915,6 +969,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 and SQL functions for internal scripts\n"
 		   "  --index-tablespace=TABLESPACE\n"
 		   "                           create indexes in the specified tablespace\n"
 		   "  --partition-method=(range|hash)\n"
@@ -4750,7 +4805,7 @@ initDropTables(PGconn *con)
 					 "pgbench_accounts, "
 					 "pgbench_branches, "
 					 "pgbench_history, "
-					 "pgbench_tellers");
+					 "pgbench_tellers cascade");
 }
 
 /*
@@ -4825,6 +4880,107 @@ createPartitions(PGconn *con)
 	termPQExpBuffer(&query);
 }
 
+/*
+ * 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");
+	if ((PQserverVersion(con) >= 140000))
+	{
+		executeStatement(con, 
+			"CREATE FUNCTION pgbench_tpcb_like_sqlfunc(_aid int, _bid int, _tid int, _delta int)\n"
+			"RETURNS void\n"
+			"BEGIN ATOMIC\n"
+			"    UPDATE pgbench_accounts SET abalance = abalance + _delta WHERE aid = _aid;\n"
+			"    SELECT 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");
+		executeStatement(con,
+			"CREATE FUNCTION pgbench_simple_update_sqlfunc(_aid int, _bid int, _tid int, _delta int)\n"
+			"RETURNS void\n"
+			"BEGIN ATOMIC\n"
+			"    UPDATE pgbench_accounts SET abalance = abalance + _delta WHERE aid = _aid;\n"
+			"    SELECT 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");
+	}
+	executeStatement(con, 
+		"CREATE FUNCTION pgbench_tpcb_like_oldsqlfunc(_aid int, _bid int, _tid int, _delta int)\n"
+		"RETURNS void\n"
+		"LANGUAGE sql\n"
+		"AS $sql$\n"
+		"-- BEGIN\n"
+		"    UPDATE pgbench_accounts SET abalance = abalance + _delta WHERE aid = _aid;\n"
+		"    SELECT 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"
+		"$sql$;\n");
+	executeStatement(con,
+		"CREATE FUNCTION pgbench_simple_update_oldsqlfunc(_aid int, _bid int, _tid int, _delta int)\n"
+		"RETURNS void\n"
+		"LANGUAGE sql\n"
+		"AS $sql$\n"
+		"-- BEGIN\n"
+		"    UPDATE pgbench_accounts SET abalance = abalance + _delta WHERE aid = _aid;\n"
+		"    SELECT 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"
+		"$sql$;\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");
+	executeStatement(con, 
+		"DROP FUNCTION IF EXISTS pgbench_tpcb_like_sqlfunc(_aid int, _bid int, _tid int, _delta int);\n");
+	executeStatement(con,
+		"DROP FUNCTION IF EXISTS pgbench_simple_update_sqlfunc(_aid int, _bid int, _tid int, _delta int);\n");
+	executeStatement(con, 
+		"DROP FUNCTION IF EXISTS pgbench_tpcb_like_oldsqlfunc(_aid int, _bid int, _tid int, _delta int);\n");
+	executeStatement(con,
+		"DROP FUNCTION IF EXISTS pgbench_simple_update_oldsqlfunc(_aid int, _bid int, _tid int, _delta int);\n");
+}
+
 /*
  * Create pgbench's standard tables
  */
@@ -5311,6 +5467,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:
@@ -6146,7 +6310,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");
 }
 
@@ -6705,6 +6869,7 @@ main(int argc, char **argv)
 		{"verbose-errors", no_argument, NULL, 15},
 		{"exit-on-abort", no_argument, NULL, 16},
 		{"debug", no_argument, NULL, 17},
+		{"no-functions", no_argument, NULL, 18},
 		{NULL, 0, NULL, 0}
 	};
 
@@ -6712,6 +6877,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;
@@ -7058,6 +7224,10 @@ main(int argc, char **argv)
 			case 17:			/* debug */
 				pg_logging_increase_verbosity();
 				break;
+			case 18:				/* 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);
@@ -7155,6 +7325,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 */
-- 
2.50.0.727.gbf7dc18ff4-goog

---

From: Hannu Krosing <[email-masked]>
Date: Wed, 23 Jul 2025 18:18:29 +0200

I had left plain < and > in code sample in documentation page, fixed now

---

From: Robert Treat <[email-masked]>
Date: Mon, 1 Sep 2025 09:28:33 -0400

> > Also added the documentation.

Hey Hannu,

I took the above for a spin and generally it all worked well and I do
think it is a nice addition. Attached v4 patch basically combines v3
01 and 02 patches into one (you need both or the build fails on the
docs, so...), along with the following changes:
- whitespace and typo fixes in pgbench.c
- wordsmithing the caution notification, clean up --no-functions doc
- document the new Yy options alongside other -I options.

On that last item, I did notice that there is a potential backwards
compatibility issue, which is that existing scripts that are reliant
on functions existing will need to be updated to include "y", but that
feels pretty niche, so I am not personally worried about it.

Robert Treat
https://xzilla.net