Resource Queues: How the Legacy Manager Works

Most Greenplum clusters still run resource queues. They are the compiled default, they work, and the code itself explains why they are called legacy.

§21.1 described a resource manager that is fully inspectable on this cluster and not enforcing anything. This section describes the other one, and it is enforcing: gp_resource_manager is queue, which is also the compiled-in default (guc_gp.c:4939), so every statement run by a non-superuser role here has passed through the machinery below. One design decision explains almost all of its behaviour. A resource queue is not a scheduler with its own wait loop; it is a row in the ordinary lock table. A statement that wants to run takes a lock on its queue, and if the queue is full it waits exactly the way a transaction waits for a row lock — visible in pg_locks, with a wait event, under the deadlock detector, honouring lock_timeout. Start there, because once a slot is understood as a lock, the rest of the subsystem stops being surprising.

21.2.1 A slot is a lock

§11.1 introduced the heavyweight lock manager: a partitioned shared hash table keyed by LOCKTAG, a conflict matrix per lock method, a waitProcs queue per lock object. Resource queues do not build a parallel copy of that. They register a third lock method alongside the default one and the pg_advisory_* one, and reuse the same hash tables, the same partition locks, and the same PGPROC wait links. There is no separate scheduler to learn.

src/backend/storage/lmgr/lock.c:169

const LockMethodData resource_lockmethod = {
	MaxLockMode,        /* highest valid lock mode number */
	LockConflicts,
	lock_mode_names,
	...
};

const LockMethod LockMethods[] = {
	NULL, &default_lockmethod, &user_lockmethod, &resource_lockmethod
};

Here is that in action. The queue m212_q has ACTIVE_STATEMENTS = 1; its only member is the non-superuser role m212_u. Session A takes the slot and sits in pg_sleep. Session B then submits a count(*) that takes 230 ms when the queue is free. An observer connected as gpadmin watches both — and, being a superuser, is exempt from the queue it is inspecting.

The whole mechanism in one snapshot: two toolkit views, pg_stat_activity and pg_locks all describing the same wait. Real capture; timings are ratios on one cassert build, never benchmarks.

-- session A (as m212_u)
SELECT pg_sleep(10) FROM m212_t LIMIT 1;
-- session B (as m212_u), two seconds later
SELECT count(*) FROM m212_t;

-- observer (as gpadmin, superuser, therefore exempt)
SELECT rsqname, rsqcountlimit, rsqcountvalue, rsqwaiters, rsqholders
  FROM gp_toolkit.gp_resqueue_status WHERE rsqname = 'm212_q';
SELECT lorusename, lorlocktype, lormode, lorgranted, lorwaiteventtype, lorwaitevent
  FROM gp_toolkit.gp_locks_on_resqueue WHERE lorrsqname = 'm212_q' ORDER BY lorgranted DESC;
SELECT pid, sess_id, usename, wait_event_type, wait_event, substring(query,1,32) AS query
  FROM pg_stat_activity WHERE usename = 'm212_u' ORDER BY pid;
SELECT locktype, objid, mode, granted, pid
  FROM pg_locks WHERE locktype = 'resource queue' ORDER BY granted DESC;

-- A:  Time: 10117.610 ms (00:10.118)
-- B:  Time: 8344.075 ms (00:08.344)      <-- 230 ms of work, 8.1 s of queueing

 rsqname | rsqcountlimit | rsqcountvalue | rsqwaiters | rsqholders
---------+---------------+---------------+------------+------------
 m212_q  |             1 |             1 |          1 |          1

 lorusename |  lorlocktype   |    lormode    | lorgranted | lorwaiteventtype | lorwaitevent
------------+----------------+---------------+------------+------------------+---------------
 m212_u     | resource queue | ExclusiveLock | t          | IPC              | Interconnect
 m212_u     | resource queue | ExclusiveLock | f          | ResourceQueue    | ResourceQueue

  pid  | sess_id | usename | wait_event_type |  wait_event   |              query
-------+---------+---------+-----------------+---------------+----------------------------------
 17850 |   17312 | m212_u  | IPC             | Interconnect  | SELECT pg_sleep(10) FROM m212_t
 17878 |   17313 | m212_u  | ResourceQueue   | ResourceQueue | SELECT count(*) FROM m212_t;

    locktype    | objid |     mode      | granted |  pid
----------------+-------+---------------+---------+-------
 resource queue | 50940 | ExclusiveLock | t       | 17850
 resource queue | 50940 | ExclusiveLock | f       | 17878

