Q:

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

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.

A:

[Originally, the question was tagged with PostgreSQL.  I have no idea about Greenplum, but hopefully someone will turn up to answer for that system, too.  The rest applies to PostgreSQL only (8.3 onwards, whereas Greenplum was forked from 8.2).]
There is a relevant snippet in the documentation:

Another important point is that when a server process is asked to display any of these statistics, it first fetches the most recent report emitted by the collector process and then continues to use this snapshot for all statistical views and functions until the end of its current transaction.

That means that the output is frozen for the transaction that once queries pg_stat_activity.  Fortunately, a few sentences later, this is being said:

Alternatively, you can invoke pg_stat_clear_snapshot(), which will discard the current transaction's statistics snapshot (if any). The next use of statistical information will cause a new snapshot to be fetched.

I've tried it in the below minimalistic example, while issuing different statements in an other session:
DO $$ 
DECLARE i text; 
BEGIN 
    LOOP 
        PERFORM pg_stat_clear_snapshot(); -- makes the output up-to-date
        PERFORM pg_sleep(1);  

        FOR i IN SELECT query FROM pg_stat_activity 
        LOOP 
            RAISE WARNING '%', i; 
        END LOOP; 
    END LOOP; 
END;$$;

It finds newly issued queries and even new sessions, so I think this is what you need.

