Hi hackers, I am proposing a patch that makes pgbench's \gset and \aset store a SQL NULL as the null value, so that capturing a NULL no longer kills the client.
readCommandResponse() stores every column with PQgetvalue(), which returns an empty string for SQL NULL. An empty string is not a valid value in the pgbench expression language, so the first expression use of such a variable aborts the client. Neither --max-tries nor --continue-on-error recovers from it. I ran into this writing a TPC-C like script, where min() over an empty set is a normal branch and not an error. Reduced to the smallest form: ``` $ cat repro.sql SELECT NULL AS nv \gset \set i debug(:nv) $ pgbench -n -t 1 -f repro.sql postgres pgbench: error: client 0 aborted in command 1 (set) of script 0; evaluation of meta-command failed number of transactions actually processed: 0/1 ``` The workaround is coalesce() with a sentinel the query cannot return, so IS NULL is unusable on anything \gset produced. The patch checks PQgetisnull() and assigns the null value, exactly as \set varname NULL does. An empty string returned by the server still aborts the client when used in an expression. A NULL-valued variable is still bound as the string "NULL" in the extended and prepared query modes. That is the existing behavior of \set varname NULL, so the patch only documents it. Scripts that captured a NULL and interpolated it textually used to get an empty string, usually a syntax error, but inside quotes it silently inserted ''. That silent change is why I think this is master only, though an unrecoverable client abort is an argument for backpatching. The patch is attached. Thoughts? -- Shinya Kato NTT OSS Center
From 69f064c18bbe288fac4588862e4bcd144a44e044 Mon Sep 17 00:00:00 2001 From: Shinya Kato <[email protected]> Date: Sun, 26 Jul 2026 14:31:14 +0900 Subject: [PATCH v1] Make pgbench \gset and \aset store SQL NULL as the null value Previously, readCommandResponse() stored every column with PQgetvalue(), which returns an empty string for SQL NULL. An empty string is not a valid value in the pgbench expression language, so the first expression use of such a variable aborted the client, and neither --max-tries nor --continue-on-error recovered from it. An empty string returned by the server was also indistinguishable from a NULL. Check PQgetisnull() on the row being stored and assign the null value for NULL columns, exactly as \set varname NULL does, so the variable can be tested with IS NULL. Empty strings are still stored as empty strings. Scripts that captured a NULL and interpolated it textually now get NULL instead of an empty string. A NULL-valued variable is still bound as the string "NULL" in the extended and prepared query modes, which the patch only documents. Author: Shinya Kato <[email protected]> Reviewed-by: Discussion: https://postgr.es/m/ --- doc/src/sgml/ref/pgbench.sgml | 13 +++ src/bin/pgbench/pgbench.c | 19 ++++- src/bin/pgbench/t/001_pgbench_with_server.pl | 83 ++++++++++++++++++++ 3 files changed, 112 insertions(+), 3 deletions(-) diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml index 9688527c955..3c489286257 100644 --- a/doc/src/sgml/ref/pgbench.sgml +++ b/doc/src/sgml/ref/pgbench.sgml @@ -1241,6 +1241,19 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d If a query returns more than one row, the last value is kept. </para> + <para> + A column whose value is SQL <literal>NULL</literal> is stored as + the <literal>NULL</literal> value, just as + <literal>\set <replaceable>varname</replaceable> NULL</literal> does. + It can be tested for nullness, and it is substituted into SQL text as + the unquoted word <literal>NULL</literal>. In the + <literal>extended</literal> and <literal>prepared</literal> query + modes, however, it is passed as the string <literal>NULL</literal> + rather than as an SQL null parameter. A non-null column whose text is + <literal>NULL</literal>, in any letter case, is also treated as the + null value in expressions. + </para> + <para> <literal>\gset</literal> and <literal>\aset</literal> cannot be used in pipeline mode, since the query results are not yet available by the time diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c index 5862758427f..bfd33383f7d 100644 --- a/src/bin/pgbench/pgbench.c +++ b/src/bin/pgbench/pgbench.c @@ -3319,6 +3319,7 @@ readCommandResponse(CState *st, MetaCommand meta, char *varprefix) if ((is_last && meta == META_GSET) || meta == META_ASET) { int ntuples = PQntuples(res); + const char *context = meta == META_ASET ? "aset" : "gset"; if (meta == META_GSET && ntuples != 1) { @@ -3338,14 +3339,26 @@ readCommandResponse(CState *st, MetaCommand meta, char *varprefix) for (int fld = 0; fld < PQnfields(res); fld++) { char *varname = PQfname(res, fld); + bool ok; /* allocate varname only if necessary, freed below */ if (*varprefix != '\0') varname = psprintf("%s%s", varprefix, varname); - /* store last row result as a string */ - if (!putVariable(&st->variables, meta == META_ASET ? "aset" : "gset", varname, - PQgetvalue(res, ntuples - 1, fld))) + /* store last row result, SQL NULL as null value */ + if (PQgetisnull(res, ntuples - 1, fld)) + { + PgBenchValue nullval; + + setNullValue(&nullval); + ok = putVariableValue(&st->variables, context, + varname, &nullval); + } + else + ok = putVariable(&st->variables, context, varname, + PQgetvalue(res, ntuples - 1, fld)); + + if (!ok) { /* internal error */ pg_log_error("client %d script %d command %d query %d: error storing into variable %s", diff --git a/src/bin/pgbench/t/001_pgbench_with_server.pl b/src/bin/pgbench/t/001_pgbench_with_server.pl index bb12ade59f5..76a86e0e65d 100644 --- a/src/bin/pgbench/t/001_pgbench_with_server.pl +++ b/src/bin/pgbench/t/001_pgbench_with_server.pl @@ -850,6 +850,73 @@ SELECT 5432 AS fail UNION SELECT 5433 ORDER BY 1 \gset } }); +# \gset stores a SQL NULL as the null value, which expressions can use. +$node->pgbench( + '-t 1', 0, + [ qr{type: .*/001_pgbench_gset_null}, qr{processed: 1/1} ], + [ qr{command=2.: null\b}, qr{command=3.: boolean true\b} ], + 'pgbench gset command with NULL', + { + '001_pgbench_gset_null' => q{-- NULL is stored as the null value +SELECT NULL AS nv \gset +\set i debug(:nv) +\set i debug(:nv IS NULL) +} + }); + +# NULL and empty string captured by \gset stay distinct when interpolated +$node->safe_psql('postgres', + 'CREATE UNLOGGED TABLE gset_null_tab(id INTEGER, t TEXT);'); + +$node->pgbench( + '-t 1', 0, + [ qr{type: .*/001_pgbench_gset_null_interp}, qr{processed: 1/1} ], + [], + 'pgbench gset NULL interpolation', + { + '001_pgbench_gset_null_interp' => q{-- interpolate NULL and '' +SELECT NULL AS nv, ''::text AS es \gset +INSERT INTO gset_null_tab VALUES (1, :nv); +INSERT INTO gset_null_tab VALUES (2, ':es'); +} + }); + +is( $node->safe_psql( + 'postgres', 'SELECT t IS NULL FROM gset_null_tab WHERE id = 1;'), + 't', + 'gset NULL interpolates as SQL NULL'); +is( $node->safe_psql( + 'postgres', "SELECT t = '' FROM gset_null_tab WHERE id = 2;"), + 't', + 'gset empty string stays empty'); + +# In the extended and prepared query modes a NULL-valued variable is bound as +# the string "NULL", not as an SQL null parameter. +for my $mode ('extended', 'prepared') +{ + $node->pgbench( + "-t 1 -M $mode", + 0, + [ qr{type: .*/001_pgbench_gset_null_param_$mode}, qr{processed: 1/1} ], + [], + "pgbench gset NULL as query parameter ($mode)", + { + "001_pgbench_gset_null_param_$mode" => + q{-- NULL bound as a query parameter +SELECT NULL AS nv \gset +INSERT INTO gset_null_tab VALUES (3, :nv); +} + }); +} + +is( $node->safe_psql( + 'postgres', + "SELECT count(*) FROM gset_null_tab WHERE id = 3 AND t = 'NULL';"), + '2', + 'gset NULL is bound as the string NULL in extended and prepared modes'); + +$node->safe_psql('postgres', 'DROP TABLE gset_null_tab;'); + # working \aset # Valid cases. $node->pgbench( @@ -881,6 +948,22 @@ $node->pgbench( -- empty result \; SELECT 5432 AS i8 WHERE FALSE \; \aset \set i debug(:i8) +} + }); +# \aset keeps the last row of a multi-row result, NULL included. +$node->pgbench( + '-t 1', 0, + [ qr{type: .*/001_pgbench_aset_null}, qr{processed: 1/1} ], + [ qr{command=2.: null\b}, qr{command=4.: int 1\b} ], + 'pgbench aset command with NULL', + { + '001_pgbench_aset_null' => q{ +-- the last row is NULL +SELECT * FROM (VALUES (1), (NULL)) AS v(n) ORDER BY 1 NULLS LAST \aset +\set i debug(:n) +-- the last row is not NULL +SELECT * FROM (VALUES (1), (NULL)) AS v(n) ORDER BY 1 NULLS FIRST \aset +\set i debug(:n) } }); -- 2.47.3
