Postgresql – Check for blocked queries periodically and cancel them

postgresqlupdate

I have series of update queries which are executed every 5-10 seconds. Sometimes it happens that one of these will block all the pending update queries as for some reason it just freezes.

Since I can afford to lose an update is there are way to periodically check what queries are pending in the database, say for example for more that 1 minute, and subsequently cancel them?

Best Answer

You can do this by querying pg_stat_activity on newer PostgreSQL versions that record the query start time.

See pg_stat_activity in the PostgreSQL manual.

Use pg_cancel_backend to cancel the query, or pg_terminate_backend to close the connection running the query.

However, I strongly recommend that you instead fix the problem so that your queries don't get blocked. Look at pg_locks when it's stuck, determine why it's getting stuck, and fix the problem. Read the PostgreSQL manual chapter on explicit locking, and the wiki entries on lock monitoring.

Related Question