Read the objid column: 50940 is literally m212_q’s OID in pg_resqueue, which is how gp_locks_on_resqueue manages to be nothing but a three-way join of pg_stat_activity, pg_locks and pg_resqueue on pgl.objid = pgrq.oid. Note also that the granted row’s wait event is IPC / Interconnect — session A is not waiting on the queue at all, it is waiting on its own gang (§19.2). The queue view shows holders, not sleepers.

Two experiments confirm that this really is the lock manager and not a lookalike. First, lock_timeout — a core PostgreSQL GUC that knows nothing about resource queues — aborts a queued statement, because ResProcSleep arms DEADLOCK_TIMEOUT and LOCK_TIMEOUT through the same enable_timeouts path as ProcSleep (proc.c:2158). Second, a session can deadlock against itself by holding a slot in one portal and asking for another, which open cursors make easy: ResCheckSelfDeadLock (resqueue.c:1565) sums this backend’s own increments across all its portals and refuses before sleeping. The one place the lock manager did have to be told about queues is the soft deadlock path: when CheckDeadLock rearranges a wait queue to break a cycle it normally re-runs ProcLockWakeup, but for a resource queue that would be pointless — nobody can proceed until a holder actually releases resources — so the call is skipped by an explicit tag test (deadlock.c:273).

lock_timeout aborts a queued statement; a cursor plus a second query self-deadlocks. Both errors come from the standard lock machinery.

-- while session A holds the only slot, as m212_u:
SET lock_timeout = '2s';
SELECT count(*) FROM m212_t;

-- and, in a single session, with ACTIVE_STATEMENTS = 1:
BEGIN;
DECLARE m212_c CURSOR FOR SELECT * FROM m212_t ORDER BY id;
FETCH 2 FROM m212_c;
SELECT count(*) FROM m212_t;
ROLLBACK;

SET
ERROR:  canceling statement due to lock timeout
Time: 2095.191 ms (00:02.095)

BEGIN
DECLARE CURSOR
 id | v
----+----
  1 | x1
  2 | x2
(2 rows)

ERROR:  deadlock detected, locking against self
DETAIL:  resource queue id: 50940, portal id: 0
ROLLBACK

21.2.2 Six knobs, two tables

A queue is a name plus six settings, and they are stored in two different shapes for historical reasons. pg_resqueue carries four of them as ordinary columns; the other two live as key/value rows in pg_resqueuecapability, keyed by a small integer from pg_resourcetype. The view pg_resqueue_attributes UNIONs the two shapes back together, which is the only sane way to read a queue.

The catalog trio, and the one queue Cloudberry ships. Note that pg_resqueuecapability holds only types 5 and 6.

SELECT oid, rsqname, rsqcountlimit, rsqcostlimit, rsqovercommit, rsqignorecostlimit
  FROM pg_resqueue ORDER BY oid;
SELECT restypid, resname, reshasdefault, reshasdisable, resdefaultsetting, resdisabledsetting
  FROM pg_resourcetype ORDER BY restypid;
SELECT * FROM pg_resqueuecapability ORDER BY resqueueid, restypid;

  oid  |  rsqname   | rsqcountlimit | rsqcostlimit | rsqovercommit | rsqignorecostlimit
-------+------------+---------------+--------------+---------------+--------------------
  6055 | pg_default |          20 |           -1 | f             |                  0
 50940 | m212_q     |             1 |           -1 | f             |                  0

 restypid |      resname      | reshasdefault | reshasdisable | resdefaultsetting | resdisabledsetting
----------+-------------------+---------------+---------------+-------------------+--------------------
        1 | active_statements | t             | t             | -1                | -1
        2 | max_cost          | t             | t             | -1                | -1
        3 | min_cost          | t             | t             | -1                | 0
        4 | cost_overcommit   | t             | t             | -1                | -1
        5 | priority          | t             | f             | medium            |
        6 | memory_limit      | t             | t             | -1                | -1

 resqueueid | restypid | ressetting
------------+----------+------------
       6055 |        5 | medium
       6055 |        6 | -1
      50940 |        5 | medium
      50940 |        6 | -1

DDL is CREATE/ALTER/DROP RESOURCE QUEUE in queue.c (:705, :1002, :1422), with role assignment through CREATE ROLE … RESOURCE QUEUE q and ALTER ROLE … RESOURCE QUEUE q writing pg_authid.rolresqueue. Validation is thorough about individual values and indifferent to their relationship to each other: a queue whose MIN_COST sits above its MAX_COST — in which every plan either falls below the floor and runs unmanaged, or exceeds the ceiling and errors, so that nothing can ever be admitted normally — is accepted without comment. pg_default cannot be dropped either, but not because it is protected — only because every role in the cluster references it, which is also why every CREATE ROLE here emits NOTICE: resource queue required -- using default resource queue "pg_default". §21.1 showed that notice arriving side by side with the resource-group warning on a single CREATE ROLE — the clearest picture there is of the two managers coexisting.

