How to assign lowest priority to any long-running query

Post date: Oct 17, 2013 2:47:11 PM

Assign lowest priority to any long-running query (any query running longer than say 30 minutes)

select psa.sess_id as session_id,

    rpb.rqpcommand as command_id,

    current_timestamp-psa.query_start as query_runtime

from gp_resq_priority_backend rpb

join pg_stat_activity psa ON rpb.rqpsession = psa.sess_id

where extract(epoch from current_timestamp-psa.query_start)/60 > 30

order by 1 desc;

then use gp_adjust_priority() command to change the priority.

Or even in one query:

select gp_adjust_priority(psa.sess_id, rpb.rqpcommand, 'LOW')

from gp_resq_priority_backend rpb

join pg_stat_activity psa ON rpb.rqpsession = psa.sess_id

where extract(epoch from current_timestamp-psa.query_start)/60 > 30

order by 1 desc;