Those new to the realm of PostgreSQL from other databases will find little gems sprinkled liberally through the software. One of those gems is the watch command. It can be used to repeatedly run a query over and over again.
Pretend you are watching the progress of the importation of a CSV file and want to sample the progress every so often. Before you start the import, type query to sample the incoming data.
1 2 3 4 5 | blog=# SELECT MAX(id) FROM x1; max ------ (1 row) |
To get updates three times a minute, you would then do the following while the import runs.
1 2 3 4 5 6 7 8 9 | \watch 20 8/24/2022 11:22:34 AM (every 20s) max ------ 1000 (1 row) |
For MySQL-ers who miss the information from SHOW PROCESSLIST, try instead this combination. First, check the latest queries from the various sessions.
1 2 3 4 5 6 7 8 9 | blog=# SELECT datname, query, usename FROM pg_stat_activity ORDER BY query_start; datname | query | usename ---------+----------------------------------------------------------------------------+---------- blog | insert into x1 (id) values (generate_series(1300,1499)); | percona blog | SELECT datname, query, usename FROM pg_stat_activity ORDER BY query_start; | percona |
Then set a fifteen-second timer!
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | blog=# \watch 15 8/24/2022 11:39:00 AM (every 15s) datname | query | usename ---------+----------------------------------------------------------------------------+---------- blog | insert into x1 (id) values (generate_series(1300,1499)); | percona blog | SELECT datname, query, usename FROM pg_stat_activity ORDER BY query_start; | percona 8/24/2022 11:39:15 AM (every 15s) datname | query | usename ---------+----------------------------------------------------------------------------+---------- blog | insert into x1 (id) values (generate_series(2100,2499)); | percona blog | SELECT datname, query, usename FROM pg_stat_activity ORDER BY query_start; | percona |
The watch command comes in handy whenever you repetitively need to run a query.
