Skip to content

Commit 7c09d27

Browse files
macdicemacdice
committed
Add PSQL_WATCH_PAGER for psql's \watch command.
Allow a pager to be used by the \watch command. This works but isn't very useful with traditional pagers like "less", so use a different environment variable. The popular open source tool "pspg" (also by Pavel) knows how to display the output if you set PSQL_WATCH_PAGER="pspg --stream". To make \watch react quickly when the user quits the pager or presses ^C, and also to increase the accuracy of its timing and decrease the rate of useless context switches, change the main loop of the \watch command to use sigwait() rather than a sleeping/polling loop, on Unix. Supported on Unix only for now (like pspg). Author: Pavel Stehule <pavel.stehule@gmail.com> Author: Thomas Munro <thomas.munro@gmail.com> Discussion: https://postgr.es/m/CAFj8pRBfzUUPz-3gN5oAzto9SDuRSq-TQPfXU_P6h0L7hO%2BEhg%40mail.gmail.com
1 parent f014b1b commit 7c09d27

File tree

6 files changed

+184
-15
lines changed

6 files changed

+184
-15
lines changed

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

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3002,6 +3002,16 @@ lo_import 152801
30023002
(such as <filename>more</filename>) is used.
30033003
</para>
30043004

3005+
<para>
3006+
When using the <literal>\watch</literal> command to execute a query
3007+
repeatedly, the environment variable <envar>PSQL_WATCH_PAGER</envar>
3008+
is used to find the pager program instead, on Unix systems. This is
3009+
configured separately because it may confuse traditional pagers, but
3010+
can be used to send output to tools that understand
3011+
<application>psql</application>'s output format (such as
3012+
<filename>pspg --stream</filename>).
3013+
</para>
3014+
30053015
<para>
30063016
When the <literal>pager</literal> option is <literal>off</literal>, the pager
30073017
program is not used. When the <literal>pager</literal> option is
@@ -4672,6 +4682,24 @@ PSQL_EDITOR_LINENUMBER_ARG='--line '
46724682
</listitem>
46734683
</varlistentry>
46744684

4685+
<varlistentry>
4686+
<term><envar>PSQL_WATCH_PAGER</envar></term>
4687+
4688+
<listitem>
4689+
<para>
4690+
When a query is executed repeatedly with the <command>\watch</command>
4691+
command, a pager is not used by default. This behavior can be changed
4692+
by setting <envar>PSQL_WATCH_PAGER</envar> to a pager command, on Unix
4693+
systems. The <literal>pspg</literal> pager (not part of
4694+
<productname>PostgreSQL</productname> but available in many open source
4695+
software distributions) can display the output of
4696+
<command>\watch</command> if started with the option
4697+
<literal>--stream</literal>.
4698+
</para>
4699+
4700+
</listitem>
4701+
</varlistentry>
4702+
46754703
<varlistentry>
46764704
<term><envar>PSQLRC</envar></term>
46774705

‎src/bin/psql/command.c

Lines changed: 124 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -13,6 +13,7 @@
1313
#include <utime.h>
1414
#ifndef WIN32
1515
#include <sys/stat.h> /* for stat() */
16+
#include <sys/time.h> /* for setitimer() */
1617
#include <fcntl.h> /* open() flags */
1718
#include <unistd.h> /* for geteuid(), getpid(), stat() */
1819
#else
@@ -4894,15 +4895,76 @@ do_watch(PQExpBuffer query_buf, double sleep)
48944895
const char *strftime_fmt;
48954896
const char *user_title;
48964897
char *title;
4898+
const char *pagerprog = NULL;
4899+
FILE *pagerpipe = NULL;
48974900
int title_len;
48984901
int res = 0;
4902+
#ifndef WIN32
4903+
sigset_t sigalrm_sigchld_sigint;
4904+
sigset_t sigalrm_sigchld;
4905+
sigset_t sigint;
4906+
struct itimerval interval;
4907+
bool done = false;
4908+
#endif
48994909

49004910
if (!query_buf || query_buf->len <= 0)
49014911
{
49024912
pg_log_error("\\watch cannot be used with an empty query");
49034913
return false;
49044914
}
49054915

4916+
#ifndef WIN32
4917+
sigemptyset(&sigalrm_sigchld_sigint);
4918+
sigaddset(&sigalrm_sigchld_sigint, SIGCHLD);
4919+
sigaddset(&sigalrm_sigchld_sigint, SIGALRM);
4920+
sigaddset(&sigalrm_sigchld_sigint, SIGINT);
4921+
4922+
sigemptyset(&sigalrm_sigchld);
4923+
sigaddset(&sigalrm_sigchld, SIGCHLD);
4924+
sigaddset(&sigalrm_sigchld, SIGALRM);
4925+
4926+
sigemptyset(&sigint);
4927+
sigaddset(&sigint, SIGINT);
4928+
4929+
/*
4930+
* Block SIGALRM and SIGCHLD before we start the timer and the pager (if
4931+
* configured), to avoid races. sigwait() will receive them.
4932+
*/
4933+
sigprocmask(SIG_BLOCK, &sigalrm_sigchld, NULL);
4934+
4935+
/*
4936+
* Set a timer to interrupt sigwait() so we can run the query at the
4937+
* requested intervals.
4938+
*/
4939+
interval.it_value.tv_sec = sleep_ms / 1000;
4940+
interval.it_value.tv_usec = (sleep_ms % 1000) * 1000;
4941+
interval.it_interval = interval.it_value;
4942+
if (setitimer(ITIMER_REAL, &interval, NULL) < 0)
4943+
{
4944+
pg_log_error("could not set timer: %m");
4945+
done = true;
4946+
}
4947+
#endif
4948+
4949+
/*
4950+
* For \watch, we ignore the size of the result and always use the pager
4951+
* if PSQL_WATCH_PAGER is set. We also ignore the regular PSQL_PAGER or
4952+
* PAGER environment variables, because traditional pagers probably won't
4953+
* be very useful for showing a stream of results.
4954+
*/
4955+
#ifndef WIN32
4956+
pagerprog = getenv("PSQL_WATCH_PAGER");
4957+
#endif
4958+
if (pagerprog && myopt.topt.pager)
4959+
{
4960+
disable_sigpipe_trap();
4961+
pagerpipe = popen(pagerprog, "w");
4962+
4963+
if (!pagerpipe)
4964+
/* silently proceed without pager */
4965+
restore_sigpipe_trap();
4966+
}
4967+
49064968
/*
49074969
* Choose format for timestamps. We might eventually make this a \pset
49084970
* option. In the meantime, using a variable for the format suppresses
@@ -4911,10 +4973,12 @@ do_watch(PQExpBuffer query_buf, double sleep)
49114973
strftime_fmt = "%c";
49124974

49134975
/*
4914-
* Set up rendering options, in particular, disable the pager, because
4915-
* nobody wants to be prompted while watching the output of 'watch'.
4976+
* Set up rendering options, in particular, disable the pager unless
4977+
* PSQL_WATCH_PAGER was successfully launched.
49164978
*/
4917-
myopt.topt.pager = 0;
4979+
if (!pagerpipe)
4980+
myopt.topt.pager = 0;
4981+
49184982

49194983
/*
49204984
* If there's a title in the user configuration, make sure we have room
@@ -4929,7 +4993,6 @@ do_watch(PQExpBuffer query_buf, double sleep)
49294993
{
49304994
time_t timer;
49314995
char timebuf[128];
4932-
long i;
49334996

49344997
/*
49354998
* Prepare title for output. Note that we intentionally include a
@@ -4948,7 +5011,7 @@ do_watch(PQExpBuffer query_buf, double sleep)
49485011
myopt.title = title;
49495012

49505013
/* Run the query and print out the results */
4951-
res = PSQLexecWatch(query_buf->data, &myopt);
5014+
res = PSQLexecWatch(query_buf->data, &myopt, pagerpipe);
49525015

49535016
/*
49545017
* PSQLexecWatch handles the case where we can no longer repeat the
@@ -4957,6 +5020,11 @@ do_watch(PQExpBuffer query_buf, double sleep)
49575020
if (res <= 0)
49585021
break;
49595022

5023+
if (pagerpipe && ferror(pagerpipe))
5024+
break;
5025+
5026+
#ifdef WIN32
5027+
49605028
/*
49615029
* Set up cancellation of 'watch' via SIGINT. We redo this each time
49625030
* through the loop since it's conceivable something inside
@@ -4967,12 +5035,10 @@ do_watch(PQExpBuffer query_buf, double sleep)
49675035

49685036
/*
49695037
* Enable 'watch' cancellations and wait a while before running the
4970-
* query again. Break the sleep into short intervals (at most 1s)
4971-
* since pg_usleep isn't interruptible on some platforms.
5038+
* query again. Break the sleep into short intervals (at most 1s).
49725039
*/
49735040
sigint_interrupt_enabled = true;
4974-
i = sleep_ms;
4975-
while (i > 0)
5041+
for (long i = sleep_ms; i > 0;)
49765042
{
49775043
long s = Min(i, 1000L);
49785044

@@ -4982,8 +5048,57 @@ do_watch(PQExpBuffer query_buf, double sleep)
49825048
i -= s;
49835049
}
49845050
sigint_interrupt_enabled = false;
5051+
#else
5052+
/* sigwait() will handle SIGINT. */
5053+
sigprocmask(SIG_BLOCK, &sigint, NULL);
5054+
if (cancel_pressed)
5055+
done = true;
5056+
5057+
/* Wait for SIGINT, SIGCHLD or SIGALRM. */
5058+
while (!done)
5059+
{
5060+
int signal_received;
5061+
5062+
if (sigwait(&sigalrm_sigchld_sigint, &signal_received) < 0)
5063+
{
5064+
/* Some other signal arrived? */
5065+
if (errno == EINTR)
5066+
continue;
5067+
else
5068+
{
5069+
pg_log_error("could not wait for signals: %m");
5070+
done = true;
5071+
break;
5072+
}
5073+
}
5074+
/* On ^C or pager exit, it's time to stop running the query. */
5075+
if (signal_received == SIGINT || signal_received == SIGCHLD)
5076+
done = true;
5077+
/* Otherwise, we must have SIGALRM. Time to run the query again. */
5078+
break;
5079+
}
5080+
5081+
/* Unblock SIGINT so that slow queries can be interrupted. */
5082+
sigprocmask(SIG_UNBLOCK, &sigint, NULL);
5083+
if (done)
5084+
break;
5085+
#endif
49855086
}
49865087

5088+
if (pagerpipe)
5089+
{
5090+
pclose(pagerpipe);
5091+
restore_sigpipe_trap();
5092+
}
5093+
5094+
#ifndef WIN32
5095+
/* Disable the interval timer. */
5096+
memset(&interval, 0, sizeof(interval));
5097+
setitimer(ITIMER_REAL, &interval, NULL);
5098+
/* Unblock SIGINT, SIGCHLD and SIGALRM. */
5099+
sigprocmask(SIG_UNBLOCK, &sigalrm_sigchld_sigint, NULL);
5100+
#endif
5101+
49875102
pg_free(title);
49885103
return (res >= 0);
49895104
}

‎src/bin/psql/common.c

