Queries are not entering resource queue and are in wait state in Pivotal Greenplum

Products

• VMware Tanzu Greenplum

Issue/Introduction

Customer may have a RQ queue with an active statements of 5. There are 2 IDLE sessions, the rest are in wait state and note entering. 

(gpadmin@[local])[admin]> select p.query_start,p.datname,p.procpid,p.sess_id,p.usename,rq.rsqname,substr(p.current_query,1,50),p.waiting,current_timestamp-p.query_start from pg_stat_activity p inner join pg_roles r on r.rolname =p.usename inner join pg_resqueue rq on rq.oid = r.rolresqueue and rq.rsqname='rq1' order by waiting;
query_start | datname | procpid | sess_id | usename | rsqname | substr | waiting | ?column?
-------------------------------+---------+---------+---------+---------+------------------+----------------------------------------------------+---------+-----------------
2019-03-04 09:58:09.682187-05 | gpadmin | 72857 | 5867939 | gpuser | rq1 | <IDLE> in transaction | f | 02:52:35.54226
2019-03-04 09:58:25.604617-05 | gpadmin | 72646 | 5867934 | gpuser | rq1 | <IDLE> in transaction | f | 02:52:19.61983
2019-03-04 09:58:24.991907-05 | gpadmin | 72645 | 5867933 | gpuser | rq1 | SELECT * FROM set_config('application_name', $1, F | t | 02:52:20.23254
2019-03-04 09:58:24.675358-05 | gpadmin | 72853 | 5867938 | gpuser | rq1 | SELECT * FROM set_config('application_name', $1, F | t | 02:52:20.549089
2019-03-04 09:58:25.163259-05 | gpadmin | 216044 | 5870436 | gpuser | rq1 | select outer_as.target_table, outer_as.target_colu | t | 02:52:20.061188
2019-03-04 09:58:33.731977-05 | gpadmin | 220457 | 5870510 | gpuser | rq1 | SELECT * FROM set_config('application_name', $1, F | t | 02:52:11.49247
2019-03-04 09:58:25.547321-05 | gpadmin | 72848 | 5867937 | gpuser | rq1 | SELECT * from temp_dimension_result where delivery | t | 02:52:19.677126
2019-03-04 09:58:20.075098-05 | gpadmin | 43277 | 5867125 | gpuser | rq1 | SELECT * FROM set_config('application_name', $1, F | t | 02:52:25.149349
(8 rows)


(gpadmin@[local])[admin]> select rsqname, active_statements, priority from msgp.rsq_def_wide where rsqname ='rq1';
rsqname | active_statements | priority
------------------+-------------------+----------
rq1 | 5 | low
(1 row)

Environment

Product Version: 4.3

Resolution

This is a known issue in GPDB and it is fixed in GPDB 5.x. The resource queue counter is not properly handled in some cases. We were able to get the RQ processing again by terminating the idle query. However, the counter is tracked in shared memory, so restarting the database is the accepted workaround to get them reset and back to normal.

Resource groups in GPDB 5.x do not have this issue, and the suggestion from engineering is to upgrade to 5.x and use resource groups.