Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit9d8ef98

Browse files
committed
Add support for \aset in pgbench
This option is similar to \gset, except that it is able to store allresults from combined SQL queries into separate variables. If a queryreturns multiple rows, the last result is stored and if a query returnsno rows, nothing is stored.While on it, add a TAP test for \gset to check for a failure when aquery returns multiple rows.Author: Fabien CoelhoReviewed-by: Ibrar Ahmed, Michael PaquierDiscussion:https://postgr.es/m/alpine.DEB.2.21.1904081914200.2529@lancre
1 parented7a509 commit9d8ef98

File tree

3 files changed

+104
-19
lines changed

3 files changed

+104
-19
lines changed

‎doc/src/sgml/ref/pgbench.sgml

Lines changed: 18 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1057,18 +1057,29 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
10571057
<varlistentry id='pgbench-metacommand-gset'>
10581058
<term>
10591059
<literal>\gset [<replaceable>prefix</replaceable>]</literal>
1060+
<literal>\aset [<replaceable>prefix</replaceable>]</literal>
10601061
</term>
10611062

10621063
<listitem>
10631064
<para>
1064-
This command may be used to end SQL queries, taking the place of the
1065+
These commands may be used to end SQL queries, taking the place of the
10651066
terminating semicolon (<literal>;</literal>).
10661067
</para>
10671068

10681069
<para>
1069-
When this command is used, the preceding SQL query is expected to
1070-
return one row, the columns of which are stored into variables named after
1071-
column names, and prefixed with <replaceable>prefix</replaceable> if provided.
1070+
When the <literal>\gset</literal> command is used, the preceding SQL query is
1071+
expected to return one row, the columns of which are stored into variables
1072+
named after column names, and prefixed with <replaceable>prefix</replaceable>
1073+
if provided.
1074+
</para>
1075+
1076+
<para>
1077+
When the <literal>\aset</literal> command is used, all combined SQL queries
1078+
(separated by <literal>\;</literal>) have their columns stored into variables
1079+
named after column names, and prefixed with <replaceable>prefix</replaceable>
1080+
if provided. If a query returns no row, no assignment is made and the variable
1081+
can be tested for existence to detect this. If a query returns more than one
1082+
row, the last value is kept.
10721083
</para>
10731084

10741085
<para>
@@ -1077,6 +1088,8 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
10771088
<replaceable>p_two</replaceable> and <replaceable>p_three</replaceable>
10781089
with integers from the third query.
10791090
The result of the second query is discarded.
1091+
The result of the two last combined queries are stored in variables
1092+
<replaceable>four</replaceable> and <replaceable>five</replaceable>.
10801093
<programlisting>
10811094
UPDATE pgbench_accounts
10821095
SET abalance = abalance + :delta
@@ -1085,6 +1098,7 @@ UPDATE pgbench_accounts
10851098
-- compound of two queries
10861099
SELECT 1 \;
10871100
SELECT 2 AS two, 3 AS three \gset p_
1101+
SELECT 4 AS four \; SELECT 5 AS five \aset
10881102
</programlisting>
10891103
</para>
10901104
</listitem>

‎src/bin/pgbench/pgbench.c

