I want to write a function that monitors for a particular query becoming a blocker (i.e. it is both blocked by a query, and is blocking another query) and terminate it. Here is my current code, which I have amended to use
left join for testing purposes so that it does not require the query to be blocked:
CREATE OR REPLACE FUNCTION monitor_sql_and_terminate_blocker(IN p_query text, OUT result text) RETURNS text AS $BODY$ DECLARE monitor_sql text; rec record; BEGIN monitor_sql = ' with blocker as ( select distinct waiting.pid pid , other.pid blocker from pg_stat_activity join pg_catalog.pg_locks waiting on waiting.pid = pg_stat_activity.procpid and not waiting.granted join pg_catalog.pg_locks other on ( ( other."database" = waiting."database" and other.relation = waiting.relation ) or other.transactionid = waiting.transactionid ) and other.pid <> waiting.pid where current_query not like ''%<IDLE>%'' ) , blockers as ( select pid, array_to_string(array_agg(blocker),'','') blocker_list from blocker group by pid ) , blocking as ( select blocker, array_to_string(array_agg(pid),'','') blocking_list from blocker group by blocker ) select procpid from pg_stat_activity left join blockers on blockers.pid = pg_stat_activity.procpid left join blocking on blocking.blocker = pg_stat_activity.procpid where current_query = ''' || p_query || ''' '; LOOP FOR rec IN EXECUTE monitor_sql LOOP RAISE NOTICE 'Terminating procpid %', rec.procpid; PERFORM pg_terminate_backend(rec.procpid); END LOOP; PERFORM pg_sleep(1); END LOOP; END; $BODY$ LANGUAGE plpgsql VOLATILE;
It is invoked like this:
select monitor_sql_and_terminate_blocker('select * from very_large_table')
However, it just loops infinitely and never does anything. If I run the query manually, it finds the process and returns the procpid which I can then terminate manually.
This is because of transaction isolation, the function only sees the queries that were running when it started. If I run the monitor function while the query is running, it kills it successfully and then keeps trying to kill it again and again. What can I do to work around it?
My current solution is to move the loop out into a shell script that runs psql to invoke a version of this code that runs once and then exits.