Resource Queues showing higher Active Statement counts than pg_stat_activity listing

Products

• VMware Tanzu Greenplum

Issue/Introduction

When checking gp_resqueue_status, a resource queue is showing a higher active statement count value (slots taken) than showing in pg_stat_activity.

> select * from gp_toolkit.gp_resqueue_status where rsqname ilike 'res_q01';
 queueid   | rsqname       | rsqcountlimit | rsqcountvalue | rsqcostlimit | rsqcostvalue  | rsqmemorylimit | rsqmemoryvalue | rsqwaiters | rsqholders
-----------+---------------+---------------+---------------+--------------+---------------+----------------+----------------+------------+------------
102631158  | res_q01       |    15         |    6          |        -1    | 4.6832394e+09 | -1             |  5.3687091e+09 |          0 | 6      
(1 row) 
Time: 54.146 ms

> select a.rsqname,
         a.rsqcountlimit as countlimit, 
         a.rsqcountvalue as countvalue, 
         a.rsqwaiters as waiters, 
         a.rsqholders as running,
         a.rsqcostlimit as costlimit,
         a.rsqcostvalue as costvalue, 
         b.rsqignorecostlimit as ignorecostlimit, 
         b.rsqovercommit as overcommit from gp_toolkit.gp_resqueue_status a, pg_resqueue b 
  where a.rsqname = b.rsqname and a.rsqname = 'res_q01' order by 1;
 rsqname | countlimit | countvalue | waiters | running | costlimit |     costvalue | ignorecostlimit | overcommit
 --------+------------+------------+---------+---------+-----------+---------------+-----------------+------------
 res_q01 |         15 |          5 |       0 |       5 |        -1 | 4.6832394e+09 |               0 |        f 
(1 row) 
Time: 97.245 ms 

> select now()-query_start, datname,pid,sess_id,usename,application_name,state,waiting,substring(query,0,60) qry 
 from pg_stat_activity 
 where usename in ('user01','user02','user03') order by 1 desc;
 ?column? | datname | pid | sess_id | usename | application_name | state | waiting | qry 
 ----------+---------+-----+---------+---------+------------------+-------+---------+----- 
 (0 rows) 
 Time: 5.040 ms

Environment

Product Version: 6.23

Resolution

This caused by a known bug where resource queue slots were not freed up by statements using "cursor with hold"
Fixed in Greenplum 6.25 and above.