| From: | Shinya Kato <shinya11(dot)kato(at)gmail(dot)com> |
|---|---|
| To: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | pgbench: \gset and \aset should store SQL NULL as the null value |
| Date: | 2026-08-18 05:07:02 |
| Message-ID: | CAOzEurT9J9QMmpJt_8pWZCziNYCUHOD=c-hoccOVYJLc_VxVRg@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
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
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0001-Make-pgbench-gset-and-aset-store-SQL-NULL-as-the-.patch | application/x-patch | 7.1 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Shinya Kato | 2026-08-18 05:10:18 | Re: Logical replication row filter loses unchanged toasted columns |
| Previous Message | David Rowley | 2026-08-18 04:56:59 | Re: Allow a prosupport function to be attached to an aggregate |