Pivotal Knowledge Base

Follow

Status and Activity for Resource Queue in HAWQ v1.3.1

Environment

 Product  Version
 Pivotal HDB  1.3.1.0
 OS  All Supported OS

Purpose

You want to get the status and activity for resource queue in HAWQ v1.3.1 but an error occurs stating that HAWQ v1.3.1 hawq_toolkit.hawq_resqueue_status doesn't exist.

NOTE: If you are using HAWQ 2.x please see How to Install the Hawq_toolkit Views.

Description

Currently, the function to get the status and activity for resource queue is NOT formally implemented/supported with any HAWQ versions at the moment. 

However, there's a workaround that might help you get the required information.

Workaround

Reproduce the query as shown below 

### Below resqueue_status.sql is a partial extract from /usr/local/hawq/share/postgresql/gp_toolkit.sql after uncommenting the lines.

$ cat resqueue_status.sql
--------------------------------------------------------------------------------
-- @view:
--              hawq_toolkit.hawq_pg_resqueue_status
--
-- @doc:
--              New version of pg_catalog.pg_resqueue_status that shows memory limits
--
--------------------------------------------------------------------------------

 CREATE VIEW hawq_toolkit.hawq_resqueue_status
 AS
    SELECT
        q.oid as queueid,
        q.rsqname as rsqname,
        t1.value::int as rsqcountlimit,
        t2.value::int as rsqcountvalue,
        t3.value::real as rsqcostlimit,
        t4.value::real as rsqcostvalue,
        t5.value::real as rsqmemorylimit,
        t6.value::real as rsqmemoryvalue,
        t7.value::int as rsqwaiters,
        t8.value::int 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'
        AND t2.key = 'rsqcountvalue'
        AND t3.key = 'rsqcostlimit'
        AND t4.key = 'rsqcostvalue'
        AND t5.key = 'rsqmemorylimit'
        AND t6.key = 'rsqmemoryvalue'
        AND t7.key = 'rsqwaiters'
        AND t8.key = 'rsqholders'
    ;

GRANT SELECT ON hawq_toolkit.hawq_resqueue_status TO public;

$ psql -f resqueue_status.sql
CREATE VIEW
GRANT

$ psql
gpadmin=# select * from hawq_toolkit.hawq_resqueue_status ;                                                                                            queueid |  rsqname   | rsqcountlimit | rsqcountvalue | rsqcostlimit | rsqcostvalue | rsqmemorylimit | rsqmemoryvalue | rsqwaiters | rsqholders
---------+------------+---------------+---------------+--------------+--------------+----------------+----------------+------------+------------
   16567 | myqueue    |            20 |             0 |           -1 |            0 |    2.09715e+09 |              0 |          0 |          0
    6055 | pg_default |            20 |             0 |           -1 |            0 |             -1 |              0 |          0 |          0
(2 rows)

Comments

Powered by Zendesk