Add \watch [SEC] command to psql.

This allows convenient re-execution of commands.

Will Leinweber, reviewed by Peter Eisentraut, Daniel Farina, and Tom Lane

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index c6347cd6f84075adb190deb62ac0437f611de6ce..7547e51b5eae54dd758381c00c131c87ac1cf604 100644 (file)
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -2478,6 +2478,18 @@ testdb=&gt; <userinput>\setenv LESS -imx4F</userinput>
       </varlistentry>
 
 
+      <varlistentry>
+        <term><literal>\watch [ <replaceable class="parameter">seconds</replaceable> ]</literal></term>
+        <listitem>
+        <para>
+        Repeatedly execute the current query buffer (like <literal>\g</>)
+        until interrupted or the query fails.  Wait the specified number of
+        seconds (default 2) between executions.
+        </para>
+        </listitem>
+      </varlistentry>
+
+
       <varlistentry>
         <term><literal>\x [ <replaceable class="parameter">on</replaceable> | <replaceable class="parameter">off</replaceable> | <replaceable class="parameter">auto</replaceable> ]</literal></term>
         <listitem>

diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 33bc2a7e4c54ee0e282f2e770556b7fba2beed1f..09939fda5dd1ec6f07caf442ed0afc615a2a6dc5 100644 (file)
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -60,6 +60,7 @@ static bool do_edit(const char *filename_arg, PQExpBuffer query_buf,
        int lineno, bool *edited);
 static bool do_connect(char *dbname, char *user, char *host, char *port);
 static bool do_shell(const char *command);
+static bool do_watch(PQExpBuffer query_buf, long sleep);
 static bool lookup_function_oid(PGconn *conn, const char *desc, Oid *foid);
 static bool get_create_function_cmd(PGconn *conn, Oid oid, PQExpBuffer buf);
 static int strip_lineno_from_funcdesc(char *func);
@@ -1433,6 +1434,29 @@ exec_command(const char *cmd,
        free(fname);
    }
 
+   /* \watch -- execute a query every N seconds */
+   else if (strcmp(cmd, "watch") == 0)
+   {
+       char       *opt = psql_scan_slash_option(scan_state,
+                                                OT_NORMAL, NULL, true);
+       long        sleep = 2;
+
+       /* Convert optional sleep-length argument */
+       if (opt)
+       {
+           sleep = strtol(opt, NULL, 10);
+           if (sleep <= 0)
+               sleep = 1;
+           free(opt);
+       }
+
+       success = do_watch(query_buf, sleep);
+
+       /* Reset the query buffer as though for \r */
+       resetPQExpBuffer(query_buf);
+       psql_scan_reset(scan_state);
+   }
+
    /* \x -- set or toggle expanded table representation */
    else if (strcmp(cmd, "x") == 0)
    {
@@ -2555,6 +2579,112 @@ do_shell(const char *command)
    return true;
 }
 
+/*
+ * do_watch -- handler for \watch
+ *
+ * We break this out of exec_command to avoid having to plaster "volatile"
+ * onto a bunch of exec_command's variables to silence stupider compilers.
+ */
+static bool
+do_watch(PQExpBuffer query_buf, long sleep)
+{
+   printQueryOpt myopt = pset.popt;
+   char        title[50];
+
+   if (!query_buf || query_buf->len <= 0)
+   {
+       psql_error(_("\\watch cannot be used with an empty query\n"));
+       return false;
+   }
+
+   /*
+    * Set up rendering options, in particular, disable the pager, because
+    * nobody wants to be prompted while watching the output of 'watch'.
+    */
+   myopt.nullPrint = NULL;
+   myopt.topt.pager = 0;
+
+   for (;;)
+   {
+       PGresult   *res;
+       time_t      timer;
+       long        i;
+
+       /*
+        * Prepare title for output.  XXX would it be better to use the time
+        * of completion of the command?
+        */
+       timer = time(NULL);
+       snprintf(title, sizeof(title), _("Watch every %lds\t%s"),
+                sleep, asctime(localtime(&timer)));
+       myopt.title = title;
+
+       /*
+        * Run the query.  We use PSQLexec, which is kind of cheating, but
+        * SendQuery doesn't let us suppress autocommit behavior.
+        */
+       res = PSQLexec(query_buf->data, false);
+
+       /* PSQLexec handles failure results and returns NULL */
+       if (res == NULL)
+           break;
+
+       /*
+        * If SIGINT is sent while the query is processing, PSQLexec will
+        * consume the interrupt.  The user's intention, though, is to cancel
+        * the entire watch process, so detect a sent cancellation request and
+        * exit in this case.
+        */
+       if (cancel_pressed)
+       {
+           PQclear(res);
+           break;
+       }
+
+       switch (PQresultStatus(res))
+       {
+           case PGRES_TUPLES_OK:
+               printQuery(res, &myopt, pset.queryFout, pset.logfile);
+               break;
+
+           case PGRES_EMPTY_QUERY:
+               psql_error(_("\\watch cannot be used with an empty query\n"));
+               PQclear(res);
+               return false;
+
+           default:
+               /* should we fail for non-tuple-result commands? */
+               break;
+       }
+
+       PQclear(res);
+
+       /*
+        * Set up cancellation of 'watch' via SIGINT.  We redo this each time
+        * through the loop since it's conceivable something inside PSQLexec
+        * could change sigint_interrupt_jmp.
+        */
+       if (sigsetjmp(sigint_interrupt_jmp, 1) != 0)
+           break;
+
+       /*
+        * Enable 'watch' cancellations and wait a while before running the
+        * query again.  Break the sleep into short intervals since pg_usleep
+        * isn't interruptible on some platforms.
+        */
+       sigint_interrupt_enabled = true;
+       for (i = 0; i < sleep; i++)
+       {
+           pg_usleep(1000000L);
+           if (cancel_pressed)
+               break;
+       }
+       sigint_interrupt_enabled = false;
+   }
+
+   return true;
+}
+
 /*
  * This function takes a function description, e.g. "x" or "x(int)", and
  * issues a query on the given connection to retrieve the function's OID

diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index ccb307b791f53a09283b13bd0a9612b1a83e1a15..3c7442a0733162173102bd564bfe5f32a49415f8 100644 (file)
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -165,7 +165,7 @@ slashUsage(unsigned short int pager)
 
    currdb = PQdb(pset.db);
 
-   output = PageOutput(95, pager);
+   output = PageOutput(96, pager);
 
    /* if you add/remove a line here, change the row count above */
 
@@ -175,6 +175,7 @@ slashUsage(unsigned short int pager)
    fprintf(output, _("  \\gset [PREFIX]         execute query and store results in psql variables\n"));
    fprintf(output, _("  \\h [NAME]              help on syntax of SQL commands, * for all commands\n"));
    fprintf(output, _("  \\q                     quit psql\n"));
+   fprintf(output, _("  \\watch [SEC]           execute query every SEC seconds\n"));
    fprintf(output, "\n");
 
    fprintf(output, _("Query Buffer\n"));

diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index d2170308f7c8a8f37b7d80369a45e8b2e4fde3f0..7d2c812612659074641e03c847a157f1f56132b1 100644 (file)
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -900,7 +900,7 @@ psql_completion(char *text, int start, int end)
        "\\lo_import", "\\lo_export", "\\lo_list", "\\lo_unlink",
        "\\o", "\\p", "\\password", "\\prompt", "\\pset", "\\q", "\\qecho", "\\r",
        "\\set", "\\sf", "\\t", "\\T",
-       "\\timing", "\\unset", "\\x", "\\w", "\\z", "\\!", NULL
+       "\\timing", "\\unset", "\\x", "\\w", "\\watch", "\\z", "\\!", NULL
    };
 
    (void) end;                 /* not used */