Waiting for 9.3 – Add \watch [SEC] command to psql.

On 4th of April, Tom Lane committed patch:

Add \watch [SEC] command to psql.
 
This allows convenient re-execution of commands.
 
Will Leinweber, reviewed by Peter Eisentraut, Daniel Farina, and Tom Lane

Psql is the best client for PostgreSQL (in my not so humble opinion). It can do many cool stuff, and with some tinkering even more.

That's why I'm always enthusiastic about new features in it – as I will be using them on daily basis.

This new patch makes it simple to rerun given query every so often using nothing but psql. How it works? Let's see:

$ \x
Expanded display IS ON.
 
$ SELECT now() - query_start, state, query FROM pg_stat_activity \watch 5
Watch every 5s  Sun Apr  7 12:08:02 2013
 
-[ RECORD 1 ]-------------------------------------------------------------
?COLUMN? | 
state    | idle
query    | 
-[ RECORD 2 ]-------------------------------------------------------------
?COLUMN? | 00:00:00
state    | active
query    | SELECT now() - query_start, state, query FROM pg_stat_activity 
 
Watch every 5s  Sun Apr  7 12:08:07 2013
 
-[ RECORD 1 ]-------------------------------------------------------------
?COLUMN? | 
state    | idle
query    | 
-[ RECORD 2 ]-------------------------------------------------------------
?COLUMN? | 00:00:00
state    | active
query    | SELECT now() - query_start, state, query FROM pg_stat_activity 
 
Watch every 5s  Sun Apr  7 12:08:12 2013
 
-[ RECORD 1 ]-------------------------------------------------------------
?COLUMN? | 00:00:01.00034
state    | idle
query    | SELECT * FROM pg_stat_activity 
-[ RECORD 2 ]-------------------------------------------------------------
?COLUMN? | 00:00:00
state    | active
query    | SELECT now() - query_start, state, query FROM pg_stat_activity 
 
Watch every 5s  Sun Apr  7 12:08:18 2013
 
-[ RECORD 1 ]-------------------------------------------------------------
...

New results are printed every 5 seconds. There is no screen cleanup, so just whenever 5 seconds passed, query it run, and output printed to screen using setup that was set before \watch'ing, with a one-line header that informs us that it is indeed a watch, and what is the time the query has been started.

This will be probably mostly useful with administration/monitoring type of queries (like checking pg_stat_activity, pg_locks or the like), but it can be used for other purposes too.

Query that will be run using \watch will never end, unless there will be an error, or you'll stop the loop using ctrl-c.

All in all, it looks great – I had this type of ability earlier, with Curo, but \watch is more general, and does similar thing with less work. Great stuff, thanks Will.

4 thoughts on “Waiting for 9.3 – Add \watch [SEC] command to psql.”

  1. note that you can use something very similar with a shell loop or even easier, when you don’t care about the old output (just the current output), with watch(1).

  2. @Tobias:
    there are couple of issues though:
    1. watch(1) is not always available – for example – Solaris
    2. shell loop has the drawback of overhead of starting new connection for every query.

    So – while it is technically possible to do the same thing (and more) using other means – it’s good that we get this to work in plain psql, in the same connection, with minimal overhead.

  3. The example doesn’t actually show \watch being invoked, it just appears to magically take effect without being typed.

Comments are closed.