When Altering a Resource Queue, 'COST_OVERCOMMIT=FALSE', it Fails with Error "Disabling Overcommit cannot Leave Queue in Possibly Overcommitted State"

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

When altering a resource queue 'COST_OVERCOMMIT=FALSE', the command fails with the following error:

ERROR : 'disabling overcommit cannot leave queue in possibly overcommitted state'

 

Environment

Cause

When a resource queue has ''COST_OVERCOMMIT=TRUE', the resource queue can run queries even though the total cost of queries in the queue exceeds the max_cost limit of the queue. During this time, if we try to alter the resource queue in another session by setting the 'COST_OVERCOMMIT=FALSE', it will fail with the above error. This behavior within Greenplum is by design and is used to protect the resource queue from being left in an overcommitted state.

 

Resolution

Wait for the resource queue to be free or running low load. Query the gp_toolkit.gp_resqueue_status view to check if 'rsqcountvalue' is less than 'rsqcostlimit'. If yes, you can try to run the alter command to set 'COST_OVERCOMMIT=FALSE'.

Example:

The 'test' resource queue has a query running which is taking more than the max_cost of 1000. [rsqcostvalue=702437]:

gpadmin=# select * from gp_toolkit.gp_resqueue_status ;

queueid | rsqname | rsqcountlimit | rsqcountvalue | rsqcostlimit | rsqcostvalue | rsqmemorylimit | rsqmemoryvalue | rsqwaiters | rsqholders
---------+------------+---------------+---------------+--------------+--------------+----------------+
91024 	| test 	  | 10 			  | 	1 		  | 	1000 	 | 702437 		| 		-1 		 | 1.31072e+08

In another window/session, if we try to alter the resource queue by setting 'COST_OVERCOMMIT=FALSE', it will fail:

gpadmin=# alter resource queue test with (COST_OVERCOMMIT=false);
ERROR: disabling overcommit cannot leave queue in possibly overcommitted state

After the query finishes, try changing the value:

gpadmin=# select * from gp_toolkit.gp_resqueue_status ;

queueid | rsqname | rsqcountlimit | rsqcountvalue | rsqcostlimit | rsqcostvalue | rsqmemorylimit 
---------+------------+---------------+---------------+--------------+--------------+----------------+
91024 | test | 10 | 0 | 1000 | 0 | -1 | 

gpadmin=# alter resource queue test with (COST_OVERCOMMIT=false);
ALTER QUEUE