ALTER RESOURCE QUEUE
Changes the limits of a resource queue.
Synopsis
ALTER RESOURCE QUEUE <name> WITH ( <queue_attribute>=<value> [, ... ] )where queue_attribute is:
ACTIVE_STATEMENTS=<integer>
MEMORY_LIMIT='<memory_units>'
MAX_COST=<float>
COST_OVERCOMMIT={TRUE|FALSE}
MIN_COST=<float>
PRIORITY={MIN|LOW|MEDIUM|HIGH|MAX}ALTER RESOURCE QUEUE <name> WITHOUT ( <queue_attribute> [, ... ] )where queue_attribute is:
ACTIVE_STATEMENTS
MEMORY_LIMIT
MAX_COST
COST_OVERCOMMIT
MIN_COSTA resource queue must have either an ACTIVE_STATEMENTS or a MAX_COST value (or it can have both).
Do not remove both attributes from a resource queue.
Description
ALTER RESOURCE QUEUE changes the limits of a resource queue.
Only a superuser can alter a resource queue.
You can also set or reset priority for a resource queue to control the relative share of available CPU resources used by queries associated with the queue, or memory limit of a resource queue to control the amount of memory that all queries submitted through the queue can consume on a segment host.
ALTER RESOURCE QUEUE WITHOUT removes the specified limits on a resource that were previously set.
Parameters
| Parameter | Description |
|---|---|
name |
The name of the resource queue whose limits are to be altered |
ACTIVE_STATEMENTS <integer> |
The number of active statements submitted from users in this resource queue allowed on the system at any one time.
The value for |
MEMORY_LIMIT '<memory_units>' |
Sets the total memory quota for all statements submitted from users in this resource queue.
Memory units can be specified in |
MAX_COST <float> |
The total query optimizer cost of statements submitted from users in this resource queue allowed on the system at any one time.
The value for |
COST_OVERCOMMIT <boolean> |
If a resource queue is limited based on query cost, then the administrator can allow cost overcommit ( |
MIN_COST <float> |
Queries with a cost under this limit will not be queued and run immediately.
Cost is measured in units of disk page fetches; |
PRIORITY={MIN|LOW|MEDIUM|HIGH|MAX} |
Sets the priority of queries associated with a resource queue. Queries or statements in queues with higher priority levels will receive a larger share of available CPU resources in case of contention. Queries in low-priority queues may be delayed while higher priority queries are run |
Notes
GPORCA and the Postgres planner utilize different query costing models and may compute different costs for the same query. The Greengage DB resource queue resource management scheme neither differentiates nor aligns costs between GPORCA and the Postgres planner; it uses the literal cost value returned from the optimizer to throttle queries.
When resource queue-based resource management is active, use the MEMORY_LIMIT and ACTIVE_STATEMENTS limits for resource queues rather than configuring cost-based limits.
Even when using GPORCA, Greengage DB may fall back to using the Postgres planner for certain queries, so using cost-based limits can lead to unexpected results.
Examples
Change the active query limit for a resource queue:
ALTER RESOURCE QUEUE myqueue WITH (ACTIVE_STATEMENTS = 20);Change the memory limit for a resource queue:
ALTER RESOURCE QUEUE myqueue WITH (MEMORY_LIMIT = '2GB');Reset the maximum and minimum query cost limit for a resource queue to no limit:
ALTER RESOURCE QUEUE myqueue WITH (MAX_COST = -1.0, MIN_COST = -1.0);Reset the query cost limit for a resource queue to 3e+10 (or 30000000000.0) and do not allow overcommit:
ALTER RESOURCE QUEUE myqueue WITH (MAX_COST = 3e+10, COST_OVERCOMMIT = FALSE);Reset the priority of queries associated with a resource queue to the minimum level:
ALTER RESOURCE QUEUE myqueue WITH (PRIORITY = MIN);Remove the MAX_COST and MEMORY_LIMIT limits from a resource queue:
ALTER RESOURCE QUEUE myqueue WITHOUT (MAX_COST, MEMORY_LIMIT);Compatibility
The ALTER RESOURCE QUEUE statement is a Greengage DB extension.
This command does not exist in standard PostgreSQL.