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

Commitf347ec7

Browse files
Allow \watch queries to stop on minimum rows returned
When running a repeat query with \watch in psql, it can behelpful to be able to stop the watch process when the queryno longer returns the expected amount of rows. An examplewould be to watch for the presence of a certain event inpg_stat_activity and stopping when the event is no longerpresent, or to watch an index creation and stop when theindex is created.This adds a min_rows=MIN parameter to \watch which can beset to a non-negative integer, and the watch query willstop executing when it returns less than MIN rows.Author: Greg Sabino Mullane <htamfids@gmail.com>Reviewed-by: Michael Paquier <michael@paquier.xyz>Reviewed-by: Daniel Gustafsson <daniel@yesql.se>Discussion:https://postgr.es/m/CAKAnmmKStATuddYxP71L+p0DHtp9Rvjze3XRoy0Dyw67VQ45UA@mail.gmail.com
1 parent95fff2a commitf347ec7

File tree

6 files changed

+72
-15
lines changed

6 files changed

+72
-15
lines changed

‎doc/src/sgml/ref/psql-ref.sgml

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -3566,13 +3566,14 @@ testdb=&gt; <userinput>\setenv LESS -imx4F</userinput>
35663566

35673567

35683568
<varlistentry id="app-psql-meta-command-watch">
3569-
<term><literal>\watch [ i[nterval]=<replaceable class="parameter">seconds</replaceable> ] [ c[ount]=<replaceable class="parameter">times</replaceable> ] [ <replaceable class="parameter">seconds</replaceable> ]</literal></term>
3569+
<term><literal>\watch [ i[nterval]=<replaceable class="parameter">seconds</replaceable> ] [ c[ount]=<replaceable class="parameter">times</replaceable> ] [m[in_rows]=<replaceable class="parameter">rows</replaceable> ] [<replaceable class="parameter">seconds</replaceable> ]</literal></term>
35703570
<listitem>
35713571
<para>
35723572
Repeatedly execute the current query buffer (as <literal>\g</literal> does)
35733573
until interrupted, or the query fails, or the execution count limit
3574-
(if given) is reached. Wait the specified number of
3575-
seconds (default 2) between executions. For backwards compatibility,
3574+
(if given) is reached, or the query no longer returns the minimum number
3575+
of rows. Wait the specified number of seconds (default 2) between executions.
3576+
For backwards compatibility,
35763577
<replaceable class="parameter">seconds</replaceable> can be specified
35773578
with or without an <literal>interval=</literal> prefix.
35783579
Each query result is

‎src/bin/psql/command.c