Queue DDL: what is rejected, what is quietly accepted, and why a queue with roles cannot be dropped.

CREATE RESOURCE QUEUE m212_bad WITH (PRIORITY=HIGH);
CREATE RESOURCE QUEUE m212_bad WITH (ACTIVE_STATEMENTS=0);
CREATE RESOURCE QUEUE m212_bad WITH (ACTIVE_STATEMENTS=1, MAX_COST=-2);
CREATE RESOURCE QUEUE m212_bad WITH (ACTIVE_STATEMENTS=1, PRIORITY=URGENT);
CREATE RESOURCE QUEUE m212_bad WITH (ACTIVE_STATEMENTS=1, FOO=1);
CREATE RESOURCE QUEUE m212_bad WITH (MAX_COST=1000, MIN_COST=2000);
DROP RESOURCE QUEUE m212_q;
DROP RESOURCE QUEUE pg_default;

ERROR:  at least one threshold ("ACTIVE_STATEMENTS", "MAX_COST") must be specified
ERROR:  active threshold cannot be less than -1 or equal to 0
ERROR:  cost threshold cannot be less than -1 or equal to 0
ERROR:  Invalid parameter value "urgent" for resource type "PRIORITY"
ERROR:  option "foo" is not a valid resource type
CREATE QUEUE                      <-- MIN_COST above MAX_COST: accepted
ERROR:  resource queue "m212_q" is used by at least one role
ERROR:  resource queue "pg_default" is used by at least one role

ALTER is where the code has visibly drifted from its own documentation, and the drift is small enough to see all of. ResAlterQueue once refused to lower a threshold below a queue’s current counter value; MPP-4340 removed that check on the grounds that the whole point of a queue is to throttle a system that is already overloaded (resscheduler.c:382-391), leaving exactly one live rejection — you may not turn COST_OVERCOMMIT off while the queue might be in an overcommitted state (:414). But result is declared bool, and ALTERQUEUE_OVERCOMMITTED is the third member of ResAlterQueueResult (resscheduler.h:127), so storing it yields 1, which is ALTERQUEUE_SMALL_THRESHOLD. The caller tests that value first (queue.c:1373) and prints the other message. The only error this function can raise is therefore permanently mislabelled, and it names a check that no longer exists.

src/backend/utils/resscheduler/resscheduler.c:362

	ResQueue		queue = ResQueueHashFind(queueid);
	bool			result = ALTERQUEUE_OK;      /* <-- bool, not the enum */
	...
			if (!overcommit && queue->overcommit &&
				(queue->limits[i].current_value > 0.1))
				result = ALTERQUEUE_OVERCOMMITTED;   /* :414 — enum value 2 */
	...
	return result;

/* live, one statement admitted, COST_OVERCOMMIT=TRUE:
 *   ALTER ... WITH (COST_OVERCOMMIT=FALSE);
 *   ERROR: thresholds cannot be less than current values  */

21.2.3 Where admission happens

Part III allocated every gang and every motion queue “as though the machine were ours alone” (§19.1), and §19.2 closed by naming what was missing: admission. This is where it happens, and the exact position matters more than the mechanism. ResLockPortal is called from PortalStart (pquery.c:226) — so a slot hangs off the portal of §13.1, not off the transaction, and it is taken after parse, analyse, rewrite and planning but before ExecutorStart. (Resource groups differ here: §21.1 showed their slot being taken by StartTransaction, well before a plan exists — which is why cost gating has to be a second, post-planning decision for them and can be the only decision for queues.) The subsystem’s README states the reason plainly: the call “is made after planning is completed, so there is access to all elements of the plan structure, thus controlling resources on the basis of cost is possible.” Cost gating and memory sizing both need a finished PlannedStmt; nothing after that point does.

src/backend/tcop/pquery.c:211

	check_and_unassign_from_resgroup(queryDesc->plannedstmt);
	queryDesc->plannedstmt->query_mem = ResourceManagerGetQueryMemoryLimit(queryDesc->plannedstmt);
	if (Gp_role == GP_ROLE_DISPATCH || IS_SINGLENODE())
	{
		if (IsResQueueEnabled() && !superuser() && !IsResQueueLockedForPortal(portal))
		{
			if ((!ResourceSelectOnly || portal->sourceTag == T_SelectStmt) && stmt->canSetTag)
				ResLockPortal(portal, queryDesc);
			else
				queryDesc->plannedstmt->query_mem = 0;   /* not tracked */
		}
	}

