Re: pgbench: extend variable usage in scripts

On Fri, 29 Aug 2025 09:27:36 +0200
Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:

> pá 29. 8. 2025 v 9:23 odesílatel Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp> napsal:
>
> > Hi,
> >
> > I would like to propose patches to extend variable usage in pgbench
> > scripts.
> >
> > * 0001: Allow variables to be used as an SQL literal or identifier
> >
> > Currently, variables in pgbench scripts are expanded always without
> > quotes, so they cannot be used as SQL literals or identifiers.
> > This patch allows the use of :'var' and :"var" in addition to :var

While working on this patch, I was surprised to notice that variables are
expanded even inside quoted literals or identifiers in pgbench scripts,
which differs from the behavior in psql.

For example, a variable var is expanded in the following query if it is
defined. Otherwise, if the variable is not defined, the query result is
unchanged:

SELECT 'var is :var';

This means that we don’t actually need the new syntax proposed in patch 0001
to allow variables to be used as SQL literals or identifiers.

However, it also means that users need to be careful when a script contains
text or identifiers such as table names or function names that include a colon,
since such text could be unexpectedly rewritten.

Even worse, when "extended" or "prepared" mode is used, scripts containing a colon
will fail because any word starting with a colon is replaced with a placeholder like
"$1". For example, the query above fails with the following error, regardless of whether
the variable is defined or not:

ERROR: bind message supplies 1 parameters, but prepared statement "" requires 0
STATEMENT: SELECT 'var is $1';

In my opinion, it is not worth fixing the expansion of variables inside quoted text,
since I have never heard any complaints about it, and changing it would break backward
compatibility.

However, it would be better to update the documentation to note that variables are expanded
in all parts of an SQL command, and that this behavior differs from psql scripts.

Any thought?

> > However, they can used only in SQL commands, not in the arguments of
> > meta-commands, since exprscan.l (the lexical scanner for pgbench backslash
> > commands) cannot currently handle quoted values.
> >
> > Also, we have to use \aset, \gset or -D option to assign a string to a
> > varialbe,
> > since pgbench's \set command cannot recognize text values in its arguments.
> >
> > * 0002: Add syntax for variable exisitence check
> >
> > Currently, pgbench does not support :{?var} syntax to check whether the
> > variable
> > is defined or not. This patch adds support for this syntax in meta-command
> > arguments.
> > This is useful for checking if \aset set the result to the variable or not.
> >
> > However, it cannot be used in SQL statements for now.
> >
> >
> > Some of the current limitations described above might be relaxed in the
> > future, but
> > for now, I would like to ask for initial feedback on this proposal.
> >
>
> +1
>
> Regards
>
> Pavel
>
>
> >
> > Regards,
> > Yugo Nagata
> >
> > --
> > Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp>
> >

--
Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp>