PostgreSQL 17: Allow “\watch” to stop based on the number of rows returned

psql already is really powerful but still it gets more features with almost every release of PostgreSQL. One example is the “\watch” meta command. This command was introduced in PostgreSQL 9.3 and hasn’t changed much since then. PostgreSQL 17, probably next year, will come with an extended “\watch” meta command.

Before we look at what was added lets briefly look at what “\watch” is doing up to version 16 of PostgreSQL. This meta command always becomes handy when you want to monitor some progress of something in PostgreSQL, e.g. the number of active sessions:

postgres=# select count(*) from pg_stat_activity where state = 'active';
 count 
-------
     1
(1 row)

Without the “\watch” command you either have to repeat the query on your own, or you need to schedule it somehow. This is what “\watch” is doing for you. Without any parameters “\watch” will execute your last query every two seconds:

postgres=# \watch
Mon 04 Sep 2023 11:40:13 AM CEST (every 2s)

 count 
-------
     1
(1 row)

Mon 04 Sep 2023 11:40:15 AM CEST (every 2s)

 count 
-------
     1
(1 row)
...

The amount of control you have up to PostgreSQL 16 is, that you can specify the internal:

postgres=# \watch 3
Mon 04 Sep 2023 11:41:35 AM CEST (every 3s)

 count 
-------
     1
(1 row)

Mon 04 Sep 2023 11:41:38 AM CEST (every 3s)

 count 
-------
     1
(1 row)

Starting with PostgreSQL 16 there will be an additional switch. You can tell “\watch” to automatically stop executing after a given execution count is reached, e.g.

postgres=# \watch interval=1 count=3
Mon 04 Sep 2023 11:47:42 AM CEST (every 1s)

 count 
-------
     1
(1 row)

Mon 04 Sep 2023 11:47:43 AM CEST (every 1s)

 count 
-------
     1
(1 row)

Mon 04 Sep 2023 11:47:44 AM CEST (every 1s)

 count 
-------
     1
(1 row)


postgres=# 

PostgreSQL 17 will bring an additional option: Stop executing when the query does not anymore return a certain amount of rows. For this we’ll need another session in the database:

-- second session
postgres=# select 1;
 ?column? 
----------
        1
(1 row)

postgres=# 

In the first session we ask for the database name of all sessions from the postgres user and tell “\watch” to stop when that count goes below 3:

postgres=# select datname from pg_stat_activity where usename = 'postgres';
 datname  
----------
 
 postgres
 postgres
(4 rows)

postgres=# \watch min_rows=3
Mon 04 Sep 2023 11:56:53 AM CEST (every 2s)

 datname  
----------
 
 postgres
 postgres
(3 rows)

Mon 04 Sep 2023 11:56:55 AM CEST (every 2s)

 datname  
----------
 
 postgres
 postgres
(3 rows)

-- exit in the second session

Mon 04 Sep 2023 11:56:57 AM CEST (every 2s)

 datname  
----------
 
 postgres
(2 rows)

As soon as we exit the second session “\watch” will stop as the number of rows returned drops to two.