Lines changed: 41 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -480,6 +480,7 @@ typedef enum MetaCommand
480480
META_SHELL,/* \shell */
481481
META_SLEEP,/* \sleep */
482482
META_GSET,/* \gset */
483+
META_ASET,/* \aset */
483484
META_IF,/* \if */
484485
META_ELIF,/* \elif */
485486
META_ELSE,/* \else */
@@ -504,14 +505,16 @@ static const char *QUERYMODE[] = {"simple", "extended", "prepared"};
504505
*not applied.
505506
* first_lineA short, single-line extract of 'lines', for error reporting.
506507
* typeSQL_COMMAND or META_COMMAND
507-
* metaThe type of meta-command, or META_NONE if command is SQL
508+
* metaThe type of meta-command, with META_NONE/GSET/ASET if command
509+
*is SQL.
508510
* argcNumber of arguments of the command, 0 if not yet processed.
509511
* argvCommand arguments, the first of which is the command or SQL
510512
*string itself. For SQL commands, after post-processing
511513
*argv[0] is the same as 'lines' with variables substituted.
512-
* varprefix SQL commands terminated with \gset have this set
514+
* varprefix SQL commands terminated with \gsetor \asethave this set
513515
*to a non NULL value. If nonempty, it's used to prefix the
514516
*variable name that receives the value.
517+
* asetdo gset on all possible queries of a combined query (\;).
515518
* exprParsed expression, if needed.
516519
* statsTime spent in this command.
517520
*/
@@ -2489,6 +2492,8 @@ getMetaCommand(const char *cmd)
24892492
mc=META_ENDIF;
24902493
elseif (pg_strcasecmp(cmd,"gset")==0)
24912494
mc=META_GSET;
2495+
elseif (pg_strcasecmp(cmd,"aset")==0)
2496+
mc=META_ASET;
24922497
else
24932498
mc=META_NONE;
24942499
returnmc;
@@ -2711,17 +2716,25 @@ sendCommand(CState *st, Command *command)
27112716
* Process query response from the backend.
27122717
*
27132718
* If varprefix is not NULL, it's the variable name prefix where to store
2714-
* the results of the *last* command.
2719+
* the results of the *last* command (META_GSET) or *all* commands
2720+
* (META_ASET).
27152721
*
27162722
* Returns true if everything is A-OK, false if any error occurs.
27172723
*/
27182724
staticbool
2719-
readCommandResponse(CState*st,char*varprefix)
2725+
readCommandResponse(CState*st,MetaCommandmeta,char*varprefix)
27202726
{
27212727
PGresult*res;
27222728
PGresult*next_res;
27232729
intqrynum=0;
27242730

2731+
/*
2732+
* varprefix should be set only with \gset or \aset, and SQL commands do
2733+
* not need it.
2734+
*/
2735+
Assert((meta==META_NONE&&varprefix==NULL)||
2736+
((meta==META_GSET||meta==META_ASET)&&varprefix!=NULL));
2737+
27252738
res=PQgetResult(st->con);
27262739

27272740
while (res!=NULL)
@@ -2736,7 +2749,7 @@ readCommandResponse(CState *st, char *varprefix)
27362749
{
27372750
casePGRES_COMMAND_OK:/* non-SELECT commands */
27382751
casePGRES_EMPTY_QUERY:/* may be used for testing no-op overhead */
2739-
if (is_last&&varprefix!=NULL)
2752+
if (is_last&&meta==META_GSET)
27402753
{
27412754
pg_log_error("client %d script %d command %d query %d: expected one row, got %d",
27422755
st->id,st->use_file,st->command,qrynum,0);
@@ -2745,14 +2758,22 @@ readCommandResponse(CState *st, char *varprefix)
27452758
break;
27462759

27472760
casePGRES_TUPLES_OK:
2748-
if (is_last&&varprefix!=NULL)
2761+
if ((is_last&&meta==META_GSET)||meta==META_ASET)
27492762
{
2750-
if (PQntuples(res)!=1)
2763+
intntuples=PQntuples(res);
2764+
2765+
if (meta==META_GSET&&ntuples!=1)
27512766
{
2767+
/* under \gset, report the error */
27522768
pg_log_error("client %d script %d command %d query %d: expected one row, got %d",
27532769
st->id,st->use_file,st->command,qrynum,PQntuples(res));
27542770
gotoerror;
27552771
}
2772+
elseif (meta==META_ASET&&ntuples <=0)
2773+
{
2774+
/* coldly skip empty result under \aset */
2775+
break;
2776+
}
27562777

27572778
/* store results into variables */
27582779
for (intfld=0;fld<PQnfields(res);fld++)
@@ -2763,9 +2784,9 @@ readCommandResponse(CState *st, char *varprefix)
27632784
if (*varprefix!='\0')
27642785
varname=psprintf("%s%s",varprefix,varname);
27652786

2766-
/* store result as a string */
2767-
if (!putVariable(st,"gset",varname,
2768-
PQgetvalue(res,0,fld)))
2787+
/* storelast rowresult as a string */
2788+
if (!putVariable(st,meta==META_ASET ?"aset" :"gset",varname,
2789+
PQgetvalue(res,ntuples-1,fld)))
27692790
{
27702791
/* internal error */
27712792
pg_log_error("client %d script %d command %d query %d: error storing into variable %s",
@@ -3181,7 +3202,9 @@ advanceConnectionState(TState *thread, CState *st, StatsData *agg)
31813202
return;/* don't have the whole result yet */
31823203

31833204
/* store or discard the query results */
3184-
if (readCommandResponse(st,sql_script[st->use_file].commands[st->command]->varprefix))
3205+
if (readCommandResponse(st,
3206+
sql_script[st->use_file].commands[st->command]->meta,
3207+
sql_script[st->use_file].commands[st->command]->varprefix))
31853208
st->state=CSTATE_END_COMMAND;
31863209
else
31873210
st->state=CSTATE_ABORTED;
@@ -4660,7 +4683,7 @@ process_backslash_command(PsqlScanState sstate, const char *source)
46604683
syntax_error(source,lineno,my_command->first_line,my_command->argv[0],
46614684
"unexpected argument",NULL,-1);
46624685
}
4663-
elseif (my_command->meta==META_GSET)
4686+
elseif (my_command->meta==META_GSET||my_command->meta==META_ASET)
46644687
{
46654688
if (my_command->argc>2)
46664689
syntax_error(source,lineno,my_command->first_line,my_command->argv[0],
@@ -4804,10 +4827,10 @@ ParseScript(const char *script, const char *desc, int weight)
48044827
if (command)
48054828
{
48064829
/*
4807-
* If this is gset, merge into the preceding command. (We
4808-
* don't use a command slot in this case).
4830+
* If this is gset or aset, merge into the preceding command.
4831+
*(Wedon't use a command slot in this case).
48094832
*/
4810-
if (command->meta==META_GSET)
4833+
if (command->meta==META_GSET||command->meta==META_ASET)
48114834
{
48124835
Command*cmd;
48134836

@@ -4830,6 +4853,9 @@ ParseScript(const char *script, const char *desc, int weight)
48304853
else
48314854
cmd->varprefix=pg_strdup(command->argv[1]);
48324855

4856+
/* update the sql command meta */
4857+
cmd->meta=command->meta;
4858+
48334859
/* cleanup unused command */
48344860
free_command(command);
48354861

‎src/bin/pgbench/t/001_pgbench_with_server.pl

Lines changed: 45 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -699,6 +699,51 @@ sub pgbench
699699
-- work on the last SQL command under \;
700700
\; \; SELECT 0 AS i5 \; SELECT 5 AS i5 \; \; \gset
701701
\set i debug(:i5)
702+
}
703+
});
704+
# \gset cannot accept more than one row, causing command to fail.
705+
pgbench(
706+
'-t 1', 2,
707+
[qr{type: .*/001_pgbench_gset_two_rows},qr{processed: 0/1} ],
708+
[qr{expected one row, got 2\b}],
709+
'pgbench gset command with two rows',
710+
{
711+
'001_pgbench_gset_two_rows'=>q{
712+
SELECT 5432 AS fail UNION SELECT 5433 ORDER BY 1 \gset
713+
}
714+
});
715+
716+
# working \aset
717+
# Valid cases.
718+
pgbench(
719+
'-t 1', 0,
720+
[qr{type: .*/001_pgbench_aset},qr{processed: 1/1} ],
721+
[qr{command=3.: int 8\b},qr{command=4.: int 7\b} ],
722+
'pgbench aset command',
723+
{
724+
'001_pgbench_aset'=>q{
725+
-- test aset, which applies to a combined query
726+
\; SELECT 6 AS i6 \; SELECT 7 AS i7 \; \aset
727+
-- unless it returns more than one row, last is kept
728+
SELECT 8 AS i6 UNION SELECT 9 ORDER BY 1 DESC \aset
729+
\set i debug(:i6)
730+
\set i debug(:i7)
731+
}
732+
});
733+
# Empty result set with \aset, causing command to fail.
734+
pgbench(
735+
'-t 1', 2,
736+
[qr{type: .*/001_pgbench_aset_empty},qr{processed: 0/1} ],
737+
[
738+
qr{undefined variable\"i8\"},
739+
qr{evaluation of meta-command failed\b}
740+
],
741+
'pgbench aset command with empty result',
742+
{
743+
'001_pgbench_aset_empty'=>q{
744+
-- empty result
745+
\; SELECT 5432 AS i8 WHERE FALSE \; \aset
746+
\set i debug(:i8)
702747
}
703748
});
704749

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp