Here's a rebase, due to a conflict with 3a513067 "psql: Show all query
results by default" which moved a few things around making it harder
to use the pager for the right scope. Lacking time, I came up with
this change to PSQLexecWatch():
+ if (printQueryFout)
+ {
+ restoreQueryFout = pset.queryFout;
+ pset.queryFout = printQueryFout;
+ }
+
SetCancelConn(pset.db);
res = SendQueryAndProcessResults(query, &elapsed_msec, true);
ResetCancelConn();
fflush(pset.queryFout);
+ if (restoreQueryFout)
+ pset.queryFout = restoreQueryFout;
+
If someone has a tidier way to factor this, I'm keen to hear it. I'd
like to push this today.
From bd71cff8d25b919caeaab4db94b93d8647f6d911 Mon Sep 17 00:00:00 2001
From: Thomas Munro <thomas.mu...@gmail.com>
Date: Sat, 20 Mar 2021 21:54:27 +1300
Subject: [PATCH v5] Add PSQL_WATCH_PAGER for psql's \watch command.
Allow a pager to be used by the \watch command, which runs a query
periodically. It works but isn't very useful with traditional pagers
like "less", so use a different evironment variable to control it.
Pagers that understand the output format of psql such as the popular
open source tool "pspg" (also by Pavel Stehule) can use this to show the
results in a user-friendly format. Example: 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.steh...@gmail.com>
Author: Thomas Munro <thomas.mu...@gmail.com>
Discussion: https://postgr.es/m/CAFj8pRBfzUUPz-3gN5oAzto9SDuRSq-TQPfXU_P6h0L7hO%2BEhg%40mail.gmail.com
---
doc/src/sgml/ref/psql-ref.sgml | 28 +++++++
src/bin/psql/command.c | 132 ++++++++++++++++++++++++++++++---
src/bin/psql/common.c | 12 ++-
src/bin/psql/common.h | 2 +-
src/bin/psql/help.c | 6 +-
src/bin/psql/startup.c | 14 ++++
6 files changed, 182 insertions(+), 12 deletions(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index c1451c1672..45cfb8af8a 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -2990,6 +2990,16 @@ lo_import 152801
(such as <filename>more</filename>) is used.
</para>
+ <para>
+ When using the <literal>\watch</literal> command to execute a query
+ repeatedly, the environment variable <envar>PSQL_WATCH_PAGER</envar>
+ is used to find the pager program instead, on Unix systems. This is
+ configured separately because it may confuse traditional pagers, but
+ can be used to send output to tools that understand
+ <application>psql</application>'s output format (such as
+ <filename>pspg --stream</filename>).
+ </para>
+
<para>
When the <literal>pager</literal> option is <literal>off</literal>, the pager
program is not used. When the <literal>pager</literal> option is
@@ -4668,6 +4678,24 @@ PSQL_EDITOR_LINENUMBER_ARG='--line '
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><envar>PSQL_WATCH_PAGER</envar></term>
+
+ <listitem>
+ <para>
+ When a query is executed repeatedly with the <command>\watch</command>
+ command, a pager is not used by default. This behavior can be changed
+ by setting <envar>PSQL_WATCH_PAGER</envar> to a pager command, on Unix
+ systems. The <literal>pspg</literal> pager (not part of
+ <productname>PostgreSQL</productname> but available in many open source
+ software distributions) can display the output of
+ <command>\watch</command> if started with the option
+ <literal>--stream</literal>.
+ </para>
+
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><envar>PSQLRC</envar></term>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index e04ccc5b62..db9efe5012 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -13,6 +13,7 @@
#include <utime.h>
#ifndef WIN32
#include <sys/stat.h> /* for stat() */
+#include <sys/time.h> /* for setitimer() */
#include <fcntl.h> /* open() flags */
#include <unistd.h> /* for geteuid(), getpid(), stat() */
#else
@@ -4850,8 +4851,17 @@ do_watch(PQExpBuffer query_buf, double sleep)
const char *strftime_fmt;
const char *user_title;
char *title;
+ const char *pagerprog = NULL;
+ FILE *pagerpipe = NULL;
int title_len;
int res = 0;
+#ifndef WIN32
+ sigset_t sigalrm_sigchld_sigint;
+ sigset_t sigalrm_sigchld;
+ sigset_t sigint;
+ struct itimerval interval;
+ bool done = false;
+#endif
if (!query_buf || query_buf->len <= 0)
{
@@ -4859,6 +4869,58 @@ do_watch(PQExpBuffer query_buf, double sleep)
return false;
}
+#ifndef WIN32
+ sigemptyset(&sigalrm_sigchld_sigint);
+ sigaddset(&sigalrm_sigchld_sigint, SIGCHLD);
+ sigaddset(&sigalrm_sigchld_sigint, SIGALRM);
+ sigaddset(&sigalrm_sigchld_sigint, SIGINT);
+
+ sigemptyset(&sigalrm_sigchld);
+ sigaddset(&sigalrm_sigchld, SIGCHLD);
+ sigaddset(&sigalrm_sigchld, SIGALRM);
+
+ sigemptyset(&sigint);
+ sigaddset(&sigint, SIGINT);
+
+ /*
+ * Block SIGALRM and SIGCHLD before we start the timer and the pager (if
+ * configured), to avoid races. sigwait() will receive them.
+ */
+ sigprocmask(SIG_BLOCK, &sigalrm_sigchld, NULL);
+
+ /*
+ * Set a timer to interrupt sigwait() so we can run the query at the
+ * requested intervals.
+ */
+ interval.it_value.tv_sec = sleep_ms / 1000;
+ interval.it_value.tv_usec = (sleep_ms % 1000) * 1000;
+ interval.it_interval = interval.it_value;
+ if (setitimer(ITIMER_REAL, &interval, NULL) < 0)
+ {
+ pg_log_error("could not set timer: %m");
+ done = true;
+ }
+#endif
+
+ /*
+ * For \watch, we ignore the size of the result and always use the pager if
+ * PSQL_WATCH_PAGER is set. We also ignore the regular PSQL_PAGER or PAGER
+ * environment variables, because traditional pagers probably won't be very
+ * useful for showing a stream of results.
+ */
+#ifndef WIN32
+ pagerprog = getenv("PSQL_WATCH_PAGER");
+#endif
+ if (pagerprog && myopt.topt.pager)
+ {
+ disable_sigpipe_trap();
+ pagerpipe = popen(pagerprog, "w");
+
+ if (!pagerpipe)
+ /*silently proceed without pager */
+ restore_sigpipe_trap();
+ }
+
/*
* Choose format for timestamps. We might eventually make this a \pset
* option. In the meantime, using a variable for the format suppresses
@@ -4867,10 +4929,12 @@ do_watch(PQExpBuffer query_buf, double sleep)
strftime_fmt = "%c";
/*
- * Set up rendering options, in particular, disable the pager, because
- * nobody wants to be prompted while watching the output of 'watch'.
+ * Set up rendering options, in particular, disable the pager unless
+ * PSQL_WATCH_PAGER was successfully launched.
*/
- myopt.topt.pager = 0;
+ if (!pagerpipe)
+ myopt.topt.pager = 0;
+
/*
* If there's a title in the user configuration, make sure we have room
@@ -4885,7 +4949,6 @@ do_watch(PQExpBuffer query_buf, double sleep)
{
time_t timer;
char timebuf[128];
- long i;
/*
* Prepare title for output. Note that we intentionally include a
@@ -4904,7 +4967,7 @@ do_watch(PQExpBuffer query_buf, double sleep)
myopt.title = title;
/* Run the query and print out the results */
- res = PSQLexecWatch(query_buf->data, &myopt);
+ res = PSQLexecWatch(query_buf->data, &myopt, pagerpipe);
/*
* PSQLexecWatch handles the case where we can no longer repeat the
@@ -4913,6 +4976,10 @@ do_watch(PQExpBuffer query_buf, double sleep)
if (res <= 0)
break;
+ if (pagerpipe && ferror(pagerpipe))
+ break;
+
+#ifdef WIN32
/*
* Set up cancellation of 'watch' via SIGINT. We redo this each time
* through the loop since it's conceivable something inside
@@ -4923,12 +4990,10 @@ do_watch(PQExpBuffer query_buf, double sleep)
/*
* Enable 'watch' cancellations and wait a while before running the
- * query again. Break the sleep into short intervals (at most 1s)
- * since pg_usleep isn't interruptible on some platforms.
+ * query again. Break the sleep into short intervals (at most 1s).
*/
sigint_interrupt_enabled = true;
- i = sleep_ms;
- while (i > 0)
+ for (int i = sleep_ms; i > 0;)
{
long s = Min(i, 1000L);
@@ -4938,8 +5003,57 @@ do_watch(PQExpBuffer query_buf, double sleep)
i -= s;
}
sigint_interrupt_enabled = false;
+#else
+ /* sigwait() will handle SIGINT. */
+ sigprocmask(SIG_BLOCK, &sigint, NULL);
+ if (cancel_pressed)
+ done = true;
+
+ /* Wait for SIGINT, SIGCHLD or SIGALRM. */
+ while (!done)
+ {
+ int signal_received;
+
+ if (sigwait(&sigalrm_sigchld_sigint, &signal_received) < 0)
+ {
+ /* Some other signal arrived? */
+ if (errno == EINTR)
+ continue;
+ else
+ {
+ pg_log_error("could not wait for signals: %m");
+ done = true;
+ break;
+ }
+ }
+ /* On ^C or pager exit, it's time to stop running the query. */
+ if (signal_received == SIGINT || signal_received == SIGCHLD)
+ done = true;
+ /* Otherwise, we must have SIGALRM. Time to run the query again. */
+ break;
+ }
+
+ /* Unblock SIGINT so that slow queries can be interrupted. */
+ sigprocmask(SIG_UNBLOCK, &sigint, NULL);
+ if (done)
+ break;
+#endif
}
+ if (pagerpipe)
+ {
+ pclose(pagerpipe);
+ restore_sigpipe_trap();
+ }
+
+#ifndef WIN32
+ /* Disable the interval timer. */
+ memset(&interval, 0, sizeof(interval));
+ setitimer(ITIMER_REAL, &interval, NULL);
+ /* Unblock SIGINT, SIGCHLD and SIGALRM. */
+ sigprocmask(SIG_UNBLOCK, &sigalrm_sigchld_sigint, NULL);
+#endif
+
pg_free(title);
return (res >= 0);
}
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index 028a357991..e2f4372c3f 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -605,10 +605,11 @@ PSQLexec(const char *query)
* e.g., because of the interrupt, -1 on error.
*/
int
-PSQLexecWatch(const char *query, const printQueryOpt *opt)
+PSQLexecWatch(const char *query, const printQueryOpt *opt, FILE *printQueryFout)
{
double elapsed_msec = 0;
int res;
+ FILE *restoreQueryFout = NULL;
if (!pset.db)
{
@@ -616,12 +617,21 @@ PSQLexecWatch(const char *query, const printQueryOpt *opt)
return 0;
}
+ if (printQueryFout)
+ {
+ restoreQueryFout = pset.queryFout;
+ pset.queryFout = printQueryFout;
+ }
+
SetCancelConn(pset.db);
res = SendQueryAndProcessResults(query, &elapsed_msec, true);
ResetCancelConn();
fflush(pset.queryFout);
+ if (restoreQueryFout)
+ pset.queryFout = restoreQueryFout;
+
/* Possible microtiming output */
if (pset.timing)
PrintTiming(elapsed_msec);
diff --git a/src/bin/psql/common.h b/src/bin/psql/common.h
index 041b2ac068..d8538a4e06 100644
--- a/src/bin/psql/common.h
+++ b/src/bin/psql/common.h
@@ -29,7 +29,7 @@ extern sigjmp_buf sigint_interrupt_jmp;
extern void psql_setup_cancel_handler(void);
extern PGresult *PSQLexec(const char *query);
-extern int PSQLexecWatch(const char *query, const printQueryOpt *opt);
+extern int PSQLexecWatch(const char *query, const printQueryOpt *opt, FILE *printQueryFout);
extern bool SendQuery(const char *query);
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index ac9a89a889..0a7845d4b2 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -345,7 +345,7 @@ helpVariables(unsigned short int pager)
* Windows builds currently print one more line than non-Windows builds.
* Using the larger number is fine.
*/
- output = PageOutput(158, pager ? &(pset.popt.topt) : NULL);
+ output = PageOutput(160, pager ? &(pset.popt.topt) : NULL);
fprintf(output, _("List of specially treated variables\n\n"));
@@ -505,6 +505,10 @@ helpVariables(unsigned short int pager)
" alternative location for the command history file\n"));
fprintf(output, _(" PSQL_PAGER, PAGER\n"
" name of external pager program\n"));
+#ifndef WIN32
+ fprintf(output, _(" PSQL_WATCH_PAGER\n"
+ " name of external pager program used for \\watch\n"));
+#endif
fprintf(output, _(" PSQLRC\n"
" alternative location for the user's .psqlrc file\n"));
fprintf(output, _(" SHELL\n"
diff --git a/src/bin/psql/startup.c b/src/bin/psql/startup.c
index 17437ce07d..33949b12e8 100644
--- a/src/bin/psql/startup.c
+++ b/src/bin/psql/startup.c
@@ -110,6 +110,11 @@ log_locus_callback(const char **filename, uint64 *lineno)
}
}
+static void
+empty_signal_handler(SIGNAL_ARGS)
+{
+}
+
/*
*
* main
@@ -303,6 +308,15 @@ main(int argc, char *argv[])
psql_setup_cancel_handler();
+ /*
+ * do_watch() needs signal handlers installed (otherwise sigwait() will
+ * filter them out on some platforms), but doesn't need them to do
+ * anything, and they shouldn't ever run (unless perhaps a stray SIGALRM
+ * arrives due to a race when do_watch() cancels an itimer).
+ */
+ pqsignal(SIGCHLD, empty_signal_handler);
+ pqsignal(SIGALRM, empty_signal_handler);
+
PQsetNoticeProcessor(pset.db, NoticeProcessor, NULL);
SyncVariables();
--
2.30.1
