public inbox for [email protected]
help / color / mirror / Atom feedRe: [EXTERNAL] Re: [PATCH] Support using "all" for the db user in pg_ident.conf
12+ messages / 6 participants
[nested] [flat]
* Re: [EXTERNAL] Re: [PATCH] Support using "all" for the db user in pg_ident.conf
@ 2023-01-19 11:23 Jelte Fennema <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Jelte Fennema @ 2023-01-19 11:23 UTC (permalink / raw)
To: Michael Paquier <[email protected]>; +Cc: Jelte Fennema <[email protected]>; [email protected] <[email protected]>; [email protected] <[email protected]>; Nathan Bossart <[email protected]>; Andrew Dunstan <[email protected]>
Looks good to me. One tiny typo in a comment that I noticed when going
over the diff:
+ * Mark the token as quoted, so it will only be compared literally
+ * and not for some special meaning, such as "all" or a group
+ * membership checks.
should be either:
1. a group membership check
2. group membership checks
Now it's mixed singular and plural.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [EXTERNAL] Re: [PATCH] Support using "all" for the db user in pg_ident.conf
@ 2023-01-20 02:26 Michael Paquier <[email protected]>
parent: Jelte Fennema <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Michael Paquier @ 2023-01-20 02:26 UTC (permalink / raw)
To: Jelte Fennema <[email protected]>; +Cc: Jelte Fennema <[email protected]>; [email protected] <[email protected]>; [email protected] <[email protected]>; Nathan Bossart <[email protected]>; Andrew Dunstan <[email protected]>
On Thu, Jan 19, 2023 at 12:23:16PM +0100, Jelte Fennema wrote:
> should be either:
> 1. a group membership check
> 2. group membership checks
>
> Now it's mixed singular and plural.
Thanks, fixed. And now applied the last patch.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc)
download
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [EXTERNAL] Re: [PATCH] Support using "all" for the db user in pg_ident.conf
@ 2023-02-13 14:06 Pavel Luzanov <[email protected]>
parent: Michael Paquier <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Pavel Luzanov @ 2023-02-13 14:06 UTC (permalink / raw)
To: Michael Paquier <[email protected]>; Jelte Fennema <[email protected]>; +Cc: Jelte Fennema <[email protected]>; [email protected] <[email protected]>; [email protected] <[email protected]>; Nathan Bossart <[email protected]>; Andrew Dunstan <[email protected]>
Hello,
Playing with this patch, I did not see descriptive comments in
pg_ident.conf.
Does it make sense to reflect changes to the PG-USERNAME field in the
pg_ident.conf.sample file?
The same relates to the regexp supportin the DATABASE and USER fieldsof
the pg_hba.conf.sample file(8fea8683).
-----
Pavel Luzanov
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [EXTERNAL] Re: [PATCH] Support using "all" for the db user in pg_ident.conf
@ 2023-02-13 14:13 Jelte Fennema <[email protected]>
parent: Pavel Luzanov <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Jelte Fennema @ 2023-02-13 14:13 UTC (permalink / raw)
To: Pavel Luzanov <[email protected]>; +Cc: Michael Paquier <[email protected]>; Jelte Fennema <[email protected]>; [email protected] <[email protected]>; [email protected] <[email protected]>; Nathan Bossart <[email protected]>; Andrew Dunstan <[email protected]>
On Mon, 13 Feb 2023 at 15:06, Pavel Luzanov <[email protected]> wrote:
> Does it make sense to reflect changes to the PG-USERNAME field in the
> pg_ident.conf.sample file?
>
> The same relates to the regexp supportin the DATABASE and USER fieldsof
> the pg_hba.conf.sample file(8fea8683).
That definitely makes sense to me. When writing the patch I didn't
realise that there was also documentation in those files.
I think it also makes sense to include usage of (some of) the features
in the example files here:
https://www.postgresql.org/docs/devel/auth-username-maps.html
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [EXTERNAL] Re: [PATCH] Support using "all" for the db user in pg_ident.conf
@ 2023-02-15 07:11 Michael Paquier <[email protected]>
parent: Jelte Fennema <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Michael Paquier @ 2023-02-15 07:11 UTC (permalink / raw)
To: Jelte Fennema <[email protected]>; +Cc: Pavel Luzanov <[email protected]>; Jelte Fennema <[email protected]>; [email protected] <[email protected]>; [email protected] <[email protected]>; Nathan Bossart <[email protected]>; Andrew Dunstan <[email protected]>
On Mon, Feb 13, 2023 at 03:13:04PM +0100, Jelte Fennema wrote:
> On Mon, 13 Feb 2023 at 15:06, Pavel Luzanov <[email protected]> wrote:
>> Does it make sense to reflect changes to the PG-USERNAME field in the
>> pg_ident.conf.sample file?
>>
>> The same relates to the regexp supportin the DATABASE and USER fieldsof
>> the pg_hba.conf.sample file(8fea8683).
Which comes down to blame me for both of them.
> That definitely makes sense to me. When writing the patch I didn't
> realise that there was also documentation in those files.
>
> I think it also makes sense to include usage of (some of) the features
> in the example files here:
> https://www.postgresql.org/docs/devel/auth-username-maps.html
Hmm, I am not sure that adding more examples in the sample files is
worth the duplication with the docs.
So, please find attached a patch to close the gap the sample files,
for both things, with descriptions of all the field values they can
use.
What do you think?
--
Michael
Attachments:
[text/x-diff] sample-descriptions.patch (2.1K, ../../[email protected]/2-sample-descriptions.patch)
download | inline diff:
diff --git a/src/backend/libpq/pg_hba.conf.sample b/src/backend/libpq/pg_hba.conf.sample
index 095e3b4cc0..fc448b5b7e 100644
--- a/src/backend/libpq/pg_hba.conf.sample
+++ b/src/backend/libpq/pg_hba.conf.sample
@@ -31,14 +31,15 @@
# - "hostnogssenc" is a TCP/IP socket that is not GSSAPI-encrypted
#
# DATABASE can be "all", "sameuser", "samerole", "replication", a
-# database name, or a comma-separated list thereof. The "all"
-# keyword does not match "replication". Access to replication
-# must be enabled in a separate record (see example below).
+# database name, a regular expression (if it starts with a slash (/))
+# or a comma-separated list thereof. The "all" keyword does not match
+# "replication". Access to replication must be enabled in a separate
+# record (see example below).
#
-# USER can be "all", a user name, a group name prefixed with "+", or a
-# comma-separated list thereof. In both the DATABASE and USER fields
-# you can also write a file name prefixed with "@" to include names
-# from a separate file.
+# USER can be "all", a user name, a group name prefixed with "+", a
+# regular expression (if it starts with a slash (/)) or a comma-separated
+# list thereof. In both the DATABASE and USER fields you can also write
+# a file name prefixed with "@" to include names from a separate file.
#
# ADDRESS specifies the set of hosts the record matches. It can be a
# host name, or it is made up of an IP address and a CIDR mask that is
diff --git a/src/backend/libpq/pg_ident.conf.sample b/src/backend/libpq/pg_ident.conf.sample
index 5d32684b28..f5225f26cd 100644
--- a/src/backend/libpq/pg_ident.conf.sample
+++ b/src/backend/libpq/pg_ident.conf.sample
@@ -29,6 +29,10 @@
# will be substituted for \1 (backslash-one) if present in
# PG-USERNAME.
#
+# PG-USERNAME can be "all", a user name, a group name prefixed with "+", or
+# a regular expression (if it starts with a slash (/)). If it is a regular
+# expression, the substring matching with \1 has no effect.
+#
# Multiple maps may be specified in this file and used by pg_hba.conf.
#
# No map names are defined in the default configuration. If all
[application/pgp-signature] signature.asc (833B, ../../[email protected]/3-signature.asc)
download
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [EXTERNAL] Re: [PATCH] Support using "all" for the db user in pg_ident.conf
@ 2023-02-15 10:05 Pavel Luzanov <[email protected]>
parent: Michael Paquier <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Pavel Luzanov @ 2023-02-15 10:05 UTC (permalink / raw)
To: Michael Paquier <[email protected]>; Jelte Fennema <[email protected]>; +Cc: Jelte Fennema <[email protected]>; [email protected] <[email protected]>; [email protected] <[email protected]>; Nathan Bossart <[email protected]>; Andrew Dunstan <[email protected]>
On 15.02.2023 10:11, Michael Paquier wrote:
> Which comes down to blame me for both of them.
My only intention was to make postgres better.I'm sorry you took it that
way.
> So, please find attached a patch to close the gap the sample files,
> for both things, with descriptions of all the field values they can
> use.
A short and precise description. Nothing to add.Next time I will try to
offer a patch at once.
-----
Pavel Luzanov
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [EXTERNAL] Re: [PATCH] Support using "all" for the db user in pg_ident.conf
@ 2023-02-15 10:25 Michael Paquier <[email protected]>
parent: Pavel Luzanov <[email protected]>
0 siblings, 0 replies; 12+ messages in thread
From: Michael Paquier @ 2023-02-15 10:25 UTC (permalink / raw)
To: Pavel Luzanov <[email protected]>; +Cc: Jelte Fennema <[email protected]>; Jelte Fennema <[email protected]>; [email protected] <[email protected]>; [email protected] <[email protected]>; Nathan Bossart <[email protected]>; Andrew Dunstan <[email protected]>
On Wed, Feb 15, 2023 at 01:05:04PM +0300, Pavel Luzanov wrote:
> On 15.02.2023 10:11, Michael Paquier wrote:
>> Which comes down to blame me for both of them.
>
> My only intention was to make postgres better.I'm sorry you took it that
> way.
You have no need to feel sorry about that. I really appreciate that
you took the time to report this issue, so don't worry. My point is
that I have committed this code, so basically it is my responsibility
to take care of its maintenance.
>> So, please find attached a patch to close the gap the sample files,
>> for both things, with descriptions of all the field values they can
>> use.
>
> A short and precise description. Nothing to add.Next time I will try to
> offer a patch at once.
If you have a proposal of patch, that's always nice to have, but you
should not feel obliged to do so, either.
Thanks a lot for the report, Pavel!
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc)
download
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pgbench - adding pl/pgsql versions of tests
@ 2025-07-06 21:52 Hannu Krosing <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Hannu Krosing @ 2025-07-06 21:52 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Nathan Bossart <[email protected]>; Fabien COELHO <[email protected]>; pgsql-hackers
On Sat, Feb 3, 2024 at 8:54 AM Hannu Krosing <[email protected]> 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.
Attachments:
[text/x-patch] v2-0001-added-new-and-old-style-SQL-functions-and-documen.patch (14.7K, ../../CAMT0RQTeYHV+kpdaKMZJpa0gZMQAKCDKSTsq+haeMN=L3X0Pjg@mail.gmail.com/2-v2-0001-added-new-and-old-style-SQL-functions-and-documen.patch)
download | inline diff:
From 72ede8812f61ed4879f5c005a18131c32ba2768b Mon Sep 17 00:00:00 2001
From: Hannu Krosing <[email protected]>
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pgbench - adding pl/pgsql versions of tests
@ 2025-07-23 16:18 Hannu Krosing <[email protected]>
parent: Hannu Krosing <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Hannu Krosing @ 2025-07-23 16:18 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Nathan Bossart <[email protected]>; Fabien COELHO <[email protected]>; pgsql-hackers
I had left plain < and > in code sample in documentation page, fixed now
On Sun, Jul 6, 2025 at 11:52 PM Hannu Krosing <[email protected]> wrote:
>
> On Sat, Feb 3, 2024 at 8:54 AM Hannu Krosing <[email protected]> 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.
Attachments:
[application/x-patch] v3-0001-added-new-and-old-style-SQL-functions-and-documen.patch (14.7K, ../../CAMT0RQSCsf9TTtoMrDpA5T3N64HMprz+BnBRmGgMp7JHSk1wog@mail.gmail.com/2-v3-0001-added-new-and-old-style-SQL-functions-and-documen.patch)
download | inline diff:
From 72ede8812f61ed4879f5c005a18131c32ba2768b Mon Sep 17 00:00:00 2001
From: Hannu Krosing <[email protected]>
Date: Sun, 6 Jul 2025 23:41:46 +0200
Subject: [PATCH v3 1/2] 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
[application/x-patch] v3-0002-Fixed-pleaving-plain-in-code-sample-in.patch (1.9K, ../../CAMT0RQSCsf9TTtoMrDpA5T3N64HMprz+BnBRmGgMp7JHSk1wog@mail.gmail.com/3-v3-0002-Fixed-pleaving-plain-in-code-sample-in.patch)
download | inline diff:
From f333fe194863b07ba905c4c5706266d6167ad140 Mon Sep 17 00:00:00 2001
From: Hannu Krosing <[email protected]>
Date: Wed, 23 Jul 2025 12:20:32 +0200
Subject: [PATCH v3 2/2] Fixed pleaving plain < > in code sample in
---
doc/src/sgml/ref/pgbench.sgml | 18 +++++++++---------
1 file changed, 9 insertions(+), 9 deletions(-)
diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index 6d733cff1af..4502c8eaba0 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -456,15 +456,15 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
<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>
+ 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>
--
2.50.0.727.gbf7dc18ff4-goog
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pgbench - adding pl/pgsql versions of tests
@ 2025-09-01 13:28 Robert Treat <[email protected]>
parent: Hannu Krosing <[email protected]>
0 siblings, 0 replies; 12+ messages in thread
From: Robert Treat @ 2025-09-01 13:28 UTC (permalink / raw)
To: Hannu Krosing <[email protected]>; +Cc: Robert Haas <[email protected]>; Nathan Bossart <[email protected]>; Fabien COELHO <[email protected]>; pgsql-hackers
On Wed, Jul 23, 2025 at 12:18 PM Hannu Krosing <[email protected]> wrote:
> On Sun, Jul 6, 2025 at 11:52 PM Hannu Krosing <[email protected]> wrote:
> > On Sat, Feb 3, 2024 at 8:54 AM Hannu Krosing <[email protected]> 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.
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
Attachments:
[application/octet-stream] v4-0001-added-new-and-old-style-SQL-functions-and-documen.patch (17.1K, ../../CABV9wwP9+shGotYw0t--miae0jBzZoYhYXuTsbB1qPJdY2mdZA@mail.gmail.com/2-v4-0001-added-new-and-old-style-SQL-functions-and-documen.patch)
download | inline diff:
From b9850ac2f99dbb253ab5365f8ed26f533687b860 Mon Sep 17 00:00:00 2001
From: Robert Treat <[email protected]>
Date: Mon, 1 Sep 2025 08:40:01 -0400
Subject: [PATCH v4] added-new-and-old-style-SQL-functions-and-documentation
Content-Type: text/plain; charset="utf-8"
---
doc/src/sgml/ref/pgbench.sgml | 74 ++++++++++++--
src/bin/pgbench/pgbench.c | 187 +++++++++++++++++++++++++++++++++-
2 files changed, 247 insertions(+), 14 deletions(-)
diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index ab252d9fc74..959172de9b9 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -102,12 +102,12 @@ pgbench -i <optional> <replaceable>other-options</replaceable> </optional> <repl
<caution>
<para>
- <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
- names!
+ <literal>pgbench -i</literal> creates four tables (<structname>pgbench_accounts</structname>,
+ <structname>pgbench_branches</structname>, <structname>pgbench_history</structname>,
+ and <structname>pgbench_tellers</structname>) and six functions with names
+ begining with <structname>pgbench_</structname>. This operation will drop
+ any existing tables or functions with these names, including all dependent
+ objects.
</para>
</caution>
@@ -193,18 +193,26 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
<replaceable>init_steps</replaceable> specifies the
initialization steps to be performed, using one character per step.
Each step is invoked in the specified order.
- The default is <literal>dtgvp</literal>.
+ The default is <literal>dYtgvpy</literal>.
The available steps are:
<variablelist>
<varlistentry id="pgbench-option-init-steps-d">
- <term><literal>d</literal> (Drop)</term>
+ <term><literal>d</literal> (Drop Tables)</term>
<listitem>
<para>
Drop any existing <application>pgbench</application> tables.
</para>
</listitem>
</varlistentry>
+ <varlistentry id="pgbench-option-init-steps-Y">
+ <term><literal>Y</literal> (Drop Functions)</term>
+ <listitem>
+ <para>
+ Drop any existing <application>pgbench</application> functions.
+ </para>
+ </listitem>
+ </varlistentry>
<varlistentry id="pgbench-option-init-steps-t">
<term><literal>t</literal> (create Tables)</term>
<listitem>
@@ -269,7 +277,15 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
</para>
</listitem>
</varlistentry>
- <varlistentry id="pgbench-option-init-steps-f">
+ <varlistentry id="pgbench-option-init-steps-y">
+ <term><literal>y</literal> (create Functions)</term>
+ <listitem>
+ <para>
+ Create any neccessary <application>pgbench</application> functions.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry id="pgbench-option-init-steps-f">
<term><literal>f</literal> (create Foreign keys)</term>
<listitem>
<para>
@@ -361,6 +377,17 @@ 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 or SQL functions for internal scripts.
+ (This option suppresses the <literal>y</literal> initialization step,
+ even if it was specified in <option>-I</option>.)
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="pgbench-option-partition-method">
<term><option>--partition-method=<replaceable>NAME</replaceable></option></term>
<listitem>
@@ -427,8 +454,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 the <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 125f3c7bbbe..a3d951bce87 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>",
@@ -917,6 +971,7 @@ usage(void)
" --foreign-keys create foreign key constraints between tables\n"
" --index-tablespace=TABLESPACE\n"
" create indexes in the specified tablespace\n"
+ " --no-functions do not create pl/pgsql or SQL functions for internal scripts\n"
" --partition-method=(range|hash)\n"
" partition pgbench_accounts with this method (default: range)\n"
" --partitions=NUM partition pgbench_accounts into NUM parts (default: 0)\n"
@@ -4763,7 +4818,7 @@ initDropTables(PGconn *con)
"pgbench_accounts, "
"pgbench_branches, "
"pgbench_history, "
- "pgbench_tellers");
+ "pgbench_tellers cascade");
}
/*
@@ -4838,6 +4893,107 @@ createPartitions(PGconn *con)
termPQExpBuffer(&query);
}
+/*
+ * Create the functions needed for plpgsql-* builtin scripts
+ */
+static void
+initCreateFunctions(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
*/
@@ -5324,6 +5480,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";
+ initCreateFunctions(con);
+ break;
case ' ':
break; /* ignore */
default:
@@ -6159,7 +6323,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");
}
@@ -6718,6 +6882,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}
};
@@ -6725,6 +6890,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;
@@ -7071,6 +7237,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);
@@ -7168,6 +7338,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.24.3 (Apple Git-128)
^ permalink raw reply [nested|flat] 12+ messages in thread
* [PATCH v3] Fix test_custom_stats modules to error out when not preloaded
@ 2026-07-02 04:34 Bertrand Drouvot <[email protected]>
0 siblings, 0 replies; 12+ messages in thread
From: Bertrand Drouvot @ 2026-07-02 04:34 UTC (permalink / raw)
Previously, test_custom_var_stats and test_custom_fixed_stats silently
skipped pgstat_register_kind() when not loaded via shared_preload_libraries.
This left the SQL functions callable without the kind registered, leading to NULL
dereferences in core pgstat functions.
Remove the early return and let pgstat_register_kind() error out naturally,
matching the pattern used by test_custom_rmgrs with RegisterCustomRmgr(). Now
CREATE EXTENSION fails with a clear error if the module is not preloaded.
Author: Bertrand Drouvot <[email protected]>
Reviewed-by: Ewan Young <[email protected]>
Discussion: https://postgr.es/m/akS/ldidWeqG1FWk%40bdtpg
---
.../modules/test_custom_stats/test_custom_fixed_stats.c | 9 ++++-----
.../modules/test_custom_stats/test_custom_var_stats.c | 9 ++++-----
2 files changed, 8 insertions(+), 10 deletions(-)
100.0% src/test/modules/test_custom_stats/
diff --git a/src/test/modules/test_custom_stats/test_custom_fixed_stats.c b/src/test/modules/test_custom_stats/test_custom_fixed_stats.c
index a066ce117a6..8b29ca3e27a 100644
--- a/src/test/modules/test_custom_stats/test_custom_fixed_stats.c
+++ b/src/test/modules/test_custom_stats/test_custom_fixed_stats.c
@@ -72,11 +72,10 @@ static const PgStat_KindInfo custom_stats = {
void
_PG_init(void)
{
- /* Must be loaded via shared_preload_libraries */
- if (!process_shared_preload_libraries_in_progress)
- return;
-
- /* Register custom statistics kind */
+ /*
+ * In order to register our custom statistics kind, we have to be loaded
+ * via shared_preload_libraries. Otherwise, registration will fail.
+ */
pgstat_register_kind(PGSTAT_KIND_TEST_CUSTOM_FIXED_STATS, &custom_stats);
}
diff --git a/src/test/modules/test_custom_stats/test_custom_var_stats.c b/src/test/modules/test_custom_stats/test_custom_var_stats.c
index 863d6a52492..fa415c81301 100644
--- a/src/test/modules/test_custom_stats/test_custom_var_stats.c
+++ b/src/test/modules/test_custom_stats/test_custom_var_stats.c
@@ -129,11 +129,10 @@ static const PgStat_KindInfo custom_stats = {
void
_PG_init(void)
{
- /* Must be loaded via shared_preload_libraries */
- if (!process_shared_preload_libraries_in_progress)
- return;
-
- /* Register custom statistics kind */
+ /*
+ * In order to register our custom statistics kind, we have to be loaded
+ * via shared_preload_libraries. Otherwise, registration will fail.
+ */
pgstat_register_kind(PGSTAT_KIND_TEST_CUSTOM_VAR_STATS, &custom_stats);
}
--
2.34.1
--i51FQWZELabmlh2Y--
^ permalink raw reply [nested|flat] 12+ messages in thread
* [PATCH v3] Fix test_custom_stats modules to error out when not preloaded
@ 2026-07-02 04:34 Bertrand Drouvot <[email protected]>
0 siblings, 0 replies; 12+ messages in thread
From: Bertrand Drouvot @ 2026-07-02 04:34 UTC (permalink / raw)
Previously, test_custom_var_stats and test_custom_fixed_stats silently
skipped pgstat_register_kind() when not loaded via shared_preload_libraries.
This left the SQL functions callable without the kind registered, leading to NULL
dereferences in core pgstat functions.
Remove the early return and let pgstat_register_kind() error out naturally,
matching the pattern used by test_custom_rmgrs with RegisterCustomRmgr(). Now
CREATE EXTENSION fails with a clear error if the module is not preloaded.
Author: Bertrand Drouvot <[email protected]>
Reviewed-by: Ewan Young <[email protected]>
Discussion: https://postgr.es/m/akS/ldidWeqG1FWk%40bdtpg
---
.../modules/test_custom_stats/test_custom_fixed_stats.c | 9 ++++-----
.../modules/test_custom_stats/test_custom_var_stats.c | 9 ++++-----
2 files changed, 8 insertions(+), 10 deletions(-)
100.0% src/test/modules/test_custom_stats/
diff --git a/src/test/modules/test_custom_stats/test_custom_fixed_stats.c b/src/test/modules/test_custom_stats/test_custom_fixed_stats.c
index a066ce117a6..8b29ca3e27a 100644
--- a/src/test/modules/test_custom_stats/test_custom_fixed_stats.c
+++ b/src/test/modules/test_custom_stats/test_custom_fixed_stats.c
@@ -72,11 +72,10 @@ static const PgStat_KindInfo custom_stats = {
void
_PG_init(void)
{
- /* Must be loaded via shared_preload_libraries */
- if (!process_shared_preload_libraries_in_progress)
- return;
-
- /* Register custom statistics kind */
+ /*
+ * In order to register our custom statistics kind, we have to be loaded
+ * via shared_preload_libraries. Otherwise, registration will fail.
+ */
pgstat_register_kind(PGSTAT_KIND_TEST_CUSTOM_FIXED_STATS, &custom_stats);
}
diff --git a/src/test/modules/test_custom_stats/test_custom_var_stats.c b/src/test/modules/test_custom_stats/test_custom_var_stats.c
index 863d6a52492..fa415c81301 100644
--- a/src/test/modules/test_custom_stats/test_custom_var_stats.c
+++ b/src/test/modules/test_custom_stats/test_custom_var_stats.c
@@ -129,11 +129,10 @@ static const PgStat_KindInfo custom_stats = {
void
_PG_init(void)
{
- /* Must be loaded via shared_preload_libraries */
- if (!process_shared_preload_libraries_in_progress)
- return;
-
- /* Register custom statistics kind */
+ /*
+ * In order to register our custom statistics kind, we have to be loaded
+ * via shared_preload_libraries. Otherwise, registration will fail.
+ */
pgstat_register_kind(PGSTAT_KIND_TEST_CUSTOM_VAR_STATS, &custom_stats);
}
--
2.34.1
--i51FQWZELabmlh2Y--
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2026-07-02 04:34 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-01-19 11:23 Re: [EXTERNAL] Re: [PATCH] Support using "all" for the db user in pg_ident.conf Jelte Fennema <[email protected]>
2023-01-20 02:26 ` Michael Paquier <[email protected]>
2023-02-13 14:06 ` Pavel Luzanov <[email protected]>
2023-02-13 14:13 ` Jelte Fennema <[email protected]>
2023-02-15 07:11 ` Michael Paquier <[email protected]>
2023-02-15 10:05 ` Pavel Luzanov <[email protected]>
2023-02-15 10:25 ` Michael Paquier <[email protected]>
2025-07-06 21:52 Re: pgbench - adding pl/pgsql versions of tests Hannu Krosing <[email protected]>
2025-07-23 16:18 ` Re: pgbench - adding pl/pgsql versions of tests Hannu Krosing <[email protected]>
2025-09-01 13:28 ` Re: pgbench - adding pl/pgsql versions of tests Robert Treat <[email protected]>
2026-07-02 04:34 [PATCH v3] Fix test_custom_stats modules to error out when not preloaded Bertrand Drouvot <[email protected]>
2026-07-02 04:34 [PATCH v3] Fix test_custom_stats modules to error out when not preloaded Bertrand Drouvot <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox