Prevent psql \watch from running queries that return no rows

From: Greg Sabino Mullane <htamfids(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Prevent psql \watch from running queries that return no rows
Date: 2023-06-02 15:47:16
Message-ID: CAKAnmmKStATuddYxP71L+p0DHtp9Rvjze3XRoy0Dyw67VQ45UA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Attached is a patch to allow a new behavior for the \watch command in psql.
When enabled, this instructs \watch to stop running once the query returns
zero rows. The use case is the scenario in which you are watching the
output of something long-running such as pg_stat_progress_create_index, but
once it finishes you don't need thousands of runs showing empty rows from
the view.

This adds a new argument "zero" to the existing i=SEC and c=N arguments

Notes:

* Not completely convinced of the name "zero" (better than
"stop_when_no_rows_returned"). Considered adding a new x=y argument, or
overloading c (c=-1) but neither seemed very intuitive. On the other hand,
it's tempting to stick to a single method moving forward, although this is
a boolean option not a x=y one like the other two.

* Did not update help.c on purpose - no need to make \watch span two lines
there.

* Considered leaving early (e.g. don't display the last empty result) but
seemed better to show the final empty result as an explicit confirmation as
to why it stopped.

* Quick way to test:
select * from pg_stat_activity where backend_start > now() - '20
seconds'::interval;
\watch zero

Cheers,
Greg

Attachment Content-Type Size
psql_watch_exit_on_zero_rows_v1.patch application/octet-stream 7.2 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message James Coleman 2023-06-02 17:51:22 Re: RFC: Logging plan of the running query
Previous Message Tristan Partin 2023-06-02 15:13:44 Re: [PATCH] Missing dep on Catalog.pm in meson rules