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
Appearance settings

Commit f347ec7

Browse filesBrowse files
danielgustafssondanielgustafsson
committed
Allow \watch queries to stop on minimum rows returned
When running a repeat query with \watch in psql, it can be helpful to be able to stop the watch process when the query no longer returns the expected amount of rows. An example would be to watch for the presence of a certain event in pg_stat_activity and stopping when the event is no longer present, or to watch an index creation and stop when the index is created. This adds a min_rows=MIN parameter to \watch which can be set to a non-negative integer, and the watch query will stop 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 parent 95fff2a commit f347ec7
Copy full SHA for f347ec7

File tree

Expand file treeCollapse file tree

6 files changed

+72
-15
lines changed
Filter options
Expand file treeCollapse file tree

6 files changed

+72
-15
lines changed

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

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/psql-ref.sgml
+4-3Lines 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

Copy file name to clipboardExpand all lines: src/bin/psql/command.c
+27-5Lines 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
static bool do_edit(const char *filename_arg, PQExpBuffer query_buf,
163163
int lineno, bool discard_on_quit, bool *edited);
164164
static bool do_shell(const char *command);
165-
static bool do_watch(PQExpBuffer query_buf, double sleep, int iter);
165+
static bool do_watch(PQExpBuffer query_buf, double sleep, int iter, int min_rows);
166166
static bool lookup_object_oid(EditableObjectType obj_type, const char *desc,
167167
Oid *obj_oid);
168168
static bool get_create_object_cmd(EditableObjectType obj_type, Oid oid,
@@ -2775,13 +2775,15 @@ exec_command_watch(PsqlScanState scan_state, bool active_branch,
27752775
{
27762776
bool have_sleep = false;
27772777
bool have_iter = false;
2778+
bool have_min_rows = false;
27782779
double sleep = 2;
27792780
int iter = 0;
2781+
int min_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+
else if (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
static bool
5147-
do_watch(PQExpBuffer query_buf, double sleep, int iter)
5169+
do_watch(PQExpBuffer query_buf, double sleep, int iter, int min_rows)
51485170
{
51495171
long sleep_ms = (long) (sleep * 1000);
51505172
printQueryOpt myopt = 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

Copy file name to clipboardExpand all lines: src/bin/psql/common.c
+14-5Lines changed: 14 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -36,6 +36,7 @@ static int ExecQueryAndProcessResults(const char *query,
3636
double *elapsed_msec,
3737
bool *svpt_gone_p,
3838
bool is_watch,
39+
int min_rows,
3940
const printQueryOpt *opt,
4041
FILE *printQueryFout);
4142
static bool command_no_begin(const char *query);
@@ -632,7 +633,7 @@ PSQLexec(const char *query)
632633
* e.g., because of the interrupt, -1 on error.
633634
*/
634635
int
635-
PSQLexecWatch(const char *query, const printQueryOpt *opt, FILE *printQueryFout)
636+
PSQLexecWatch(const char *query, const printQueryOpt *opt, FILE *printQueryFout, int min_rows)
636637
{
637638
bool timing = pset.timing;
638639
double elapsed_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
static int
14161417
ExecQueryAndProcessResults(const char *query,
14171418
double *elapsed_msec, bool *svpt_gone_p,
1418-
bool is_watch,
1419+
bool is_watch, int min_rows,
14191420
const printQueryOpt *opt, FILE *printQueryFout)
14201421
{
14211422
bool timing = pset.timing;
14221423
bool success;
1424+
bool return_early = false;
14231425
instr_time before,
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-
return cancel_pressed ? 0 : success ? 1 : -1;
1692+
if (cancel_pressed || return_early)
1693+
return 0;
1694+
1695+
return success ? 1 : -1;
16871696
}
16881697

16891698

‎src/bin/psql/common.h

Copy file name to clipboardExpand all lines: src/bin/psql/common.h
+1-1Lines 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
extern void SetShellResultVariables(int wait_result);
3333

3434
extern PGresult *PSQLexec(const char *query);
35-
extern int PSQLexecWatch(const char *query, const printQueryOpt *opt, FILE *printQueryFout);
35+
extern int PSQLexecWatch(const char *query, const printQueryOpt *opt, FILE *printQueryFout, int min_rows);
3636

3737
extern bool SendQuery(const char *query);
3838

‎src/bin/psql/help.c

Copy file name to clipboardExpand all lines: src/bin/psql/help.c
+3-1Lines 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

Copy file name to clipboardExpand all lines: src/bin/psql/t/001_basic.pl
+23Lines 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 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.