Four things are decided by those lines. query_mem is computed first and unconditionally, so it exists even for statements that will not be gated. Both managers are consulted through one function, so the fork between §21.1 and this section is a single if at memquota.c:906. Superusers never enter the queue. And admission precedes PORTAL_ACTIVE, which precedes ExecutorStart, which precedes gang assignment — so a queued statement has no query executor processes anywhere in the cluster. ResourceCleanupIdleGangs reinforces the same intent from the other side: a backend about to sleep on a queue lock first destroys gangs left over from earlier statements in its session (proc.c:2151), so waiting for admission does not tie up segment processes. The claim about QEs is directly checkable.

Admission is upstream of gangs: the waiting session owns no QE on any segment. Segment access is via utility mode, as elsewhere in the book.

-- coordinator, while A runs and B waits on the queue
SELECT sess_id, pid, wait_event_type, substring(query,1,30) q
  FROM pg_stat_activity WHERE usename = 'm212_u' ORDER BY sess_id;

-- seg0:  PGOPTIONS='-c gp_role=utility' psql -p 7102 -d postgres
SELECT sess_id, count(*) AS qe_backends
  FROM pg_stat_activity WHERE usename = 'm212_u' GROUP BY sess_id ORDER BY sess_id;

 sess_id |  pid  | wait_event_type |               q
---------+-------+-----------------+--------------------------------
   17423 | 29973 | IPC             | SELECT pg_sleep(12) FROM m212_
   17425 | 30014 | ResourceQueue   | SELECT count(*) FROM m212_t;

 sess_id | qe_backends
---------+-------------
   17423 |           1
(1 row)          <-- session 17425 has no QE on seg0 at all

Which statements are gated at all is decided by a switch on portal->sourceTag inside ResLockPortal. SELECT and DECLARE CURSOR are gated; INSERT, UPDATE and DELETE are gated unless resource_select_only is on (it is off here); and everything else falls to default: takeLock = false. That is a very wide door. COPY and CREATE TABLE AS were later given their own narrow path — ResHandleUtilityStmt (resscheduler.c:1127) tests for exactly those two node types and routes them to ResLockUtilityPortal (:777) — but every other utility statement, and EXPLAIN without ANALYZE, runs unmanaged. A second consequence of the position is easy to miss and hard to debug: planning has already taken the relation locks, so a statement waiting for a slot is a statement holding AccessShareLock on every table it is about to read — and therefore blocking any ALTER TABLE, TRUNCATE or VACUUM FULL on them for as long as it waits. Both effects are visible below.

With the only slot held by another session: SET, SHOW and EXPLAIN sail through; the SELECT queues and is killed by a guard timeout.

-- as m212_u, while session A holds the only slot
SET statement_timeout = '2500ms';
SHOW statement_mem;
EXPLAIN SELECT count(*) FROM m212_t;
SELECT count(*) FROM m212_t;

SET
Time: 12.978 ms

 statement_mem
---------------
 125MB
Time: 0.616 ms

                                     QUERY PLAN
------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=0.00..449.11 rows=1 width=8)
   ->  Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..449.11 rows=1 width=8)
         ->  Partial Aggregate  (cost=0.00..449.11 rows=1 width=8)
               ->  Seq Scan on m212_t  (cost=0.00..447.87 rows=666667 width=1)
 Optimizer: GPORCA
Time: 48.536 ms

ERROR:  canceling statement due to statement timeout
Time: 2500.481 ms (00:02.500)

The waiter is not idle: it holds a relation lock while it sleeps on the queue lock, exactly as the subsystem README warns. A long queue therefore delays DDL.

SELECT l.pid, a.wait_event_type, l.locktype,
       coalesce(c.relname, l.objid::text) AS obj, l.mode, l.granted
  FROM pg_locks l JOIN pg_stat_activity a ON a.pid = l.pid
  LEFT JOIN pg_class c ON c.oid = l.relation
 WHERE a.usename = 'm212_u'
   AND (c.relname = 'm212_t' OR l.locktype = 'resource queue')
 ORDER BY l.pid, l.locktype;

  pid  | wait_event_type |    locktype    |  obj   |      mode       | granted
-------+-----------------+----------------+--------+-----------------+---------
 28297 | IPC             | relation       | m212_t | AccessShareLock | t
 28297 | IPC             | resource queue | 50940  | ExclusiveLock   | t
 28321 | ResourceQueue   | relation       | m212_t | AccessShareLock | t
 28321 | ResourceQueue   | resource queue | 50940  | ExclusiveLock   | f

21.2.4 Cost gating

