SQL to display information about Resource queues in Greenplum

 -- Resource Queues in your database

select pg_resqueue.oid, rsqname from pg_catalog.pg_resqueue;

 -- Resource Queue Parameter Settings

select * from pg_catalog.pg_resqueue_attributes;

-- Viewing the Roles Assigned to a Resource Queue

SELECT rolname, rsqname FROM pg_roles, gp_toolkit.gp_resqueue_status

WHERE pg_roles.rolresqueue=gp_toolkit.gp_resqueue_status.queueid

order by rsqname;

 -- Viewing the Waiting Queries for a Resource Queue

SELECT * FROM gp_toolkit.gp_locks_on_resqueue WHERE lorwaiting='true';

 -- Clearing a Waiting Statement From a Resource Queue

SELECT rolname, rsqname, pid, granted, current_query, datname FROM pg_roles, gp_toolkit.gp_resqueue_status, pg_locks, pg_stat_activity

WHERE pg_roles.rolresqueue=pg_locks.objid

AND pg_locks.objid=gp_toolkit.gp_resqueue_status.queueid

AND pg_stat_activity.procpid=pg_locks.pid;

\qecho -- execute pg_cancel_backend(pid) to clear the statement from RQ.

\qecho

\qecho -- Resetting the Priority of an Active Statement Superusers can adjust the priority of a statement currently being executed using the

\qecho -- built-in function gp_adjust_priority(session_id, statement_count, priority). Using this function, superusers can raise or lower

\qecho -- the priority of any\qecho -- query. For example:=# SELECT gp_adjust_priority(752, 24905, 'HIGH')

\qecho -- To obtain the session ID and statement count parameters required by this function, Superusers can use the gp_toolkit administrative schema view,

\qecho -- gp_resq_priority_statement. This function affects only the specified statement . Subsequent statements in the same resource queue

\qecho -- are executed using the queue’s normally assigned priority.

 -- Resource Queue Current Activities

SELECT q.oid AS queueid, q.rsqname AS queuename, pg_stat_get_queue_num_exec(q.oid) AS n_queries_exec,

pg_stat_get_queue_num_wait(q.oid) AS n_queries_wait, pg_stat_get_queue_elapsed_exec(q.oid) AS elapsed_exec,

pg_stat_get_queue_elapsed_wait(q.oid) AS elapsed_wait FROM pg_resqueue q;

-- Resource Queue waiters and holders

SELECT

q.oid AS queueid,

q.rsqname, t1.value::integer AS rsqcountlimit,

t2.value::integer AS rsqcountvalue,

t3.value::real AS rsqcostlimit,

t4.value::real AS rsqcostvalue,

t5.value::real AS rsqmemorylimit,

t6.value::real AS rsqmemoryvalue,

t7.value::integer AS rsqwaiters,

t8.value::integer AS rsqholders

FROM pg_resqueue q,

pg_resqueue_status_kv() t1(queueid oid, key text, value text),

pg_resqueue_status_kv() t2(queueid oid, key text, value text),

pg_resqueue_status_kv() t3(queueid oid, key text, value text),

pg_resqueue_status_kv() t4(queueid oid, key text, value text),

pg_resqueue_status_kv() t5(queueid oid, key text, value text),

pg_resqueue_status_kv() t6(queueid oid, key text, value text),

pg_resqueue_status_kv() t7(queueid oid, key text, value text),

pg_resqueue_status_kv() t8(queueid oid, key text, value text)

WHERE q.oid = t1.queueid

AND t1.queueid = t2.queueid

AND t2.queueid = t3.queueid

AND t3.queueid = t4.queueid

AND t4.queueid = t5.queueid

AND t5.queueid = t6.queueid

AND t6.queueid = t7.queueid

AND t7.queueid = t8.queueid

AND t1.key = 'rsqcountlimit'::text

AND t2.key = 'rsqcountvalue'::text

AND t3.key = 'rsqcostlimit'::text

AND t4.key = 'rsqcostvalue'::text

AND t5.key = 'rsqmemorylimit'::text

AND t6.key = 'rsqmemoryvalue'::text

AND t7.key = 'rsqwaiters'::text

AND t8.key = 'rsqholders'::text;