pgbench - adding pl/pgsql versions of tests

Hello,

The attached patch adds pl/pgsql versions of "tpcb-like" and
"simple-update" internal test scripts

Why not, it makes sense because it is relevant to some usage patterns.

Why not having the select version as a version as well?

If we are going to follow this road, we could also consider
"combined" queries with \; as well?

$ 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

ISTM that the -b had a fast selection so that only a prefix was enough to
select a script (-b se = -b select-only). Maybe such convenient shortcut
should be preserved, it seems that the long name will be needed for the pl
versions.

And a flag --no-functions which lets you not to create the functions at init

Hmmm. Not so sure.

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 )

Ok.

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

Please do that.

--
Fabien Coelho.

The following review has been posted through the commitfest application:
make installcheck-world: not tested
Implements feature: tested, passed
Spec compliant: not tested
Documentation: not tested

Hi

thank you for the patch. It can be applied to current master branch and compiled fine.

The feature works as described, I am able to run plpgsql-tpcb-like and plpgsql-simple-update scripts as you have added them.

But I am not sure the purpose of --no-function to prevent the creation of pl/pgsql functions when the new plpgsql test scripts need them.

I initialized pgbench database with --no-function, and plpgsql-tpcb-like and plpgsql-simple-update scripts will fail to run

thanks

Cary Huang
===============
Highgo Software Canada
www.highgo.ca

On Fri, 24 Mar 2023 22:17:33 +0000
Cary Huang <cary.huang@highgo.ca> wrote:

The following review has been posted through the commitfest application:
make installcheck-world: not tested
Implements feature: tested, passed
Spec compliant: not tested
Documentation: not tested

The patch would need documentations describing the new options.

Hi

thank you for the patch. It can be applied to current master branch and compiled fine.

I also confirmed that it can be applied and complied, although it raised warnings
about whitespace errors.

/tmp/pgbench-plpgsql-001.patch:68: trailing whitespace.
executeStatement(con,
/tmp/pgbench-plpgsql-001.patch:102: trailing whitespace.
executeStatement(con,
warning: 2 lines add whitespace errors.

The feature works as described, I am able to run plpgsql-tpcb-like and plpgsql-simple-update scripts as you have added them.

But I am not sure the purpose of --no-function to prevent the creation of pl/pgsql functions when the new plpgsql test scripts need them.

I initialized pgbench database with --no-function, and plpgsql-tpcb-like and plpgsql-simple-update scripts will fail to run

I am not sure either whether --no-function option is necessary.
Although there is --no-vacuum, I guess this would be intended to
reduce the initialization time. I don't think omitting creating
functions has such effect. So, I wonder -no-function is unnecessary,
as similar to that there are no option to omitting to create tables.

Regards,
Yugo Nagata

--
Yugo NAGATA <nagata@sraoss.co.jp>

On Wed, Jan 04, 2023 at 07:06:44PM +0100, Hannu Krosing wrote:

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

I'm not sure we should add micro-optimized versions of the existing scripts
to pgbench. Your point about demonstrating the benefits of server-side
functions seems reasonable, but it also feels a bit like artifically
improving pgbench numbers. I think I'd rather see some more variety in the
built-in scripts so that folks can more easily test a wider range of common
workloads. Perhaps this could include a test that is focused on
server-side functions.

In any case, it looks like there is unaddressed feedback for this patch, so
I'm marking it as "waiting on author."

--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

Hello Nathan,

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

I'm not sure we should add micro-optimized versions of the existing scripts
to pgbench. Your point about demonstrating the benefits of server-side
functions seems reasonable, but it also feels a bit like artifically
improving pgbench numbers. I think I'd rather see some more variety in the
built-in scripts so that folks can more easily test a wider range of common
workloads. Perhaps this could include a test that is focused on
server-side functions.

ISTM that your argument suggests to keep the tpcb-like PL/pgSQL version.
It is the more beneficial anyway as it merges 4/5 commands in one call, so
it demonstrate the effect of investing in this kind of approach.

I'm unclear about what variety of scripts that could be provided given the
tables made available with pgbench. ISTM that other scenari would involve
both an initialization and associated scripts, and any proposal would be
bared because it would open the door to anything.

In any case, it looks like there is unaddressed feedback for this patch, so
I'm marking it as "waiting on author."

Indeed.

--
Fabien.

On Tue, Aug 15, 2023 at 09:46:59AM +0200, Fabien COELHO wrote:

I'm unclear about what variety of scripts that could be provided given the
tables made available with pgbench. ISTM that other scenari would involve
both an initialization and associated scripts, and any proposal would be
bared because it would open the door to anything.

Why's that? I'm not aware of any project policy that prohibits such
enhancements to pgbench. It might take some effort to gather consensus on
a proposal like this, but IMHO that doesn't mean we shouldn't try. If the
prevailing wisdom is that we shouldn't add more built-in scripts because
there is an existing way to provide custom ones, then it's not clear that
we should proceed with $SUBJECT, anyway.

--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

Hello Nathan,

I'm unclear about what variety of scripts that could be provided given the
tables made available with pgbench. ISTM that other scenari would involve
both an initialization and associated scripts, and any proposal would be
bared because it would open the door to anything.

Why's that?

Just a wild guess based on 19 years of occasional contributions to pg and
pgbench in particular:-)

I'm not aware of any project policy that prohibits such enhancements to
pgbench.

Attempts in extending pgbench often fall under "you can do it outside (eg
with a custom script) so there is no need to put that in pgbench as it
would add to the maintenance burden with a weak benefit proven by the fact
that it is not there already".

