Can a plpgsql stored procedure monitor for a query and kill it?

1 vote
1 answer
427 views
                          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 ''%%'' 
    )
    , 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.
                        
Asked by PhilHibbs (539 rep)
May 31, 2017, 12:02 PM
Last activity: Apr 9, 2018, 05:03 AM
Can a plpgsql stored procedure monitor for a query and kill it?

Related Questions
