Slide 190
Slide 190 text
CREATE OR REPLACE PROCEDURE
admin.kill_long_analytical_queries()
LANGUAGE plpgsql AS
$proc$
DECLARE
problematic_query RECORD;
BEGIN
FOR problematic_query IN (SELECT pid, query
FROM (SELECT now() - query_start AS run_time,
query,
pid
FROM pg_stat_activity
WHERE usename IN (...) sub
WHERE run_time >= INTERVAL '20 MINUTES')
LOOP
RAISE WARNING 'Killing pid % that was running %', problematic_query.pid, problematic_query.query;
PERFORM pg_terminate_backend(problematic_query.pid);
END LOOP;
END;
$proc$;
SELECT cron.schedule('*/5 * * * *', $$CALL
admin.kill_long_analytical_queries()$$);