Documentation fix on pgbench \aset command
Hi,
The current documentation of pgbench’s \aset command states:
“If a query returns no row, no assignment is made and the variable
can be tested for existence to detect this.”
However, this is inaccurate, since variable existence check (like \if :{?var})
is not yet supported, although such a feature was proposed in [1]/messages/by-id/alpine.DEB.2.20.1802191909500.21372@lancre and discussed
again in [2]/messages/by-id/alpine.DEB.2.21.1904081914200.2529@lancre, where \aset itself was introduced.
Therefore, I’ve attached a patch to remove this part from the documentation.
[1]: /messages/by-id/alpine.DEB.2.20.1802191909500.21372@lancre
[2]: /messages/by-id/alpine.DEB.2.21.1904081914200.2529@lancre
Best regards,
Yugo Nagata
--
Yugo Nagata <nagata@sraoss.co.jp>
Attachments:
fix_doc_pgbench_aset.ptachapplication/octet-stream; name=fix_doc_pgbench_aset.ptachDownload
diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index ab252d9fc74..efdcad5819e 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -1210,9 +1210,8 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
When the <literal>\aset</literal> command is used, all combined SQL queries
(separated by <literal>\;</literal>) have their columns stored into variables
named after column names, and prefixed with <replaceable>prefix</replaceable>
- if provided. If a query returns no row, no assignment is made and the variable
- can be tested for existence to detect this. If a query returns more than one
- row, the last value is kept.
+ if provided. If a query returns no row, no assignment is made.
+ If a query returns more than one row, the last value is kept.
</para>
<para>
On 2025/06/26 18:01, Yugo Nagata wrote:
Hi,
The current documentation of pgbench’s \aset command states:
“If a query returns no row, no assignment is made and the variable
can be tested for existence to detect this.”However, this is inaccurate, since variable existence check (like \if :{?var})
is not yet supported, although such a feature was proposed in [1] and discussed
again in [2], where \aset itself was introduced.
Right.
Therefore, I’ve attached a patch to remove this part from the documentation.
Or would it be worth documenting an alternative way to detect when no rows are
returned? For example:
If a query returns no rows, no variables are assigned. You can detect
this by setting an initial value to the variable before running the query
and checking whether it changes.
Regards,
--
Fujii Masao
NTT DATA Japan Corporation
On Fri, 27 Jun 2025 01:05:47 +0900
Fujii Masao <masao.fujii@oss.nttdata.com> wrote:
On 2025/06/26 18:01, Yugo Nagata wrote:
Hi,
The current documentation of pgbench’s \aset command states:
“If a query returns no row, no assignment is made and the variable
can be tested for existence to detect this.”However, this is inaccurate, since variable existence check (like \if :{?var})
is not yet supported, although such a feature was proposed in [1] and discussed
again in [2], where \aset itself was introduced.Right.
Therefore, I’ve attached a patch to remove this part from the documentation.
Or would it be worth documenting an alternative way to detect when no rows are
returned? For example:If a query returns no rows, no variables are assigned. You can detect
this by setting an initial value to the variable before running the query
and checking whether it changes.
That's what I thought as well, but I didn’t do it because we can’t distinguish it
from the case where the query returns the same value as the initial one.
To detect it correctly, we would need to reconsider the variable existence check,
or introduce a new meta-command or a special variable that indicates the number of
rows returned by the previous query, and so on.
Regards,
Yugo Nagata
--
Yugo Nagata <nagata@sraoss.co.jp>
On 2025/06/27 1:31, Yugo Nagata wrote:
On Fri, 27 Jun 2025 01:05:47 +0900
Fujii Masao <masao.fujii@oss.nttdata.com> wrote:On 2025/06/26 18:01, Yugo Nagata wrote:
Hi,
The current documentation of pgbench’s \aset command states:
“If a query returns no row, no assignment is made and the variable
can be tested for existence to detect this.”However, this is inaccurate, since variable existence check (like \if :{?var})
is not yet supported, although such a feature was proposed in [1] and discussed
again in [2], where \aset itself was introduced.Right.
Therefore, I’ve attached a patch to remove this part from the documentation.
Or would it be worth documenting an alternative way to detect when no rows are
returned? For example:If a query returns no rows, no variables are assigned. You can detect
this by setting an initial value to the variable before running the query
and checking whether it changes.That's what I thought as well, but I didn’t do it because we can’t distinguish it
from the case where the query returns the same value as the initial one.
Could we use a value that the query is guaranteed not to return as the initial value?
For example, in SELECT id AS myid FROM test \aset, if the id column is of type integer,
we could initialize myid to FALSE.
To detect it correctly, we would need to reconsider the variable existence check,
or introduce a new meta-command or a special variable that indicates the number of
rows returned by the previous query, and so on.
+1
Regards,
--
Fujii Masao
NTT DATA Japan Corporation
On Fri, 27 Jun 2025 01:45:16 +0900
Fujii Masao <masao.fujii@oss.nttdata.com> wrote:
On 2025/06/27 1:31, Yugo Nagata wrote:
On Fri, 27 Jun 2025 01:05:47 +0900
Fujii Masao <masao.fujii@oss.nttdata.com> wrote:On 2025/06/26 18:01, Yugo Nagata wrote:
Hi,
The current documentation of pgbench’s \aset command states:
“If a query returns no row, no assignment is made and the variable
can be tested for existence to detect this.”However, this is inaccurate, since variable existence check (like \if :{?var})
is not yet supported, although such a feature was proposed in [1] and discussed
again in [2], where \aset itself was introduced.Right.
Therefore, I’ve attached a patch to remove this part from the documentation.
Or would it be worth documenting an alternative way to detect when no rows are
returned? For example:If a query returns no rows, no variables are assigned. You can detect
this by setting an initial value to the variable before running the query
and checking whether it changes.That's what I thought as well, but I didn’t do it because we can’t distinguish it
from the case where the query returns the same value as the initial one.Could we use a value that the query is guaranteed not to return as the initial value?
For example, in SELECT id AS myid FROM test \aset, if the id column is of type integer,
we could initialize myid to FALSE.
This will work if we assume that the query never returns the initial value.
I've attached a updated patch that fix the documentation as below:
If a query returns no rows, no assignment is made. You can detect
this by initializing the variable beforehand with a value that the query is
assumed never to return, and then checking whether it changes.
Regards,
Yugo Nagata
--
Yugo Nagata <nagata@sraoss.co.jp>
Attachments:
v2_fix_doc_pgbench_aset.ptachapplication/octet-stream; name=v2_fix_doc_pgbench_aset.ptachDownload
diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index ab252d9fc74..2dc3da88cf8 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -1210,9 +1210,10 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
When the <literal>\aset</literal> command is used, all combined SQL queries
(separated by <literal>\;</literal>) have their columns stored into variables
named after column names, and prefixed with <replaceable>prefix</replaceable>
- if provided. If a query returns no row, no assignment is made and the variable
- can be tested for existence to detect this. If a query returns more than one
- row, the last value is kept.
+ if provided. If a query returns no rows, no assignment is made. You can detect
+ this by initializing the variable beforehand with a value that the query is
+ assumed never to return, and then checking whether it changes.
+ If a query returns more than one row, the last value is kept.
</para>
<para>
On Fri, Jun 27, 2025 at 10:37 AM Yugo Nagata <nagata@sraoss.co.jp> wrote:
This will work if we assume that the query never returns the initial value.
I've attached a updated patch that fix the documentation as below:If a query returns no rows, no assignment is made. You can detect
this by initializing the variable beforehand with a value that the query is
assumed never to return, and then checking whether it changes.
Thanks for updating the patch!
While reviewing the descriptions for \gset and \aset, I noticed they're listed
as a single term. I think it would be clearer to list them separately.
Also, there's no explanation of what happens if the query with \gset returns
zero or multiple rows, so I'd like to add that too. Thought?
I've attached an updated version of the patch with these changes applied.
Regards,
--
Fujii Masao
Attachments:
v3-0001-pgbench-Clarify-documentation-for-gset-and-aset.patchapplication/octet-stream; name=v3-0001-pgbench-Clarify-documentation-for-gset-and-aset.patchDownload
From 4be5223de80ceb549392af27252ab5b7a6b8806f Mon Sep 17 00:00:00 2001
From: Fujii Masao <fujii@postgresql.org>
Date: Mon, 22 Sep 2025 15:22:37 +0900
Subject: [PATCH v3] pgbench: Clarify documentation for \gset and \aset.
This commit updates the pgbench documentation to list \gset and \aset
as separate terms for easier reading. It also clarifies that \gset raises
an error if the query returns zero or multiple rows, and explains how to
detect cases where the query with \aset returned no rows.
Author: Yugo Nagata <nagata@sraoss.co.jp>
Reviewed-by: Fujii Masao <masao.fujii@gmail.com>
Discussion: https://postgr.es/m/20250626180125.5b896902a3d0bcd93f86c240@sraoss.co.jp
---
doc/src/sgml/ref/pgbench.sgml | 15 +++++++--------
1 file changed, 7 insertions(+), 8 deletions(-)
diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index ab252d9fc74..5ca9de63fa3 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -1188,10 +1188,8 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
<variablelist>
<varlistentry id="pgbench-metacommand-gset">
- <term>
- <literal>\gset [<replaceable>prefix</replaceable>]</literal>
- <literal>\aset [<replaceable>prefix</replaceable>]</literal>
- </term>
+ <term><literal>\gset [<replaceable>prefix</replaceable>]</literal></term>
+ <term><literal>\aset [<replaceable>prefix</replaceable>]</literal></term>
<listitem>
<para>
@@ -1203,16 +1201,17 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
When the <literal>\gset</literal> command is used, the preceding SQL query is
expected to return one row, the columns of which are stored into variables
named after column names, and prefixed with <replaceable>prefix</replaceable>
- if provided.
+ if provided. If the query returns zero or multiple rows, an error is raised.
</para>
<para>
When the <literal>\aset</literal> command is used, all combined SQL queries
(separated by <literal>\;</literal>) have their columns stored into variables
named after column names, and prefixed with <replaceable>prefix</replaceable>
- if provided. If a query returns no row, no assignment is made and the variable
- can be tested for existence to detect this. If a query returns more than one
- row, the last value is kept.
+ if provided. If a query returns no rows, no assignment is made.
+ This can be detected by initializing the variable beforehand with
+ a value the query cannot return, then checking whether it changes.
+ If a query returns more than one row, the last values is kept.
</para>
<para>
--
2.50.1
On Mon, 22 Sep 2025 16:19:14 +0900
Fujii Masao <masao.fujii@gmail.com> wrote:
On Fri, Jun 27, 2025 at 10:37 AM Yugo Nagata <nagata@sraoss.co.jp> wrote:
This will work if we assume that the query never returns the initial value.
I've attached a updated patch that fix the documentation as below:If a query returns no rows, no assignment is made. You can detect
this by initializing the variable beforehand with a value that the query is
assumed never to return, and then checking whether it changes.Thanks for updating the patch!
While reviewing the descriptions for \gset and \aset, I noticed they're listed
as a single term. I think it would be clearer to list them separately.
Also, there's no explanation of what happens if the query with \gset returns
zero or multiple rows, so I'd like to add that too. Thought?I've attached an updated version of the patch with these changes applied.
Thank you for your suggestion and updating the patch.
I'm fine with your version since it is clearer.
Regards,
Yugo Nagata
--
Yugo Nagata <nagata@sraoss.co.jp>