Hello devs,
A long time ago I submitted a pgbench \into command to store results of
queries into variables independently of the query being processed, which
got turn into \gset (;) and \cset (\;), which got committed, then \cset
was removed because it was not "up to standard", as it could not work with
empty query (the underlying issue is that pg silently skips empty queries,
so that "\; SELECT 1 \; \; SELECT 3," returns 2 results instead of 4, a
misplaced optimisation from my point of view).
Now there is a pgbench \gset which allows to extract the results of
variables of the last query, but as it does both setting and ending a
query at the same time, there is no way to set variables out of a combined
(\;) query but the last, which is the kind of non orthogonal behavior that
I dislike much.
This annoys me because testing the performance of combined queries cannot
be tested if the script needs to extract variables.
To make the feature somehow accessible to combined queries, the attached
patch adds the "\aset" (all set) command to store all results of queries
which return just one row into variables, i.e.:
SELECT 1 AS one \;
SELECT 2 AS two UNION SELECT 2 \;
SELECT 3 AS three \aset
will set both "one" and "three", while "two" is not set because there were
two rows. It is a kind of more permissive \gset.
Because it does it for all queries, there is no need for synchronizing
with the underlying queries, which made the code for \cset both awkward
and with limitations. Hopefully this version might be "up to standard".
I'll see. I'm in no hurry:-)
--
Fabien.
diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index ee2501be55..83eb7f5f4a 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -966,18 +966,27 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
<varlistentry id='pgbench-metacommand-gset'>
<term>
<literal>\gset [<replaceable>prefix</replaceable>]</literal>
+ <literal>\aset [<replaceable>prefix</replaceable>]</literal>
</term>
<listitem>
<para>
- This command may be used to end SQL queries, taking the place of the
+ These commands may be used to end SQL queries, taking the place of the
terminating semicolon (<literal>;</literal>).
</para>
<para>
- When this 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.
+ 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.
+ </para>
+
+ <para>
+ When the <literal>\aset</literal> command is used, all combined SQL queries
+ (separated by <literal>\;</literal>) which return just one row see their
+ columns stored into variables named after column names, and prefixed with
+ <replaceable>prefix</replaceable> if provided. Other queries are ignored.
</para>
<para>
@@ -986,6 +995,8 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
<replaceable>p_two</replaceable> and <replaceable>p_three</replaceable>
with integers from the third query.
The result of the second query is discarded.
+ The result of the two last combined queries are stored in variables
+ <replaceable>four</replaceable> and <replaceable>five</replaceable>.
<programlisting>
UPDATE pgbench_accounts
SET abalance = abalance + :delta
@@ -994,6 +1005,7 @@ UPDATE pgbench_accounts
-- compound of two queries
SELECT 1 \;
SELECT 2 AS two, 3 AS three \gset p_
+SELECT 4 AS four \; SELECT 5 AS five \aset
</programlisting>
</para>
</listitem>
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index 99529de52a..9bae707368 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -461,6 +461,7 @@ typedef enum MetaCommand
META_SHELL, /* \shell */
META_SLEEP, /* \sleep */
META_GSET, /* \gset */
+ META_ASET, /* \aset */
META_IF, /* \if */
META_ELIF, /* \elif */
META_ELSE, /* \else */
@@ -493,6 +494,7 @@ static const char *QUERYMODE[] = {"simple", "extended", "prepared"};
* varprefix SQL commands terminated with \gset have this set
* to a non NULL value. If nonempty, it's used to prefix the
* variable name that receives the value.
+ * aset do gset on all possible queries of a combined query (\;).
* expr Parsed expression, if needed.
* stats Time spent in this command.
*/
@@ -505,6 +507,7 @@ typedef struct Command
int argc;
char *argv[MAX_ARGS];
char *varprefix;
+ bool aset;
PgBenchExpr *expr;
SimpleStats stats;
} Command;
@@ -2493,6 +2496,8 @@ getMetaCommand(const char *cmd)
mc = META_ENDIF;
else if (pg_strcasecmp(cmd, "gset") == 0)
mc = META_GSET;
+ else if (pg_strcasecmp(cmd, "aset") == 0)
+ mc = META_ASET;
else
mc = META_NONE;
return mc;
@@ -2729,7 +2734,7 @@ sendCommand(CState *st, Command *command)
* Returns true if everything is A-OK, false if any error occurs.
*/
static bool
-readCommandResponse(CState *st, char *varprefix)
+readCommandResponse(CState *st, char *varprefix, bool is_aset)
{
PGresult *res;
int qrynum = 0;
@@ -2746,7 +2751,7 @@ readCommandResponse(CState *st, char *varprefix)
{
case PGRES_COMMAND_OK: /* non-SELECT commands */
case PGRES_EMPTY_QUERY: /* may be used for testing no-op overhead */
- if (is_last && varprefix != NULL)
+ if (is_last && varprefix != NULL && !is_aset)
{
fprintf(stderr,
"client %d script %d command %d query %d: expected one row, got %d\n",
@@ -2757,10 +2762,15 @@ readCommandResponse(CState *st, char *varprefix)
break;
case PGRES_TUPLES_OK:
- if (is_last && varprefix != NULL)
+ if (varprefix != NULL && (is_last || is_aset))
{
if (PQntuples(res) != 1)
{
+ /* under \aset, ignore results which do not return one row */
+ if (is_aset)
+ break;
+
+ /* else under \gset, report the error */
fprintf(stderr,
"client %d script %d command %d query %d: expected one row, got %d\n",
st->id, st->use_file, st->command, qrynum, PQntuples(res));
@@ -2780,7 +2790,7 @@ readCommandResponse(CState *st, char *varprefix)
varname = psprintf("%s%s", varprefix, varname);
/* store result as a string */
- if (!putVariable(st, "gset", varname,
+ if (!putVariable(st, is_aset ? "aset" : "gset", varname,
PQgetvalue(res, 0, fld)))
{
/* internal error */
@@ -3199,7 +3209,9 @@ advanceConnectionState(TState *thread, CState *st, StatsData *agg)
return; /* don't have the whole result yet */
/* store or discard the query results */
- if (readCommandResponse(st, sql_script[st->use_file].commands[st->command]->varprefix))
+ if (readCommandResponse(st,
+ sql_script[st->use_file].commands[st->command]->varprefix,
+ sql_script[st->use_file].commands[st->command]->aset))
st->state = CSTATE_END_COMMAND;
else
st->state = CSTATE_ABORTED;
@@ -4135,6 +4147,7 @@ create_sql_command(PQExpBuffer buf, const char *source)
my_command->argc = 0;
memset(my_command->argv, 0, sizeof(my_command->argv));
my_command->varprefix = NULL; /* allocated later, if needed */
+ my_command->aset = false;
my_command->expr = NULL;
initSimpleStats(&my_command->stats);
@@ -4363,7 +4376,7 @@ process_backslash_command(PsqlScanState sstate, const char *source)
syntax_error(source, lineno, my_command->first_line, my_command->argv[0],
"unexpected argument", NULL, -1);
}
- else if (my_command->meta == META_GSET)
+ else if (my_command->meta == META_GSET || my_command->meta == META_ASET)
{
if (my_command->argc > 2)
syntax_error(source, lineno, my_command->first_line, my_command->argv[0],
@@ -4508,10 +4521,10 @@ ParseScript(const char *script, const char *desc, int weight)
if (command)
{
/*
- * If this is gset, merge into the preceding command. (We
- * don't use a command slot in this case).
+ * If this is gset or aset, merge into the preceding command.
+ * (We don't use a command slot in this case).
*/
- if (command->meta == META_GSET)
+ if (command->meta == META_GSET || command->meta == META_ASET)
{
Command *cmd;
@@ -4533,6 +4546,7 @@ ParseScript(const char *script, const char *desc, int weight)
cmd->varprefix = pg_strdup("");
else
cmd->varprefix = pg_strdup(command->argv[1]);
+ cmd->aset = command->meta == META_ASET;
/* cleanup unused command */
free_command(command);
diff --git a/src/bin/pgbench/t/001_pgbench_with_server.pl b/src/bin/pgbench/t/001_pgbench_with_server.pl
index 62906d5e55..c461ff7d5b 100644
--- a/src/bin/pgbench/t/001_pgbench_with_server.pl
+++ b/src/bin/pgbench/t/001_pgbench_with_server.pl
@@ -538,7 +538,7 @@ pgbench(
}
});
-# working \gset
+# working \gset and \aset
pgbench(
'-t 1', 0,
[ qr{type: .*/001_pgbench_gset}, qr{processed: 1/1} ],
@@ -547,8 +547,10 @@ pgbench(
qr{command=6.: int 2\b},
qr{command=8.: int 3\b},
qr{command=10.: int 4\b},
- qr{command=12.: int 5\b} ],
- 'pgbench gset command',
+ qr{command=12.: int 5\b},
+ qr{command=15.: int 6\b},
+ qr{command=16.: int 7\b} ],
+ 'pgbench gset & aset commands',
{ '001_pgbench_gset' => q{-- test gset
-- no columns
SELECT \gset
@@ -568,6 +570,12 @@ SELECT 0 AS i4, 4 AS i4 \gset
-- work on the last SQL command under \;
\; \; SELECT 0 AS i5 \; SELECT 5 AS i5 \; \; \gset
\set i debug(:i5)
+-- test aset, which applies to a combined query
+\; SELECT 6 AS i6 \; SELECT 7 AS i7 \; \aset
+-- unless it returns more than one row
+SELECT 8 AS i6 UNION SELECT 9 \aset
+\set i debug(:i6)
+\set i debug(:i7)
} });
# trigger many expression errors