ACTIVE_STATEMENTS counts statements; MAX_COST weighs them. Both are entries in the same array of three limits — RES_COUNT_LIMIT, RES_COST_LIMIT, RES_MEMORY_LIMIT — each with a threshold and a live counter, and ResLockCheckLimit walks all three on every acquisition (resqueue.c:875), skipping any whose threshold is the -1 sentinel. Cost has one behaviour the other two do not. A statement whose own increment exceeds the threshold can never be admitted by waiting, because no amount of other people finishing will help. The check distinguishes that case as will_overcommit and, when COST_OVERCOMMIT is off, returns LIMIT_CHECK_ERROR instead of LIMIT_CHECK_FOUND (resqueue.c:999) — an immediate error rather than a wait that would never end, raised at :504.

MAX_COST as a hard gate, then as a soft one. The count(*) plan costs 449.11 and the queue ceiling is 100.

ALTER RESOURCE QUEUE m212_q WITH (ACTIVE_STATEMENTS=-1, MAX_COST=100.0, COST_OVERCOMMIT=FALSE);
SELECT count(*) FROM m212_t;                 -- as m212_u

ALTER RESOURCE QUEUE m212_q WITH (COST_OVERCOMMIT=TRUE);
SELECT count(*) FROM m212_t;                 -- as m212_u, queue otherwise idle

ALTER QUEUE

ERROR:  statement requires more resources than resource queue allows
DETAIL:  resource queue id: 50940, portal id: 0

ALTER QUEUE

  count
---------
 2000000
Time: 232.634 ms

MIN_COST is the mirror image and the more useful knob in practice. It is checked before any counter is touched (resqueue.c:406): if the plan’s cost increment is below the queue’s ignorecostlimit, ResLockAcquire unwinds everything it has done and returns LOCKACQUIRE_NOT_AVAIL (:433), the portal’s queueId is reset, and the statement runs entirely outside the queue. Choosing a value means reading real plans from a real workload, exactly as §21.1 argued for the resource-group min_cost.

MIN_COST = 500 while ACTIVE_STATEMENTS = 1 and the only slot is held. The 449.11-cost plan does not wait, and never appears in pg_locks.

ALTER RESOURCE QUEUE m212_q WITH (ACTIVE_STATEMENTS=1, MAX_COST=-1, MIN_COST=500.0);

-- session A holds the slot with a plan costing 518.44
-- session B, as m212_u, runs the plan costing 449.11:
SELECT count(*) FROM m212_t;

-- observer, while A still holds:
SELECT rsqname, rsqcountlimit, rsqcountvalue, rsqwaiters, rsqholders
  FROM gp_toolkit.gp_resqueue_status WHERE rsqname = 'm212_q';
SELECT count(*) AS resqueue_locks FROM pg_locks WHERE locktype = 'resource queue';

  count
---------
 2000000
Time: 230.789 ms          <-- not queued at all

 rsqname | rsqcountlimit | rsqcountvalue | rsqwaiters | rsqholders
---------+---------------+---------------+------------+------------
 m212_q  |             1 |             1 |          0 |          1

 resqueue_locks
----------------
              1

21.2.5 From statement_mem to operatorMemKB

This is the loop §18.1 left open. That section documented the EXPLAIN ANALYZE footer — Memory used: / Memory wanted: — and said Chapter 21 owns the granting side. §18.3 and §18.4 then found that work_mem is inert on this cluster and that each operator’s budget arrives pre-computed in Plan.operatorMemKB. Both threads end here, in one three-step chain: the queue’s MEMORY_LIMIT produces the statement’s query_mem, and the memory policy divides query_mem among the plan’s operators. Step one is ResourceQueueGetQueryMemoryLimit (resqueue.c:2409). If MEMORY_LIMIT is -1 it returns statement_mem and stops. Otherwise it divides the queue’s budget by the smaller of two ratios — the statement’s share of the queue’s slots, and its share of the queue’s cost ceiling — then floors the result at statement_mem, so asking for more with SET statement_mem always works.

src/backend/utils/resscheduler/resqueue.c:2473

	double minRatio = Min( 1.0/ (double) numSlots, planCost / costLimit);
	minRatio = Min(minRatio, 1.0);

	uint64 queryMem = (uint64) resqLimitBytes * minRatio;

	/* If user requests more using statement_mem, grant that. */
	if (queryMem < (uint64) statement_mem * 1024L)
		queryMem = (uint64) statement_mem * 1024L;

	return queryMem;