Lines changed: 27 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -162,7 +162,7 @@ static bool do_connect(enum trivalue reuse_previous_specification,
162162
staticbooldo_edit(constchar*filename_arg,PQExpBufferquery_buf,
163163
intlineno,booldiscard_on_quit,bool*edited);
164164
staticbooldo_shell(constchar*command);
165-
staticbooldo_watch(PQExpBufferquery_buf,doublesleep,intiter);
165+
staticbooldo_watch(PQExpBufferquery_buf,doublesleep,intiter,intmin_rows);
166166
staticboollookup_object_oid(EditableObjectTypeobj_type,constchar*desc,
167167
Oid*obj_oid);
168168
staticboolget_create_object_cmd(EditableObjectTypeobj_type,Oidoid,
@@ -2775,13 +2775,15 @@ exec_command_watch(PsqlScanState scan_state, bool active_branch,
27752775
{
27762776
boolhave_sleep= false;
27772777
boolhave_iter= false;
2778+
boolhave_min_rows= false;
27782779
doublesleep=2;
27792780
intiter=0;
2781+
intmin_rows=0;
27802782

27812783
/*
27822784
* Parse arguments. We allow either an unlabeled interval or
27832785
* "name=value", where name is from the set ('i', 'interval', 'c',
2784-
* 'count').
2786+
* 'count', 'm', 'min_rows').
27852787
*/
27862788
while (success)
27872789
{
@@ -2838,6 +2840,26 @@ exec_command_watch(PsqlScanState scan_state, bool active_branch,
28382840
}
28392841
}
28402842
}
2843+
elseif (strncmp("m=",opt,strlen("m="))==0||
2844+
strncmp("min_rows=",opt,strlen("min_rows="))==0)
2845+
{
2846+
if (have_min_rows)
2847+
{
2848+
pg_log_error("\\watch: minimum row count specified more than once");
2849+
success= false;
2850+
}
2851+
else
2852+
{
2853+
have_min_rows= true;
2854+
errno=0;
2855+
min_rows=strtoint(valptr,&opt_end,10);
2856+
if (min_rows <=0||*opt_end||errno==ERANGE)
2857+
{
2858+
pg_log_error("\\watch: incorrect minimum row count \"%s\"",valptr);
2859+
success= false;
2860+
}
2861+
}
2862+
}
28412863
else
28422864
{
28432865
pg_log_error("\\watch: unrecognized parameter \"%s\"",opt);
@@ -2874,7 +2896,7 @@ exec_command_watch(PsqlScanState scan_state, bool active_branch,
28742896
/* If query_buf is empty, recall and execute previous query */
28752897
(void)copy_previous_query(query_buf,previous_buf);
28762898

2877-
success=do_watch(query_buf,sleep,iter);
2899+
success=do_watch(query_buf,sleep,iter,min_rows);
28782900
}
28792901

28802902
/* Reset the query buffer as though for \r */
@@ -5144,7 +5166,7 @@ do_shell(const char *command)
51445166
* onto a bunch of exec_command's variables to silence stupider compilers.
51455167
*/
51465168
staticbool
5147-
do_watch(PQExpBufferquery_buf,doublesleep,intiter)
5169+
do_watch(PQExpBufferquery_buf,doublesleep,intiter,intmin_rows)
51485170
{
51495171
longsleep_ms= (long) (sleep*1000);
51505172
printQueryOptmyopt=pset.popt;
@@ -5274,7 +5296,7 @@ do_watch(PQExpBuffer query_buf, double sleep, int iter)
52745296
myopt.title=title;
52755297

52765298
/* Run the query and print out the result */
5277-
res=PSQLexecWatch(query_buf->data,&myopt,pagerpipe);
5299+
res=PSQLexecWatch(query_buf->data,&myopt,pagerpipe,min_rows);
52785300

52795301
/*
52805302
* PSQLexecWatch handles the case where we can no longer repeat the

‎src/bin/psql/common.c

Lines changed: 14 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -36,6 +36,7 @@ static intExecQueryAndProcessResults(const char *query,
3636
double*elapsed_msec,
3737
bool*svpt_gone_p,
3838
boolis_watch,
39+
intmin_rows,
3940
constprintQueryOpt*opt,
4041
FILE*printQueryFout);
4142
staticboolcommand_no_begin(constchar*query);
@@ -632,7 +633,7 @@ PSQLexec(const char *query)
632633
* e.g., because of the interrupt, -1 on error.
633634
*/
634635
int
635-
PSQLexecWatch(constchar*query,constprintQueryOpt*opt,FILE*printQueryFout)
636+
PSQLexecWatch(constchar*query,constprintQueryOpt*opt,FILE*printQueryFout,intmin_rows)
636637
{
637638
booltiming=pset.timing;
638639
doubleelapsed_msec=0;
@@ -646,7 +647,7 @@ PSQLexecWatch(const char *query, const printQueryOpt *opt, FILE *printQueryFout)
646647

647648
SetCancelConn(pset.db);
648649

649-
res=ExecQueryAndProcessResults(query,&elapsed_msec,NULL, true,opt,printQueryFout);
650+
res=ExecQueryAndProcessResults(query,&elapsed_msec,NULL, true,min_rows,opt,printQueryFout);
650651

651652
ResetCancelConn();
652653

@@ -1134,7 +1135,7 @@ SendQuery(const char *query)
11341135
pset.crosstab_flag|| !is_select_command(query))
11351136
{
11361137
/* Default fetch-it-all-and-print mode */
1137-
OK= (ExecQueryAndProcessResults(query,&elapsed_msec,&svpt_gone, false,NULL,NULL)>0);
1138+
OK= (ExecQueryAndProcessResults(query,&elapsed_msec,&svpt_gone, false,0,NULL,NULL)>0);
11381139
}
11391140
else
11401141
{
@@ -1415,11 +1416,12 @@ DescribeQuery(const char *query, double *elapsed_msec)
14151416
staticint
14161417
ExecQueryAndProcessResults(constchar*query,
14171418
double*elapsed_msec,bool*svpt_gone_p,
1418-
boolis_watch,
1419+
boolis_watch,intmin_rows,
14191420
constprintQueryOpt*opt,FILE*printQueryFout)
14201421
{
14211422
booltiming=pset.timing;
14221423
boolsuccess;
1424+
boolreturn_early= false;
14231425
instr_timebefore,
14241426
after;
14251427
PGresult*result;
@@ -1461,6 +1463,10 @@ ExecQueryAndProcessResults(const char *query,
14611463

14621464
/* first result */
14631465
result=PQgetResult(pset.db);
1466+
if (min_rows>0&&PQntuples(result)<min_rows)
1467+
{
1468+
return_early= true;
1469+
}
14641470

14651471
while (result!=NULL)
14661472
{
@@ -1683,7 +1689,10 @@ ExecQueryAndProcessResults(const char *query,
16831689
if (!CheckConnection())
16841690
return-1;
16851691

1686-
returncancel_pressed ?0 :success ?1 :-1;
1692+
if (cancel_pressed||return_early)
1693+
return0;
1694+
1695+
returnsuccess ?1 :-1;
16871696
}
16881697

16891698

‎src/bin/psql/common.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -32,7 +32,7 @@ extern void psql_setup_cancel_handler(void);
3232
externvoidSetShellResultVariables(intwait_result);
3333

3434
externPGresult*PSQLexec(constchar*query);
35-
externintPSQLexecWatch(constchar*query,constprintQueryOpt*opt,FILE*printQueryFout);
35+
externintPSQLexecWatch(constchar*query,constprintQueryOpt*opt,FILE*printQueryFout,intmin_rows);
3636

3737
externboolSendQuery(constchar*query);
3838

‎src/bin/psql/help.c

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -200,7 +200,9 @@ slashUsage(unsigned short int pager)
200200
HELP0(" \\gset [PREFIX] execute query and store result in psql variables\n");
201201
HELP0(" \\gx [(OPTIONS)] [FILE] as \\g, but forces expanded output mode\n");
202202
HELP0(" \\q quit psql\n");
203-
HELP0(" \\watch [[i=]SEC] [c=N] execute query every SEC seconds, up to N times\n");
203+
HELP0(" \\watch [[i=]SEC] [c=N] [m=MIN]\n");
204+
HELP0(" execute query every SEC seconds, up to N times\n");
205+
HELP0(" stop if less than MIN rows are returned\n");
204206
HELP0("\n");
205207

206208
HELP0("Help\n");

‎src/bin/psql/t/001_basic.pl

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -355,6 +355,29 @@ sub psql_fails_like
355355
psql_like($node,sprintf('SELECT 1 \watch c=3 i=%g', 0.01),
356356
qr/1\n1\n1/,'\watch with 3 iterations');
357357

358+
# Check \watch minimum row count
359+
psql_fails_like(
360+
$node,
361+
'SELECT 3 \watch m=x',
362+
qr/incorrect minimum row count/,
363+
'\watch, invalid minimum row setting');
364+
365+
psql_fails_like(
366+
$node,
367+
'SELECT 3 \watch m=1 min_rows=2',
368+
qr/minimum row count specified more than once/,
369+
'\watch, minimum rows is specified more than once');
370+
371+
psql_like(
372+
$node,
373+
q{with x as (
374+
select now()-backend_start AS howlong
375+
from pg_stat_activity
376+
where pid = pg_backend_pid()
377+
) select 123 from x where howlong < '2 seconds' \watch i=0.5 m=2},
378+
qr/^123$/,
379+
'\watch, 2 minimum rows');
380+
358381
# Check \watch errors
359382
psql_fails_like(
360383
$node,

0 commit comments

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