Parallel Retrieve Cursors and Endpoints in Cloudberry

The fastest way to get a large result set out of the cluster is to not send it through the coordinator at all.

Every plan in this book has ended the same way. Three segments scan, join and aggregate in parallel, and then a Gather Motion 3:1 funnels all of it into one process — the QD — which writes the result to one client socket (§13.1). For most work that funnel is free: the interesting rows are few. But when a client wants a billion rows — a connector feeding Spark, an ML training loader, a bulk extract — the cluster can produce results several times faster than a single coordinator process can forward them. The N-way machinery of Chapters 18 and 19 ends in a one-lane exit.

A parallel retrieve cursor removes the funnel. The plan is deliberately left un-gathered: no Motion is added on top, each QE keeps its own slice of the result, and the tuples are parked in an endpoint — a small shared-memory queue in the segment’s own address space. N client connections then attach to N segments and pull concurrently. It is the one place in Cloudberry where an ordinary client speaks directly to a segment as a first-class client, and the fitting close to a chapter about moving data: here the interconnect gets out of the way.

19.6.1 Declaring one, and the plan that is missing something

A parallel retrieve cursor is declared like any other cursor, except that it must live inside an explicit transaction and its name must be unique in that transaction. The reason is not bookkeeping: the QEs that hold the endpoints are this transaction’s gang, and the tuples they are holding belong to this transaction’s snapshot (§10.5). Close the transaction and the result evaporates — which is also why WITH HOLD is rejected outright (analyze.c:3409). Our demo table is nine rows over three segments:

Nine rows, hash-distributed. Remember which ids live on segment 0 — we are going to pull them out of that segment by hand.

CREATE TABLE m196_t (id int, v text) DISTRIBUTED BY (id);
INSERT INTO m196_t SELECT g, 'v'||g FROM generate_series(1,9) g;
SELECT gp_segment_id, count(*), string_agg(id::text, ',' ORDER BY id)
  FROM m196_t GROUP BY 1 ORDER BY 1;

 gp_segment_id | count | string_agg
---------------+-------+------------
             0 |     5 | 2,3,4,7,8
             1 |     1 | 1
             2 |     3 | 5,6,9
(3 rows)

EXPLAIN accepts the DECLARE itself, and this is the most informative thing in the section. Compare the cursor’s plan with the plan for the very same SELECT run as an ordinary statement:

The cursor’s plan next to the ordinary plan. Two things are missing from the first one: the Gather Motion, and slice 0.

BEGIN;
EXPLAIN (COSTS OFF, SLICETABLE ON) DECLARE c PARALLEL RETRIEVE CURSOR FOR SELECT * FROM m196_t;
ROLLBACK;
EXPLAIN (COSTS OFF, SLICETABLE ON) SELECT * FROM m196_t;

BEGIN
                   QUERY PLAN
-------------------------------------------------
 Seq Scan on m196_t
 Endpoint: on all 3 segments
 Slice 0: Reader; root 0; parent -1; gang size 3
 Optimizer: Postgres query optimizer
(4 rows)

ROLLBACK
                     QUERY PLAN
-----------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)
   ->  Seq Scan on m196_t
 Slice 0: Dispatcher; root 0; parent -1; gang size 0
 Slice 1: Reader; root 0; parent 0; gang size 3
 Optimizer: GPORCA
(5 rows)

Read the slice tables side by side (§19.3 taught the notation). The ordinary query has two slices: slice 0 is the Dispatcher — the QD itself, gang size 0 — and slice 1 is the reader gang of three that feeds it. The cursor has one slice, and it is a Reader with parent -1: the reader gang is the root. There is no gang above it, so there is nothing for the interconnect to connect (§19.2, §19.5). The extra Endpoint: line is printed by ExplainParallelRetrieveCursor (explain_gp.c:2308).

The second difference is the optimizer line: GPORCA becomes Postgres query optimizer. This is not a coincidence of this query — CURSOR_OPT_PARALLEL_RETRIEVE is one of the conditions in the gate that §17.1 dissected, so a parallel retrieve cursor is always planned by the PostgreSQL planner. Setting optimizer=on explicitly changes nothing; the gate is on the cursor option, not on the GUC alone.