The whole chain can be moved by editing nothing but the queue. Below, one unchanged query runs three times as m212_u; the only thing that differs is m212_q’s definition. The Memory used: line follows the formula exactly — and the first of the three reproduces §18.1’s 128000kB on the nose, because with MEMORY_LIMIT = -1 the function returns statement_mem and nothing else happens. The server will also narrate the arithmetic on request: gp_log_resqueue_memory is PGC_USERSET and its messages come out at NOTICE, so an ordinary session can watch its own grant being computed, then arrive twice — once as ResLockPortal records it as the statement’s RES_MEMORY_LIMIT increment (resscheduler.c:606), once as standard_ExecutorStart hands it to the quota policy (execMain.c:281).

One query, three queue definitions, three grants — then the same grant computed out loud. GPORCA planned all three identically; only the footer moves.

EXPLAIN ANALYZE SELECT v, count(*) FROM m212_t GROUP BY v ORDER BY 2 DESC LIMIT 5;
-- run once per queue setting, as m212_u

SET gp_log_resqueue_memory = on;    -- then the same query, configuration (c)
SELECT v, count(*) FROM m212_t GROUP BY v ORDER BY 2 DESC LIMIT 5;

-- (a) MEMORY_LIMIT = -1,      ACTIVE_STATEMENTS = 1
 * (slice1)  Executor memory: 48127K bytes avg x 3x(0) workers ...  Work_mem: 65681K bytes max, 81937K bytes wanted.
 Memory used:  128000kB          <-- = statement_mem, the §18.1 number
 Memory wanted:  164372kB
 Optimizer: GPORCA
 Execution Time: 1401.792 ms

-- (b) MEMORY_LIMIT = 1500MB,  ACTIVE_STATEMENTS = 1
 Memory used:  1536000kB           <-- 1500 x 1024, minRatio = 1.000
 Memory wanted:  164372kB

-- (c) MEMORY_LIMIT = 1500MB,  ACTIVE_STATEMENTS = 4
 Memory used:  384000kB            <-- 1536000 / 4, minRatio = 0.250
 Memory wanted:  164372kB

NOTICE:  numslots: 4, costlimit: -1.000000
NOTICE:  slotratio: 0.250, costratio: 1.000, minratio: 0.250
NOTICE:  query requested 384000KB              -- resscheduler.c:606, into the queue counter
NOTICE:  query requested 384000KB of memory    -- execMain.c:277, into the policy

Three details before moving on. costlimit: -1.000000 is printed before normalisation — with MAX_COST disabled the function sets costLimit = planCost, which is why costratio comes out as exactly 1.000. The memory counter really is a queue limit like the other two: gp_resqueue_status.rsqmemoryvalue read 1.31072e+08 while one statement was admitted, which is 128000 kB expressed in bytes. And gp_resqueue_memory_policy is PGC_SUSET (guc_gp.c:5337), so a non-superuser may watch the arithmetic but cannot change the policy that drives it. Step three then closes §18.3’s puzzle. PolicyEagerFreeAssignOperatorMemoryKB (memquota.c:847) walks the plan and writes a number into every node’s operatorMemKB, grouping operators that are never simultaneously live so they can share a budget; the simpler auto policy just divides query_mem among the memory-intensive nodes (memquota.c:341). Either way, by the time the executor runs, every node already has its budget, and work_mem is consulted only on the path where that did not happen:

src/backend/executor/execUtils.c:2314

