We have migrated to new Google sites!
-- 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;