Lines changed: 7 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -592,12 +592,13 @@ PSQLexec(const char *query)
592592
* e.g., because of the interrupt, -1 on error.
593593
*/
594594
int
595-
PSQLexecWatch(const char *query, const printQueryOpt *opt)
595+
PSQLexecWatch(const char *query, const printQueryOpt *opt, FILE *printQueryFout)
596596
{
597597
PGresult *res;
598598
double elapsed_msec = 0;
599599
instr_time before;
600600
instr_time after;
601+
FILE *fout;
601602

602603
if (!pset.db)
603604
{
@@ -638,14 +639,16 @@ PSQLexecWatch(const char *query, const printQueryOpt *opt)
638639
return 0;
639640
}
640641

642+
fout = printQueryFout ? printQueryFout : pset.queryFout;
643+
641644
switch (PQresultStatus(res))
642645
{
643646
case PGRES_TUPLES_OK:
644-
printQuery(res, opt, pset.queryFout, false, pset.logfile);
647+
printQuery(res, opt, fout, false, pset.logfile);
645648
break;
646649

647650
case PGRES_COMMAND_OK:
648-
fprintf(pset.queryFout, "%s\n%s\n\n", opt->title, PQcmdStatus(res));
651+
fprintf(fout, "%s\n%s\n\n", opt->title, PQcmdStatus(res));
649652
break;
650653

651654
case PGRES_EMPTY_QUERY:
@@ -668,7 +671,7 @@ PSQLexecWatch(const char *query, const printQueryOpt *opt)
668671

669672
PQclear(res);
670673

671-
fflush(pset.queryFout);
674+
fflush(fout);
672675

673676
/* Possible microtiming output */
674677
if (pset.timing)

‎src/bin/psql/common.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -29,7 +29,7 @@ extern sigjmp_buf sigint_interrupt_jmp;
2929
extern void psql_setup_cancel_handler(void);
3030

3131
extern PGresult *PSQLexec(const char *query);
32-
extern int PSQLexecWatch(const char *query, const printQueryOpt *opt);
32+
extern int PSQLexecWatch(const char *query, const printQueryOpt *opt, FILE *printQueryFout);
3333

3434
extern bool SendQuery(const char *query);
3535

‎src/bin/psql/help.c

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -347,7 +347,7 @@ helpVariables(unsigned short int pager)
347347
* Windows builds currently print one more line than non-Windows builds.
348348
* Using the larger number is fine.
349349
*/
350-
output = PageOutput(158, pager ? &(pset.popt.topt) : NULL);
350+
output = PageOutput(160, pager ? &(pset.popt.topt) : NULL);
351351

352352
fprintf(output, _("List of specially treated variables\n\n"));
353353

@@ -505,6 +505,10 @@ helpVariables(unsigned short int pager)
505505
" alternative location for the command history file\n"));
506506
fprintf(output, _(" PSQL_PAGER, PAGER\n"
507507
" name of external pager program\n"));
508+
#ifndef WIN32
509+
fprintf(output, _(" PSQL_WATCH_PAGER\n"
510+
" name of external pager program used for \\watch\n"));
511+
#endif
508512
fprintf(output, _(" PSQLRC\n"
509513
" alternative location for the user's .psqlrc file\n"));
510514
fprintf(output, _(" SHELL\n"

‎src/bin/psql/startup.c

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -110,6 +110,13 @@ log_locus_callback(const char **filename, uint64 *lineno)
110110
}
111111
}
112112

113+
#ifndef WIN32
114+
static void
115+
empty_signal_handler(SIGNAL_ARGS)
116+
{
117+
}
118+
#endif
119+
113120
/*
114121
*
115122
* main
@@ -302,6 +309,18 @@ main(int argc, char *argv[])
302309

303310
psql_setup_cancel_handler();
304311

312+
#ifndef WIN32
313+
314+
/*
315+
* do_watch() needs signal handlers installed (otherwise sigwait() will
316+
* filter them out on some platforms), but doesn't need them to do
317+
* anything, and they shouldn't ever run (unless perhaps a stray SIGALRM
318+
* arrives due to a race when do_watch() cancels an itimer).
319+
*/
320+
pqsignal(SIGCHLD, empty_signal_handler);
321+
pqsignal(SIGALRM, empty_signal_handler);
322+
#endif
323+
305324
PQsetNoticeProcessor(pset.db, NoticeProcessor, NULL);
306325

307326
SyncVariables();

0 commit comments

Comments
 (0)