uint64 PlanStateOperatorMemKB(const PlanState *ps)
{
	uint64 result;
	if (ps->plan->operatorMemKB == 0)
	{
		/* There are some statements that do not go through the resource queue
		 * and these plans dont get decorated with the operatorMemKB. Someday,
		 * we should fix resource queues. */
		result = work_mem;
	}
	else
		result = ps->plan->operatorMemKB;

That is the whole answer to “why does work_mem do nothing here”. It is not ignored; it is the fallback for a branch that a resource-managed statement never takes. Because query_mem is computed unconditionally at pquery.c:212 — for superusers too, where it is simply statement_mem — operatorMemKB is essentially always non-zero and work_mem stays dormant. Setting gp_resqueue_memory_policy = none would wake it up, and that is a superuser-only change.

21.2.6 The priority sweeper

ACTIVE_STATEMENTS, MAX_COST and MEMORY_LIMIT all act at admission: once a statement is in, they say nothing more. PRIORITY is the only setting that acts during execution, and it does so by a mechanism unlike anything else in this chapter — no locks, no counters, and none of the kernel involvement §21.1 described. Cloudberry simply makes low-priority backends sleep. A background worker, the sweeper, runs one per segment instance including the coordinator (ps shows postgres: 7100, sweeper process beside one each for 7102, 7103 and 7104). Every gp_resqueue_priority_sweeper_interval milliseconds — 1000 here — it reads each active backend’s CPU consumption from getrusage and writes a targetUsage fraction into that backend’s shared-memory entry, proportional to its weight (BackoffSweeper, backoff.c:696). Each backend then enforces its own target: CHECK_FOR_INTERRUPTS increments a tick counter, and when it expires the backend compares its actual CPU ratio against the target and calls pg_usleep for the difference (backoff.c:606, sleeping at :566).

Weights as the sweeper sees them, from a queue whose PRIORITY is LOW. The superuser observer is at MAX — exempt from priority just as it is exempt from the queue.

SELECT rqpusename, rqpsession, rqppriority, rqpweight, substring(rqpquery,1,26) AS rqpquery
  FROM gp_toolkit.gp_resq_priority_statement ORDER BY rqpsession;

-- then ALTER RESOURCE QUEUE m212_q WITH (PRIORITY=MAX), and three seconds later:
SELECT * FROM gp_list_backend_priorities()
  AS t(session_id int, command_count int, priority text, weight int) ORDER BY 4 DESC, 1;

 rqpusename | rqpsession | rqppriority | rqpweight |          rqpquery
------------+------------+-------------+-----------+----------------------------
 m212_u     |      17383 | LOW         |     200 | SELECT pg_sleep(14) FROM m
 m212_u     |      17384 | LOW         |       200 | SELECT count(*) FROM m212_
 gpadmin    |      17385 | MAX         |   1000000 | SELECT rqpusename, rqpsess

 session_id | command_count | priority | weight
------------+---------------+----------+---------
      17371 |             1 | MAX      | 1000000    <-- the observer
      17367 |             1 | LOW      |     200    <-- still LOW after the ALTER
      17368 |             1 | LOW      |     200
(3 rows)

Two things in that output are worth stating outright. The weight is snapshotted when the statement starts: ResourceQueueGetPriorityWeight (backoff.c:1389) reads the queue’s capability once, so ALTER RESOURCE QUEUE … PRIORITY changes only future statements. To re-weigh something already running there is gp_adjust_priority(session_id, command_count, priority) (backoff.c:1032), which reaches every backend of that session on every segment. And superusers get MAX unconditionally (BackoffSuperuserStatementWeight, backoff.c:1358), out of priority_map (backoff.c:1437), the table whose five names are the only legal PRIORITY values: MAX = 1000000, HIGH = 1000, MEDIUM = 500 (the shipped default, for pg_default and for gp_resqueue_priority_default_value alike), LOW = 200, MIN = 100. The factor-of-a-thousand gap between MAX and HIGH means one MAX statement effectively suppresses backoff everywhere else — and every superuser statement is a MAX statement.

Three postmaster-level GUCs govern the mechanism and none can be changed without a restart: gp_resqueue_priority (on, backing the C variable gp_enable_resqueue_priority at guc_gp.c:1633) is what the sweeper loop tests before doing any work at all (backoff.c:1221); gp_resqueue_priority_sweeper_interval (1000 ms) is how often shares are recomputed; and gp_resqueue_priority_cpucores_per_segment (4) tells the sweeper how much CPU one segment is entitled to, feeding numProcsPerSegment() and hence every target. That last one is a declaration, not a measurement — exactly like gp_resource_manager declaring the cgroup generation in §21.1. Set it wrong and every share is computed against a machine that does not exist. No throughput comparison is offered here: on a four-core —enable-cassert build, sleep-based CPU shaping produces numbers that would say more about this box than about the mechanism.

21.2.7 The observability surface, and the word ‘legacy’

Unlike the resource-group views of §21.1, every view here answers. Three read live shared memory through pg_resqueue_status() — gp_resqueue_status for the counters, gp_resq_activity and gp_resq_activity_by_queue for who is running or waiting — one reads the lock table (gp_locks_on_resqueue), two read the backoff array (gp_resq_priority_statement, gp_resq_priority_backend), and gp_resq_role is a plain catalog join. Alongside them sits something distinctly newer: pg_stat_resqueues is backed by a PG16 cumulative-statistics kind, PGSTAT_KIND_RESQUEUE (pgstat.c:344), with its own flush and reset callbacks in pgstat_resqueue.c and hooks planted at each decision point in ResLockPortal. It is the only part of this subsystem that looks like it was written this decade.

The gp_toolkit family during one queue event, then the cumulative counters across it. rsqcostvalue is the admitted plan’s ceil(total_cost); rsqmemoryvalue is its query_mem in bytes.

SELECT resqprocpid, resqrole, resqname, resqstatus FROM gp_toolkit.gp_resq_activity ORDER BY resqprocpid;
SELECT * FROM gp_toolkit.gp_resqueue_status ORDER BY rsqname;

SELECT queuename, queries_submitted, queries_admitted, queries_rejected, queries_completed,
       total_wait_time_secs, total_cost, total_memory_kb
  FROM pg_stat_resqueues WHERE queuename = 'm212_q';
-- then: one 8-second holder plus two queued count(*) statements, then re-read

 resqprocpid | resqrole | resqname | resqstatus
-------------+----------+----------+------------
       22462 | m212_u   | m212_q   | running
       22480 | m212_u   | m212_q   | waiting

 queueid |  rsqname   | rsqcountlimit | rsqcountvalue | rsqcostlimit | rsqcostvalue | rsqmemorylimit | rsqmemoryvalue | rsqwaiters | rsqholders
---------+------------+---------------+---------------+--------------+--------------+----------------+----------------+------------+------------
   50940 | m212_q     |             1 |             1 |           -1 |          519 |             -1 |  1.31072e+08 |          1 |          1
    6055 | pg_default |            20 |             0 |           -1 |            0 |             -1 |              0 |          0 |          0

-- BEFORE
 queuename | queries_submitted | queries_admitted | queries_rejected | queries_completed | total_wait_time_secs | total_cost | total_memory_kb
-----------+-------------------+------------------+------------------+-------------------+----------------------+------------+-----------------
 m212_q    |                41 |               36 |                5 |                36 |                   62 |  113573405 |         5504000
-- AFTER
 m212_q    |                44 |               39 |                5 |                39 |                   68 |  113574824 |         5888000

-- deltas:  +3 submitted, +3 admitted, +3 completed, +6 s waited
--          total_cost   +1419 = 519 + 450 + 450   (ceil of each plan's total_cost)
--          total_memory +384000 = 3 x 128000 kB   (statement_mem, three times)

That view has one trap worth naming: queries_rejected conflates two very different outcomes, because pgstat_resqueue_rejected() is called both when a statement errors for exceeding MAX_COST (resscheduler.c:706) and when it is waved through for costing less than MIN_COST (:746) — so a queue with a healthy MIN_COST shows a large and entirely benign rejection count. Note too that pg_stat_reset() does not clear these rows: resource queues are marked accessed_across_databases (pgstat.c:348), making them cluster-wide objects a per-database reset does not own. Which raises the closing question: why is this the legacy manager? Not because it fails — everything demonstrated above works, and it is what this cluster runs. The case is visible in the code itself. MAX_COST rations planner cost, a unit that correlates only loosely with anything an administrator cares about. There is no CPU or IO enforcement at all: PRIORITY shapes CPU by making processes sleep, which cannot bound a runaway and has no analogue for disk. MEMORY_LIMIT divides a budget by a slot count rather than by anything a query is doing. The lock mode is ExclusiveLock because, in the README’s words, “safety is the initial goal”, with “more sophisticated detection logic” listed as an obvious next step that never came. ALTERQUEUE_SMALL_THRESHOLD is unreachable. pgstat_count_queue_wait and five sibling counters sit commented out in resqueue.c (:573, :575, :600, :840). And execUtils.c:2323 says, in a code comment, “Someday, we should fix resource queues.”

Resource groups (§21.1) answer each of those: real CPU ceilings and weights, real IO limits, a memory budget owned by the group, and the kernel doing the enforcing. That is why they are the strategic direction. It is also why they are harder to deploy — they need a cgroup hierarchy in a generation this CentOS 7 box cannot provide — and why queue remains the compiled default. A manager that starts everywhere and rations the wrong things still beats one that cannot start. Beyond that, the two managers of this chapter differ in nearly every mechanism and agree on the only question that matters: who may use how much. Both attach a policy to a role, both consult it once per statement, both keep a FIFO of waiters in shared memory, and both must decide before the statement fans out across the cluster — because after that, there is nothing left to negotiate with.

Which is a fair place to stop. The book began with bytes on disk: page layout, tuple headers, the append-optimised and column-oriented alternatives, indexes, vacuum (Part I). Part II made those bytes safe to share — transactions, snapshots, MVCC, the lock manager, and Cloudberry’s distributed twist on all three. Part III took a SQL string and turned it into work: parse, rewrite, two optimisers, slices and motions, gangs and the interconnect, and an executor that measures its own memory. Chapter 20 asked what happens when a machine in that picture dies. This chapter asked what happens when the machine is merely busy — which, on a production cluster, is the more common emergency of the two. The layers are separable enough to read one at a time, and coupled enough that the last one had to be about all the others: a resource manager is only ever rationing the storage, the transactions and the query processing that came before it.