src/backend/optimizer/plan/planner.c:398

	if (optimizer &&
		GP_ROLE_DISPATCH == Gp_role &&
		IS_QUERY_DISPATCHER() &&
		(cursorOptions & CURSOR_OPT_SKIP_FOREIGN_PARTITIONS) == 0 &&
		(cursorOptions & CURSOR_OPT_PARALLEL_RETRIEVE) == 0)
	{

The missing Gather is one if. planner.c:457 records the option as glob->is_parallel_cursor, and the place that would normally staple a Motion-to-Entry onto the cheapest path asks about it first. Without that call there is simply no Motion, and the top slice keeps the locus the query produced (§16.1).

src/backend/cdb/cdbllize.c:613

		if (query->commandType == CMD_SELECT || query->returningList)
		{
			if (!root->glob->is_parallel_cursor)
			{
				/*
				 * Query result needs to be brought back to the QD. Ask for
				 * a motion to bring it in. ...

So DECLARE dispatches a plan that runs and then stops with its tuples in hand. On each QE the executor recognises the root slice of a parallel retrieve cursor, builds a 64 KB shm_mq with CreateTupleQueueDestReceiver — the same tuple queue PostgreSQL uses between parallel workers, which §18.2 met from the other side — claims a slot in the shared sharedEndpoints[] array (1024 slots, cdbendpoint_private.h:19), and only then acknowledges (execMain.c:1008 and :1018, cdbendpoint.c:305–:313). The QD’s DECLARE does not return until every acknowledgement is in, via WaitEndpointsReady at portalcmds.c:196. That is the whole handshake:

19.6.2 Where the endpoints land

How many endpoints a cursor gets, and where, is decided entirely by the locus of the top of the plan — the same locus algebra as §16.1, read one last time. adjust_top_path_for_parallel_retrieve_cursor turns that locus into the top slice’s gang type, and GetParallelCursorEndpointPosition (cdbendpoint.c:225) turns it into the four-way classification EXPLAIN prints.

src/backend/cdb/cdbllize.c:1629

	if (CdbPathLocus_IsSingleQE(path->locus)
		|| CdbPathLocus_IsGeneral(path->locus)
		|| CdbPathLocus_IsEntry(path->locus))
	{
		slice->numsegments = 1;
		slice->gangType = GANGTYPE_ENTRYDB_READER;
	}
	else if (CdbPathLocus_IsSegmentGeneral(path->locus))
	{
		/* queries to replicated table run on a single segment. */
		slice->segindex = gp_session_id % path->locus.numsegments;
		slice->gangType = GANGTYPE_SINGLETON_READER;
	}

cursor query	top of plan	EXPLAIN says Endpoint:	endpoints
SELECT * FROM m196_t	Seq Scan	on all 3 segments	3 — one per segment
… WHERE id = 1	Seq Scan + Filter, direct dispatch	on segments: contentid [1]	1 — on segment 1 only
… WHERE id IN (1,5)	Seq Scan + Filter, direct dispatch	on segments: contentid [1, 2]	2
SELECT * FROM m196_r (replicated)	Seq Scan	on segment: contentid [n]	1 — on gp_session_id % 3
SELECT 1	Result	on coordinator	1 — on the coordinator
SELECT relname FROM pg_class LIMIT 3	Limit + Seq Scan	on coordinator	1
SELECT * FROM m196_t ORDER BY id	Gather Motion 3:1, Merge Key: id	on coordinator	1
SELECT count(*) FROM m196_t	Finalize Aggregate over Gather Motion	on coordinator	1

The direct-dispatch rows are the pleasing ones: a cursor over WHERE id = 1 gets exactly one endpoint, on the one segment that can hold that hash value (§16.4). The replicated-table row is stranger — a replicated table is on every segment, so the planner must pick one, and it picks it with gp_session_id % numsegments. Three consecutive sessions therefore march across the cluster:

The same cursor declared in three fresh coordinator sessions. Sessions 145, 146, 147 land on segments 1, 2, 0 — and the session id is also visible in the middle of the endpoint name.

-- run three times, in three separate psql sessions
BEGIN;
DECLARE m196_cr PARALLEL RETRIEVE CURSOR FOR SELECT * FROM m196_r;
SELECT current_setting('gp_session_id') AS sess, gp_segment_id, endpointname
  FROM gp_session_endpoints;
ROLLBACK;

 sess | gp_segment_id |      endpointname
------+---------------+-------------------------
 145  |             1 | m196_cr0000009100000002
(1 row)

 sess | gp_segment_id |      endpointname
------+---------------+-------------------------
 146  |             2 | m196_cr0000009200000002
(1 row)

 sess | gp_segment_id |      endpointname
------+---------------+-------------------------
 147  |             0 | m196_cr0000009300000002
(1 row)

The last four rows of the table are the honest part of this feature. An un-gathered plan is not always possible. A global ORDER BY, a LIMIT, a final aggregate — anything whose top node must see all segments’ rows in one process — still ends in a Gather Motion 3:1, and then the endpoint is created on the coordinator, where the tuples already are. Such a cursor is still perfectly usable, but it is a one-endpoint cursor with a coordinator bottleneck: the parallelism you get is the parallelism the plan’s locus gives you, and the header comment on cdbendpoint.h:5 says so in as many words. Reserve the feature for queries whose result can stay distributed.

A cursor whose plan ends in a merge Gather: one endpoint, gp_segment_id -1, port 7100. It is retrievable — from the coordinator, with all nine rows in sorted order — but there is nothing parallel about it.

BEGIN;
DECLARE m196_ord PARALLEL RETRIEVE CURSOR FOR SELECT id FROM m196_t ORDER BY id;
SELECT gp_segment_id, hostname, port, state, endpointname FROM gp_session_endpoints;

-- in another shell, against the COORDINATOR port:
--   PGPASSWORD=<token> PGOPTIONS='-c gp_retrieve_conn=true' --     psql -h cbdb-node1 -p 7100 -d postgres -c 'RETRIEVE ALL FROM ENDPOINT m196_ord0000004000000021'

 gp_segment_id |  hostname  | port | state |       endpointname
---------------+------------+------+-------+--------------------------
           -1 | cbdb-node1 | 7100 | READY | m196_ord0000004000000021
(1 row)

 id
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
(9 rows)

19.6.3 Three views, a name, and a token

Endpoints are not catalogue rows; they are shared-memory slots, so they are exposed through three functions wrapped in three views. gp_endpoints runs on the coordinator and dispatches: it shows every endpoint of every session (for a superuser; a plain user sees only their own) and is the one a client driver reads to learn where to connect. gp_session_endpoints is the same data filtered to the calling session — the view a program uses to find its own cursor. gp_segment_endpoints is different in kind: it is answered locally by whichever backend you ask, so you run it in a gp_role=utility connection to one segment, and it adds the two process ids that matter — senderpid (the QE holding the tuples) and receiverpid (the retrieve session, -1 if nobody has attached).

The same three endpoints seen three ways. Note the second query: gp_session_endpoints is empty in an unrelated session, even for a superuser who can see all three rows through gp_endpoints.

-- (1) an unrelated coordinator session, while the cursor's transaction is open elsewhere
SELECT gp_segment_id, cursorname, sessionid, hostname, port, username, state, endpointname
  FROM gp_endpoints ORDER BY 1;

-- (2) the same session, asking about its own endpoints
SELECT count(*) AS rows_visible FROM gp_session_endpoints;

-- (3) a utility connection straight to segment 0
--   PGOPTIONS='-c gp_role=utility' psql -p 7102 -d postgres
SELECT senderpid, receiverpid, state, gp_segment_id, sessionid, username, endpointname, cursorname
  FROM gp_segment_endpoints;

 gp_segment_id | cursorname | sessionid |  hostname  | port | username | state |       endpointname
---------------+------------+-----------+------------+------+----------+-------+--------------------------
             0 | m196_prc   |        18 | cbdb-node1 | 7102 | gpadmin  | READY | m196_prc0000001200000003
             1 | m196_prc   |        18 | cbdb-node1 | 7103 | gpadmin  | READY | m196_prc0000001200000003
             2 | m196_prc   |        18 | cbdb-node1 | 7104 | gpadmin  | READY | m196_prc0000001200000003
(3 rows)

 rows_visible
--------------
            0
(1 row)

 senderpid | receiverpid | state | gp_segment_id | sessionid | username |       endpointname       | cursorname
-----------+-------------+-------+---------------+-----------+----------+--------------------------+------------
     13060 |          -1 | READY |             0 |        18 | gpadmin  | m196_prc0000001200000003 | m196_prc
(1 row)

The endpoint name is the same on every segment, because it is derived from data all the QEs share. generate_endpoint_name (cdbendpointutils.c:557) concatenates the cursor name, gp_session_id as 8 hex digits, and gp_command_count as 8 more. So m196_prc0000001200000003 decodes as cursor m196_prc, session 0x12 = 18, command 3 — and the name is unique enough that dropping and re-declaring the same cursor name in the same session cannot be confused with the old one. (The comment at cdbendpoint_private.h:31 still claims the third part is a segment index; the code has said gp_command_count since the commit below it. Trust the code.)

The auth token is what lets a stranger connect. It is 16 bytes of pg_strong_random, cached per session on each QE (cdbendpoint.c:260) and rendered as 32 hex characters. Because it is generated independently on each segment, it is per (session, segment) — different for the same cursor on different segments, and shared by all cursors of one session on one segment:

Two cursors declared in one transaction. Six endpoints, but only three distinct tokens: a token authorises a retrieve session on one segment for one coordinator session, not one cursor.

BEGIN;
DECLARE ca PARALLEL RETRIEVE CURSOR FOR SELECT id FROM m196_t;
DECLARE cb PARALLEL RETRIEVE CURSOR FOR SELECT id+1 FROM m196_t;
SELECT gp_segment_id, cursorname, auth_token FROM gp_session_endpoints ORDER BY 1,2;

 gp_segment_id | cursorname |            auth_token
---------------+------------+----------------------------------
             0 | ca         | 8223ca4ca2b997d62d3c9cc4c78cbc1b
             0 | cb         | 8223ca4ca2b997d62d3c9cc4c78cbc1b
             1 | ca         | d2787bf004868e259316ff6bee482de9
             1 | cb         | d2787bf004868e259316ff6bee482de9
             2 | ca         | cae8681dc9c8e8309c5970df01803134
             2 | cb         | cae8681dc9c8e8309c5970df01803134
(6 rows)

The token is presented as the password, and this is the one security-relevant surprise in the feature: a retrieve connection does not consult pg_hba.conf at all. ClientAuthentication checks for the gp_retrieve_conn option first and returns before any HBA lookup. The only checks are that the role name equals the cursor’s owner, that the token matches a live endpoint token for that role on this segment, and that the role may log in.

src/backend/libpq/auth.c:712

	retrieve_conn_authenticated = false;
	if (cmd_options_include_retrieve_conn(port->cmdline_options) ||
		guc_options_include_retrieve_conn(port->guc_options))
	{
		retrieve_conn_authentication(port);
		retrieve_conn_authenticated = true;
		return;
	}

19.6.4 Actually retrieving

A client enters retrieve mode with the GUC gp_retrieve_conn=true in the startup packet — PGOPTIONS from psql, options in a connection string — aimed at the segment’s port, with the endpoint’s token as the password. That GUC is PGC_BACKEND (guc_gp.c:1006) so it can only be set at connection time; postinit.c:1350 then forces Gp_role = GP_ROLE_UTILITY for the session. Such a backend is deliberately crippled: the only statement it will parse is RETRIEVE (gram.y:16383), enforced by a single XOR at analyze.c:331. Here is a real session against segment 0, pulling out the ids we saw distributed there earlier:

A retrieve-mode psql on port 7102 draining segment 0 by hand: 2 rows, then the remaining 3, then nothing. Compare with the distribution captured at the start — 2,3,4,7,8, in that order, with no coordinator in the path.

$ PGPASSWORD=e99c6f36be30e4afdc01d2815f3cf611 PGOPTIONS='-c gp_retrieve_conn=true'     psql -h cbdb-node1 -p 7102 -d postgres

SELECT current_setting('gp_retrieve_conn'), current_setting('gp_role');
RETRIEVE 2 FROM ENDPOINT m196_prc0000001200000003;
RETRIEVE ALL FROM ENDPOINT m196_prc0000001200000003;
RETRIEVE ALL FROM ENDPOINT m196_prc0000001200000003;

ERROR:  This is a retrieve connection, but the query is not a RETRIEVE.

 gp_segment_id | id | v
---------------+----+----
             0 |  2 | v2
             0 |  3 | v3
(2 rows)

 gp_segment_id | id | v
---------------+----+----
             0 |  4 | v4
             0 |  7 | v7
             0 |  8 | v8
(3 rows)

 gp_segment_id | id | v
---------------+----+---
(0 rows)

While that session sat between its two RETRIEVEs, the endpoint’s state was visible from everywhere else in the cluster — and the segment-local view had filled in the receiver’s pid:

Observed from an unrelated coordinator session and from a utility connection to segment 0, mid-retrieve. The QD is told nothing until it asks.

-- unrelated coordinator session
SELECT gp_segment_id, state, endpointname FROM gp_endpoints ORDER BY 1;
-- utility connection to segment 0
SELECT senderpid, receiverpid, state, endpointname FROM gp_segment_endpoints;
-- in the cursor's own session
SELECT gp_wait_parallel_retrieve_cursor('m196_prc', 0);

 gp_segment_id |  state   |       endpointname
---------------+----------+--------------------------
             0 | ATTACHED | m196_prc0000001200000003
             1 | READY    | m196_prc0000001200000003
             2 | READY    | m196_prc0000001200000003
(3 rows)

 senderpid | receiverpid |  state   |       endpointname
-----------+-------------+----------+--------------------------
     13060 |       13311 | ATTACHED | m196_prc0000001200000003
(1 row)

 gp_wait_parallel_retrieve_cursor
----------------------------------
 f
(1 row)

The other two segments were drained at the same time, by two more processes, on two more ports — which is the entire point of the feature. Once all three queues hit end-of-stream, the endpoints report FINISHED, their senderpid becomes -1 (the QE has finished sending), and the coordinator’s gp_wait_parallel_retrieve_cursor flips to true:

Two independent client processes, two segments, concurrently — then the coordinator’s view. Nine rows crossed the wire; none of them crossed the coordinator.

$ PGPASSWORD=$T1 PGOPTIONS='-c gp_retrieve_conn=true' psql -h cbdb-node1 -p 7103 -d postgres     -c "RETRIEVE ALL FROM ENDPOINT m196_prc0000001200000003" &
$ PGPASSWORD=$T2 PGOPTIONS='-c gp_retrieve_conn=true' psql -h cbdb-node1 -p 7104 -d postgres     -c "RETRIEVE ALL FROM ENDPOINT m196_prc0000001200000003" &

-- then, on the coordinator:
SELECT gp_segment_id, state, endpointname FROM gp_endpoints ORDER BY 1;
SELECT gp_wait_parallel_retrieve_cursor('m196_prc', 0);

 gp_segment_id | id | v
---------------+----+----
             1 |  1 | v1
(1 row)

 gp_segment_id | id | v
---------------+----+----
             2 |  5 | v5
             2 |  6 | v6
             2 |  9 | v9
(3 rows)

 gp_segment_id |  state   |       endpointname
---------------+----------+--------------------------
             0 | FINISHED | m196_prc0000001200000003
             1 | FINISHED | m196_prc0000001200000003
             2 | FINISHED | m196_prc0000001200000003
(3 rows)

 gp_wait_parallel_retrieve_cursor
----------------------------------
 t
(1 row)

gp_wait_parallel_retrieve_cursor(name, timeout) is the coordinator’s only handle on progress: 0 polls, -1 blocks until everything is drained, and any retrieve-side error is re-raised here rather than being silently lost. It must be called in the declaring transaction. Between polls, a background timeout on the QD (enable_parallel_retrieve_cursor_check_timeout) keeps checking for QE-side failures. The five states it is reporting on are the enum at cdbendpoint.h:58:

state	meaning	set by
READY	slot allocated, 64 KB queue created, nobody attached	the QE sender, in alloc_endpoint()
RETRIEVING	a RETRIEVE is draining the queue right now	start_retrieve(), cdbendpointretrieve.c:346
ATTACHED	one retrieve session owns it, but stopped short of the end	finish_retrieve(), after RETRIEVE count
FINISHED	the queue was read to end-of-stream	finish_retrieve(), on queue EOF
RELEASED	the retriever failed or vanished before finishing — nobody may attach again	retrieve_cancel_action(), cdbendpointretrieve.c:676

19.6.5 The rules, and what breaks them

The feature is unusually strict, and its error messages are unusually good. An endpoint may be attached by exactly one retrieve session — receiverPid is a single field, not a list — and once a session has drained it, no other session may touch it, though the owning session may keep issuing RETRIEVE and get empty results. FETCH and MOVE are rejected on the QD side; SCROLL and WITH HOLD are rejected at parse time. Every one of these is real output from this cluster:

Six ways to get it wrong. The DETAIL lines come from validate_retrieve_endpoint (cdbendpointretrieve.c:379) — worth reading, because a connector will hit every one of them.

-- a second retrieve session, while the first is still ATTACHED
RETRIEVE ALL FROM ENDPOINT m196_prc0000001200000003;
-- a new session, after the endpoint was drained by another one
RETRIEVE ALL FROM ENDPOINT m196_prc0000001200000003;
-- a name nobody ever declared
RETRIEVE ALL FROM ENDPOINT m196_nosuch;
-- a RETRIEVE in a plain utility connection (no gp_retrieve_conn)
RETRIEVE ALL FROM ENDPOINT m196_prc0000001200000003;
-- a retrieve-mode connection with a token that is not a live endpoint's
psql -h cbdb-node1 -p 7102 ...
-- on the coordinator, in the declaring transaction
FETCH 1 FROM m196_prc;
DECLARE c SCROLL PARALLEL RETRIEVE CURSOR FOR SELECT 1;
DECLARE c PARALLEL RETRIEVE CURSOR WITH HOLD FOR SELECT 1;
DECLARE c PARALLEL RETRIEVE CURSOR FOR SELECT id FROM m196_t;   -- outside a transaction

ERROR:  endpoint m196_prc0000001200000003 was already attached by receiver(pid: 13311)
DETAIL:  An endpoint can only be attached by one retrieving session.

ERROR:  another session (pid: 13311) used the endpoint and completed retrieving

ERROR:  the endpoint m196_nosuch does not exist for session id 18

ERROR:  This is not a retrieve connection, but the query is a RETRIEVE.

psql: error: connection to server at "cbdb-node1" (10.100.0.3), port 7102 failed:
FATAL:  Authentication failure (Wrong password or no endpoint for the user)

ERROR:  cannot specify 'FETCH' for PARALLEL RETRIEVE CURSOR
HINT:  Use 'RETRIEVE' statement on endpoint instead.

ERROR:  SCROLL is not allowed for the PARALLEL RETRIEVE CURSORs
DETAIL:  Scrollable cursors can not be parallel

ERROR:  DECLARE PARALLEL RETRIEVE CURSOR WITH HOLD ... is not supported
DETAIL:  Holdable cursors can not be parallel

ERROR:  DECLARE CURSOR can only be used in transaction blocks

The most instructive failure is a client that walks away mid-retrieve, because a partially-drained endpoint cannot simply be left behind: its QE is still blocked on a queue nobody will read. The retrieve backend’s exit callback therefore reaches across into the endpoint slot, marks it RELEASED, and cancels the sender with a custom message — which surfaces on the coordinator, the next time the coordinator asks.

A client retrieves one row from segment 0 and disconnects. The endpoint on segment 0 is RELEASED while the other two are untouched — but the whole cursor is doomed: the coordinator’s next check aborts the transaction, taking the other two endpoints with it.

$ PGPASSWORD=$T0 PGOPTIONS='-c gp_retrieve_conn=true' psql -h cbdb-node1 -p 7102 -d postgres     -c "RETRIEVE 1 FROM ENDPOINT m196_prc20000004000000003"      -- then exits

SELECT gp_segment_id, state, endpointname FROM gp_endpoints ORDER BY 1;
-- and in the declaring session:
SELECT gp_wait_parallel_retrieve_cursor('m196_prc2', 0);
CLOSE m196_prc2;

 gp_segment_id | id
---------------+----
             0 |  2
(1 row)

 gp_segment_id |  state   |       endpointname
---------------+----------+---------------------------
             0 | RELEASED | m196_prc20000004000000003
             1 | READY    | m196_prc20000004000000003
             2 | READY    | m196_prc20000004000000003
(3 rows)

ERROR:  canceling MPP operation: "Endpoint retrieve session is quitting. All unfinished
parallel retrieve cursors on the session will be terminated."  (seg0 10.100.0.3:7102 pid=15994)
ERROR:  current transaction is aborted, commands ignored until end of transaction block

src/backend/cdb/endpoint/cdbendpointretrieve.c:686

	if (endpoint != NULL &&
		endpoint->receiverPid == MyProcPid &&
		endpoint->state != ENDPOINTSTATE_FINISHED)
	{
		endpoint->receiverPid = InvalidPid;
		endpoint->state = ENDPOINTSTATE_RELEASED;
		if (endpoint->senderPid != InvalidPid)
		{
			SetBackendCancelMessage(endpoint->senderPid, msg);
			kill(endpoint->senderPid, SIGINT);
		}
	}

The tidy endings are CLOSE and end-of-transaction. Either releases every endpoint of the cursor immediately — cancelling any RETRIEVE in flight — and until one of them happens, the QEs stay parked with their tuples: DestroyEndpointExecState is deliberately blocked so that the coordinator can still inspect state after the query has finished (cdbendpoint.c:44).

CLOSE, and then COMMIT, as the release paths. Three endpoints become none, synchronously.

BEGIN;
DECLARE m196_prc3 PARALLEL RETRIEVE CURSOR FOR SELECT id FROM m196_t;
SELECT count(*) AS endpoints FROM gp_endpoints WHERE cursorname='m196_prc3';
CLOSE m196_prc3;
SELECT count(*) AS endpoints FROM gp_endpoints WHERE cursorname='m196_prc3';
COMMIT;

DECLARE PARALLEL RETRIEVE CURSOR
 endpoints
-----------
         3
(1 row)

CLOSE CURSOR
 endpoints
-----------
         0
(1 row)

COMMIT

19.6.6 Where it sits, and the end of Part III

Put the pieces beside the rest of the chapter and the shape is familiar. The QE that owns an endpoint is doing exactly what a Motion sender does (§19.4): it runs a slice to completion and pushes tuples into a bounded queue, blocking when the queue is full — which is why a retrieve client that stalls stalls a segment process, the same coupling that makes ExecSquelchNode and prefetch_inner necessary elsewhere (§18.1, §18.4). What changes is the two ends. The receiver is not a peer QE but an unrelated client process, so the last hop is a 64 KB shm_mq in the segment’s own shared memory rather than a UDP or TCP route through the interconnect (§19.5) — the pluggable IPC layer is not involved in the final hop at all. And there is no gang above the top slice, so the dispatcher (§19.2) keeps only the control plane: DECLARE, CHECK, CLOSE.

Is it worth the client-side complexity? Rarely by hand — the honest answer is that this feature exists for connectors, not for people. Something has to enumerate gp_endpoints, open one connection per endpoint to the right host and port, present the right token, drive N cursors in lockstep, and handle the failure mode where one worker’s death poisons the whole transaction. That is a library’s job, and the two example programs in src/test/examples/test_parallel_retrieve_cursor_*.c are exactly that library in miniature. When the consumer is a distributed compute framework that already has N workers on N hosts — Spark, Flink, a training data loader — matching its parallelism to the cluster’s turns the coordinator from a wall into a control channel. When the consumer is a single process that will iterate the rows anyway, the ordinary Gather Motion was never the bottleneck.

Part III ends here. Chapters 15 to 17 built a plan and cut it into slices; Chapter 18 executed the nodes; this chapter carried tuples between the processes that ran them — and, in this last section, arranged for them not to be carried at all. One question is left over from all of it: every gang, every motion queue and every endpoint we have allocated in Part III was allocated as though the machine were ours alone. It is not. Chapter 21 takes up who is allowed how much of it — resource groups, queues, and the accounting that decides which of these plans gets to run.