On 29th of August 2023, Daniel Gustafsson committed patch:

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

I love \watch. For those of you that don't know, it's a meta-command in psql.

It allows me to rerun the same query every so often.

Simplest approach:

$ SELECT COUNT(*) FROM pg_stat_activity \watch 1
Thu 07 Sep 2023 12:34:50 CEST (every 1s)
 
 COUNT
───────
     7
(1 ROW)
 
Thu 07 Sep 2023 12:34:51 CEST (every 1s)
 
 COUNT
───────
     7
(1 ROW)
...

It runs the select count(*) from pg_stat_activity, then sleeps 1s, and then repeats.

It's absolute godsend when looking at situation on server, currently running queries, or locks.

Before Pg 16 we just had \watch and \watch N where it rerun query every N seconds, or every 2 seconds if N wasn't there.

Now, though, we have more. Specifically we now, thanks to this patch, can make watch wait for a query to return certain number of rows.

For example, I can run:

$ SELECT pid FROM pg_stat_activity WHERE backend_type = 'client backend' \watch m=2
Thu 07 Sep 2023 12:39:08 CEST (every 2s)
 
  pid
────────
 177462
 177911
(2 ROWS)
 
Thu 07 Sep 2023 12:39:10 CEST (every 2s)
 
  pid
────────
 177462
 177911
(2 ROWS)
 
Thu 07 Sep 2023 12:39:12 CEST (every 2s)
 
  pid
────────
 177462
(1 ROW)

So that watch will rerun the query until it will return less than 2 rows (m= is short for min_rows=).

Thanks to this I can easily make things that will run once something is done.

For example, let's assume that I have long running transaction going, with pid = 12345. And I want to run vacuum as soon as it will be done.

To do so I can simply make test.sql with:

SELECT pid FROM pg_stat_activity WHERE pid = 12345 \watch m=1
vacuum;

and run it normally via psql -f test.sql, or \i test.sql.

As a DBA that uses psql exclusively, I just LOVE it. Thanks a lot.