Let's say I have a query like so:
SELECT * FROM a WHERE a.Category = 'liquid' ORDER BY a.MeasurementTime DESC;
and I want to see the results coming into the database 'live'.
How can I write a query for Postresql which will repeat as soon as the query finishes?
You can use the \watch n command in the terminal to re-execute the query every n seconds.
Example:
postgre=# SELECT * FROM TABLE WHERE CONDITION
postgre=# \watch 5
-- now the "SELECT * FROM TABLE WHERE CONDITION" is re-executed every 5 seconds
watch -n1 'psql -h {ip} {db} {user} -c "select * from condition;"'
Make sure that you set the password of the {user} inside an environment variable:
Linux> export PGPASSWORD="password"
You can't see them 'live'. Queries complete before returning to calling environment.
You could wrap this in a cron job ( depending on your environment ) or similar scheduler and have them run every minute, or a function and add that to pgagent to be run every minute.
To have a dml statement constantly running is not really a good idea and i would not recommend it for performance and table management purposes.
however...
Within a function you can create a loop with a wait clause using pg_sleep and just no break clause, but really a job is the best way to go.
n seconds? I do not want to use a cron job or some external code to execute the query. I am viewing the data in the IntelliJ database viewer.Not answering the exact question, but this might be useful to someone with this question.
I wanted to monitor connection activity in pgadmin. Particularly because my web app kept maxing out the number of open connections by not closing them. So I added this query in pgadmin's query tool:
SELECT
application_name,
backend_start,
query_start,
state,
query
FROM pg_stat_activity
WHERE datname = 'blablabla';
And then ran this in my browser's development console to repeat the query every second. This is an ugly, but practical way to go about it if you just need to debug something once.
const interval = setInterval(() => document.querySelector('[data-label="Execute script"]').click(), 1000)
To clear this behavior, either reload the page or run this.
clearInterval(interval)
This looks like this:
