public inbox for [email protected]help / color / mirror / Atom feed
wal_compression = method:level 6+ messages / 4 participants [nested] [flat]
* wal_compression = method:level @ 2023-01-10 23:26 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Justin Pryzby @ 2023-01-10 23:26 UTC (permalink / raw) To: pgsql-hackers; +Cc: Michael Paquier <[email protected]>; Robert Haas <[email protected]>; Matthias van de Meent <[email protected]> Is it desirable to support specifying a level ? Maybe there's a concern about using high compression levels, but I'll start by asking if the feature is wanted at all. Previous discussion at: [email protected] Attachments: [text/x-diff] 0001-Use-GUC-hooks-to-support-compression-level.patch (16.4K, ../../[email protected]/2-0001-Use-GUC-hooks-to-support-compression-level.patch) download | inline diff: From cb30e17cf19fffa370a887d28d6d7e683d588b71 Mon Sep 17 00:00:00 2001 From: Justin Pryzby <[email protected]> Date: Sun, 14 Mar 2021 17:12:07 -0500 Subject: [PATCH] Use GUC hooks to support compression:level.. ..which is useful for zstd, but less so for lz4. TODO: windows, pglz, zlib case --- doc/src/sgml/config.sgml | 6 +- src/backend/access/transam/xlog.c | 20 ++++ src/backend/access/transam/xloginsert.c | 6 +- src/backend/access/transam/xlogreader.c | 124 ++++++++++++++++++++ src/backend/utils/misc/guc_tables.c | 39 ++---- src/common/compression.c | 3 - src/include/access/xlog.h | 6 + src/include/access/xlogreader.h | 2 + src/include/utils/guc_hooks.h | 3 + src/test/regress/expected/compression.out | 19 +++ src/test/regress/expected/compression_1.out | 19 +++ src/test/regress/sql/compression.sql | 10 ++ 12 files changed, 220 insertions(+), 37 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 77574e2d4ec..7b34ed630d5 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -3161,7 +3161,7 @@ include_dir 'conf.d' </varlistentry> <varlistentry id="guc-wal-compression" xreflabel="wal_compression"> - <term><varname>wal_compression</varname> (<type>enum</type>) + <term><varname>wal_compression</varname> (<type>string</type>) <indexterm> <primary><varname>wal_compression</varname> configuration parameter</primary> </indexterm> @@ -3169,7 +3169,7 @@ include_dir 'conf.d' <listitem> <para> This parameter enables compression of WAL using the specified - compression method. + compression method and optional compression level. When enabled, the <productname>PostgreSQL</productname> server compresses full page images written to WAL when <xref linkend="guc-full-page-writes"/> is on or during a base backup. @@ -3179,6 +3179,8 @@ include_dir 'conf.d' was compiled with <option>--with-lz4</option>) and <literal>zstd</literal> (if <productname>PostgreSQL</productname> was compiled with <option>--with-zstd</option>). + A compression level may optionally be specified, by appending the level + number after a colon (<literal>:</literal>) The default value is <literal>off</literal>. Only superusers and users with the appropriate <literal>SET</literal> privilege can change this setting. diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c index 74bd1f5fbe2..e2d81129549 100644 --- a/src/backend/access/transam/xlog.c +++ b/src/backend/access/transam/xlog.c @@ -125,6 +125,8 @@ bool EnableHotStandby = false; bool fullPageWrites = true; bool wal_log_hints = false; int wal_compression = WAL_COMPRESSION_NONE; +char *wal_compression_string = "no"; /* Overwritten by GUC */ +int wal_compression_level = 1; char *wal_consistency_checking_string = NULL; bool *wal_consistency_checking = NULL; bool wal_init_zero = true; @@ -8118,6 +8120,24 @@ assign_xlog_sync_method(int new_sync_method, void *extra) } } +bool +check_wal_compression(char **newval, void **extra, GucSource source) +{ + int tmp; + if (get_compression_level(*newval, &tmp) != -1) + return true; + + return false; +} + +/* Parse the GUC into integers for wal_compression and wal_compression_level */ +void +assign_wal_compression(const char *newval, void *extra) +{ + wal_compression = get_compression_level(newval, &wal_compression_level); + Assert(wal_compression >= 0); +} + /* * Issue appropriate kind of fsync (if any) for an XLOG output file. diff --git a/src/backend/access/transam/xloginsert.c b/src/backend/access/transam/xloginsert.c index 47b6d16eaef..93003744ed0 100644 --- a/src/backend/access/transam/xloginsert.c +++ b/src/backend/access/transam/xloginsert.c @@ -906,8 +906,8 @@ XLogCompressBackupBlock(char *page, uint16 hole_offset, uint16 hole_length, case WAL_COMPRESSION_LZ4: #ifdef USE_LZ4 - len = LZ4_compress_default(source, dest, orig_len, - COMPRESS_BUFSIZE); + len = LZ4_compress_fast(source, dest, orig_len, + COMPRESS_BUFSIZE, wal_compression_level); if (len <= 0) len = -1; /* failure */ #else @@ -918,7 +918,7 @@ XLogCompressBackupBlock(char *page, uint16 hole_offset, uint16 hole_length, case WAL_COMPRESSION_ZSTD: #ifdef USE_ZSTD len = ZSTD_compress(dest, COMPRESS_BUFSIZE, source, orig_len, - ZSTD_CLEVEL_DEFAULT); + wal_compression_level); if (ZSTD_isError(len)) len = -1; /* failure */ #else diff --git a/src/backend/access/transam/xlogreader.c b/src/backend/access/transam/xlogreader.c index a61b4a99219..728ca9f18c4 100644 --- a/src/backend/access/transam/xlogreader.c +++ b/src/backend/access/transam/xlogreader.c @@ -17,7 +17,9 @@ */ #include "postgres.h" +#include <limits.h> #include <unistd.h> + #ifdef USE_LZ4 #include <lz4.h> #endif @@ -30,6 +32,7 @@ #include "access/xlogreader.h" #include "access/xlogrecord.h" #include "catalog/pg_control.h" +#include "common/compression.h" #include "common/pg_lzcompress.h" #include "replication/origin.h" @@ -56,6 +59,30 @@ static void ResetDecoder(XLogReaderState *state); static void WALOpenSegmentInit(WALOpenSegment *seg, WALSegmentContext *segcxt, int segsize, const char *waldir); +static const struct { + char *name; + enum WalCompression compress_id; /* The internal ID */ +} wal_compression_options[] = { + {"pglz", WAL_COMPRESSION_PGLZ}, + +#ifdef USE_LZ4 + {"lz4", WAL_COMPRESSION_LZ4}, +#endif + +#ifdef USE_ZSTD + {"zstd", WAL_COMPRESSION_ZSTD}, +#endif + + {"on", WAL_COMPRESSION_PGLZ}, + {"off", WAL_COMPRESSION_NONE}, + {"true", WAL_COMPRESSION_PGLZ}, + {"false", WAL_COMPRESSION_NONE}, + {"yes", WAL_COMPRESSION_PGLZ}, + {"no", WAL_COMPRESSION_NONE}, + {"1", WAL_COMPRESSION_PGLZ}, + {"0", WAL_COMPRESSION_NONE}, +}; + /* size of the buffer allocated for error message. */ #define MAX_ERRORMSG_LEN 1000 @@ -2035,6 +2062,103 @@ XLogRecGetBlockData(XLogReaderState *record, uint8 block_id, Size *len) } } +/* + * Return the wal compression ID, or -1 if the input is + * invalid/unrecognized/unsupported. + * The compression level is stored in *level. + */ +int +get_compression_level(const char *in, int *level) +{ + pg_compress_algorithm alg; + pg_compress_specification algdetail; + char *algorithm, *detail; + char *error_detail; + int compress_id = -1; + + /* Parse the algorithm and any detail suffix */ + parse_compress_options(in, &algorithm, &detail); + + /* Try to find the WAL_COMPRESSION_* value for the algorithm */ + for (int idx = 0; idx < lengthof(wal_compression_options); ++idx) + { + if (strcmp(wal_compression_options[idx].name, algorithm) == 0) + compress_id = wal_compression_options[idx].compress_id; + } + + if (!parse_compress_algorithm(algorithm, &alg)) + { + /* + * The compression algorithm wasn't a commonly-used algorithm name, but + * may be a supported wal_compression: pglz/on/off/etc. + */ + + if (compress_id >= 0 && detail != NULL) + { + /* + * Algorithms which aren't known by the generic parser don't + * support compression level or other options. + */ +#ifndef FRONTEND + GUC_check_errdetail("Compression algorithm does not support options: %s", + algorithm); +#endif + return -1; + } + + *level = 0; /* ignored */ + return compress_id; + } + else + { + /* Show a useful error if the wal compression is known, but not supported */ + if ((alg == PG_COMPRESSION_ZSTD || alg != PG_COMPRESSION_LZ4) && + compress_id == -1) + + { +#ifndef FRONTEND + GUC_check_errdetail("Compression algorithm is not supported by this build: %s", + algorithm); +#endif + return -1; + } + + /* + * If the algorithm *is* known, then parse its compression level. Note + * that "none" is a supported compression algorithm, but not a valid + * option for wal_compression + */ + parse_compress_specification(alg, detail, &algdetail); + error_detail = validate_compress_specification(&algdetail); + if (error_detail != NULL) + { +#ifndef FRONTEND + GUC_check_errdetail("Could not parse compression detail: %s", + error_detail); +#endif + return -1; + } + + *level = algdetail.level; + if (algdetail.options != 0) + { +#ifndef FRONTEND + GUC_check_errdetail("Compression options other than level= are not supported by wal_compression: %s", + detail); +#endif + return -1; + } + } + + return compress_id; + + /* + * gzip or anything parsed by the generic parser which isn't a valid + * wal_compression value will get here. + */ + return -1; +} + /* * Restore a full-page image from a backup block attached to an XLOG record. * diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c index 5025e80f89d..4a956ea11a3 100644 --- a/src/backend/utils/misc/guc_tables.c +++ b/src/backend/utils/misc/guc_tables.c @@ -433,25 +433,6 @@ static const struct config_enum_entry default_toast_compression_options[] = { {NULL, 0, false} }; -static const struct config_enum_entry wal_compression_options[] = { - {"pglz", WAL_COMPRESSION_PGLZ, false}, -#ifdef USE_LZ4 - {"lz4", WAL_COMPRESSION_LZ4, false}, -#endif -#ifdef USE_ZSTD - {"zstd", WAL_COMPRESSION_ZSTD, false}, -#endif - {"on", WAL_COMPRESSION_PGLZ, false}, - {"off", WAL_COMPRESSION_NONE, false}, - {"true", WAL_COMPRESSION_PGLZ, true}, - {"false", WAL_COMPRESSION_NONE, true}, - {"yes", WAL_COMPRESSION_PGLZ, true}, - {"no", WAL_COMPRESSION_NONE, true}, - {"1", WAL_COMPRESSION_PGLZ, true}, - {"0", WAL_COMPRESSION_NONE, true}, - {NULL, 0, false} -}; - /* * Options for enum values stored in other modules */ @@ -4491,6 +4472,16 @@ struct config_string ConfigureNamesString[] = check_wal_consistency_checking, assign_wal_consistency_checking, NULL }, + { + {"wal_compression", PGC_SUSET, WAL_SETTINGS, + gettext_noop("Compresses full-page writes written in WAL file with specified method."), + NULL + }, + &wal_compression_string, + "no", + check_wal_compression, assign_wal_compression, NULL + }, + { {"jit_provider", PGC_POSTMASTER, CLIENT_CONN_PRELOAD, gettext_noop("JIT provider to use."), @@ -4749,16 +4740,6 @@ struct config_enum ConfigureNamesEnum[] = NULL, NULL, NULL }, - { - {"wal_compression", PGC_SUSET, WAL_SETTINGS, - gettext_noop("Compresses full-page writes written in WAL file with specified method."), - NULL - }, - &wal_compression, - WAL_COMPRESSION_NONE, wal_compression_options, - NULL, NULL, NULL - }, - { {"wal_level", PGC_POSTMASTER, WAL_SETTINGS, gettext_noop("Sets the level of information written to the WAL."), diff --git a/src/common/compression.c b/src/common/compression.c index 2d3e56b4d62..a2e7abb6a47 100644 --- a/src/common/compression.c +++ b/src/common/compression.c @@ -357,8 +357,6 @@ validate_compress_specification(pg_compress_specification *spec) return NULL; } -#ifdef FRONTEND - /* * Basic parsing of a value specified through a command-line option, commonly * -Z/--compress. @@ -418,4 +416,3 @@ parse_compress_options(const char *option, char **algorithm, char **detail) *detail = pstrdup(sep + 1); } } -#endif /* FRONTEND */ diff --git a/src/include/access/xlog.h b/src/include/access/xlog.h index cfe5409738c..115257e52bb 100644 --- a/src/include/access/xlog.h +++ b/src/include/access/xlog.h @@ -16,6 +16,8 @@ #include "datatype/timestamp.h" #include "lib/stringinfo.h" #include "nodes/pg_list.h" +#include "storage/fd.h" +#include "utils/guc.h" /* Sync methods */ @@ -54,6 +56,10 @@ extern PGDLLIMPORT int wal_decode_buffer_size; extern PGDLLIMPORT int CheckPointSegments; +extern PGDLLIMPORT char *wal_compression_string; +extern PGDLLIMPORT int wal_compression; +extern PGDLLIMPORT int wal_compression_level; + /* Archive modes */ typedef enum ArchiveMode { diff --git a/src/include/access/xlogreader.h b/src/include/access/xlogreader.h index da64f99f0b3..f371fbc0e85 100644 --- a/src/include/access/xlogreader.h +++ b/src/include/access/xlogreader.h @@ -440,4 +440,6 @@ extern bool XLogRecGetBlockTagExtended(XLogReaderState *record, uint8 block_id, BlockNumber *blknum, Buffer *prefetch_buffer); +extern int get_compression_level(const char *in, int *level); + #endif /* XLOGREADER_H */ diff --git a/src/include/utils/guc_hooks.h b/src/include/utils/guc_hooks.h index aeb3663071f..fa87d8ae12c 100644 --- a/src/include/utils/guc_hooks.h +++ b/src/include/utils/guc_hooks.h @@ -155,4 +155,7 @@ extern bool check_wal_consistency_checking(char **newval, void **extra, extern void assign_wal_consistency_checking(const char *newval, void *extra); extern void assign_xlog_sync_method(int new_sync_method, void *extra); +extern bool check_wal_compression(char **newval, void **extra, GucSource source); +extern void assign_wal_compression(const char *newval, void *extra); + #endif /* GUC_HOOKS_H */ diff --git a/src/test/regress/expected/compression.out b/src/test/regress/expected/compression.out index 4c997e2602f..72735f4f91a 100644 --- a/src/test/regress/expected/compression.out +++ b/src/test/regress/expected/compression.out @@ -360,3 +360,22 @@ ALTER TABLE badcompresstbl ALTER a SET COMPRESSION I_Do_Not_Exist_Compression; - ERROR: invalid compression method "i_do_not_exist_compression" DROP TABLE badcompresstbl; \set HIDE_TOAST_COMPRESSION true +-- pglz doesn't accept a compression level: +SET wal_compression = 'pglz:1'; +ERROR: invalid value for parameter "wal_compression": "pglz:1" +DETAIL: Compression algorithm does not support options: pglz +-- none and gzip aren't supported wal_compression algorithms: +SET wal_compression = 'none'; +ERROR: invalid value for parameter "wal_compression": "none" +DETAIL: Compression algorithm is not supported by this build: none +SET wal_compression = 'gzip'; +ERROR: invalid value for parameter "wal_compression": "gzip" +DETAIL: Compression algorithm is not supported by this build: gzip +SET wal_compression = 'gzip:1'; +ERROR: invalid value for parameter "wal_compression": "gzip:1" +DETAIL: Compression algorithm is not supported by this build: gzip +-- wrong spelling with a dash instead of a colon: +SET wal_compression = 'zstd-1'; +ERROR: invalid value for parameter "wal_compression": "zstd-1" +SET wal_compression = 'lz4-1'; +ERROR: invalid value for parameter "wal_compression": "lz4-1" diff --git a/src/test/regress/expected/compression_1.out b/src/test/regress/expected/compression_1.out index c0a47646eb2..e9c053cb56b 100644 --- a/src/test/regress/expected/compression_1.out +++ b/src/test/regress/expected/compression_1.out @@ -354,3 +354,22 @@ ALTER TABLE badcompresstbl ALTER a SET COMPRESSION I_Do_Not_Exist_Compression; - ERROR: invalid compression method "i_do_not_exist_compression" DROP TABLE badcompresstbl; \set HIDE_TOAST_COMPRESSION true +-- pglz doesn't accept a compression level: +SET wal_compression = 'pglz:1'; +ERROR: invalid value for parameter "wal_compression": "pglz:1" +DETAIL: Compression algorithm does not support options: pglz +-- none and gzip aren't supported wal_compression algorithms: +SET wal_compression = 'none'; +ERROR: invalid value for parameter "wal_compression": "none" +DETAIL: Compression algorithm is not supported by this build: none +SET wal_compression = 'gzip'; +ERROR: invalid value for parameter "wal_compression": "gzip" +DETAIL: Compression algorithm is not supported by this build: gzip +SET wal_compression = 'gzip:1'; +ERROR: invalid value for parameter "wal_compression": "gzip:1" +DETAIL: Compression algorithm is not supported by this build: gzip +-- wrong spelling with a dash instead of a colon: +SET wal_compression = 'zstd-1'; +ERROR: invalid value for parameter "wal_compression": "zstd-1" +SET wal_compression = 'lz4-1'; +ERROR: invalid value for parameter "wal_compression": "lz4-1" diff --git a/src/test/regress/sql/compression.sql b/src/test/regress/sql/compression.sql index 86332dcc510..5a3cb324220 100644 --- a/src/test/regress/sql/compression.sql +++ b/src/test/regress/sql/compression.sql @@ -151,3 +151,13 @@ ALTER TABLE badcompresstbl ALTER a SET COMPRESSION I_Do_Not_Exist_Compression; - DROP TABLE badcompresstbl; \set HIDE_TOAST_COMPRESSION true + +-- pglz doesn't accept a compression level: +SET wal_compression = 'pglz:1'; +-- none and gzip aren't supported wal_compression algorithms: +SET wal_compression = 'none'; +SET wal_compression = 'gzip'; +SET wal_compression = 'gzip:1'; +-- wrong spelling with a dash instead of a colon: +SET wal_compression = 'zstd-1'; +SET wal_compression = 'lz4-1'; -- 2.25.1 ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pgbench - adding pl/pgsql versions of tests @ 2025-07-06 21:52 Hannu Krosing <[email protected]> 0 siblings, 1 reply; 6+ 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] 6+ 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; 6+ 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] 6+ 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; 6+ 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] 6+ 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; 6+ 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] 6+ 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; 6+ 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] 6+ messages in thread
end of thread, other threads:[~2026-07-02 04:34 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2023-01-10 23:26 wal_compression = method:level Justin Pryzby <[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