It might take some effort to gather consensus on a proposal like this,
but IMHO that doesn't mean we shouldn't try.

Done it in the past. Probably will do it again in the future:-)

If the prevailing wisdom is that we shouldn't add more built-in scripts
because there is an existing way to provide custom ones, then it's not
clear that we should proceed with $SUBJECT, anyway.

I'm afraid there is that argument. I do not think that this policy is good
wrt $SUBJECT, ISTM that having an easy way to test something with a
PL/pgSQL function would help promote the language by advertising/showing
the potential performance benefit (or not, depending). Just one function
would be enough for that.

--
Fabien.

I will address the comments here over this coming weekend.

I think that in addition to current "tpc-b like" test we could also
have more modern "tpc-c like" and "tpc-h like" tests

And why not any other "* -like" from the rest of TPC-*, YCSP, sysbench, ... :)

though maybe not as part of pg_bench but as extensions ?

---
Hannu

On Fri, 18 Aug 2023 at 23:04, Hannu Krosing <hannuk@google.com> wrote:

I will address the comments here over this coming weekend.

The patch which you submitted has been awaiting your attention for
quite some time now. As such, we have moved it to "Returned with
Feedback" and removed it from the reviewing queue. Depending on
timing, this may be reversible. Kindly address the feedback you have
received, and resubmit the patch to the next CommitFest.

Regards,
Vignesh

Thanks for the update.

I will give it another go over the weekend

Cheers,
Hannu

On Thu, Feb 1, 2024 at 7:33 PM vignesh C <vignesh21@gmail.com> wrote:

On Tue, Aug 15, 2023 at 11:41 AM Nathan Bossart
<nathandbossart@gmail.com> wrote:

Why's that? I'm not aware of any project policy that prohibits such
enhancements to pgbench. It might take some effort to gather consensus on
a proposal like this, but IMHO that doesn't mean we shouldn't try. If the
prevailing wisdom is that we shouldn't add more built-in scripts because
there is an existing way to provide custom ones, then it's not clear that
we should proceed with $SUBJECT, anyway.

I don't think there's a policy against adding more built-in scripts to
pgbench, but I'm skeptical of such efforts because I don't see how to
decide which ones are worthy of inclusion and which are not. Adding
everyone's favorite thing will be too cluttered, and adding nothing
forecloses nothing because people can always provide their own. If we
could establish that certain custom scripts are widely used across
many people, then those might be worth adding.

I have a vague recollection of someone proposing something similar to
this in the past, possibly Jeff Davis. If there is in fact a paper
trail showing that the same thing has been proposed more than once by
unrelated people, that would be a point in favor of adding that
particular thing.

--
Robert Haas
EDB: http://www.enterprisedb.com

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.

---
Hannu

On Fri, Feb 2, 2024 at 9:44 PM Robert Haas <robertmhaas@gmail.com> wrote:

On Sat, Feb 3, 2024 at 8:54 AM Hannu Krosing <hannuk@google.com> 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.

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

On Wed, Jul 23, 2025 at 12:18 PM Hannu Krosing <hannuk@google.com> wrote:

On Sun, Jul 6, 2025 at 11:52 PM Hannu Krosing <hannuk@google.com> wrote:

On Sat, Feb 3, 2024 at 8:54 AM Hannu Krosing <hannuk@google.com> 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

On Mon, Sep 1, 2025 at 3:28 PM Robert Treat <rob@xzilla.net> wrote:

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.

Thanks, very useful.

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.

As the functions are a new addition anyway I am also not worried.

Here is a rebased version, only change is in the flag id for --no-functions

---
Hannu