Retrieving Query Results with a Parallel Retrieve Cursor

A parallel retrieve cursor is an enhanced cursor implementation that you can use to create a special kind of cursor on the WarehousePG coordinator node, and retrieve query results, on demand and in parallel, directly from the WarehousePG segments.

About Parallel Retrieve Cursors

You use a cursor to retrieve a smaller number of rows at a time from a larger query. When you declare a parallel retrieve cursor, the WarehousePG Database Query Dispatcher (QD) dispatches the query plan to each Query Executor (QE), and creates an endpoint on each QE before it executes the query. An endpoint is a query result source for a parallel retrieve cursor on a specific QE. Instead of returning the query result to the QD, an endpoint retains the query result for retrieval via a different process: a direct connection to the endpoint. You open a special retrieve mode connection, called a retrieve session, and use the new RETRIEVE SQL command to retrieve query results from each parallel retrieve cursor endpoint. You can retrieve from parallel retrieve cursor endpoints on demand and in parallel.

You can use the following functions and views to examine and manage parallel retrieve cursors and endpoints:

Using a Parallel Retrieve Cursor

You will perform the following tasks when you use a WarehousePG parallel retrieve cursor to read query results in parallel from WarehousePG segments:

1. Declare the parallel retrieve cursor.
2. List the endpoints of the parallel retrieve cursor.
3. Open a retrieve connection to each endpoint.
4. Retrieve data from each endpoint.
5. Wait for data retrieval to complete.
6. Handle data retrieval errors.
7. Close the parallel retrieve cursor.

In addition to the above, you may optionally choose to List all parallel retrieve cursors in the system or List segment-specific retrieve session information.

Declaring a Parallel Retrieve Cursor

You DECLARE a cursor to retrieve a smaller number of rows at a time from a larger query. When you declare a parallel retrieve cursor, you can retrieve the query results directly from the WarehousePG segments.

The syntax for declaring a parallel retrieve cursor is similar to that of declaring a regular cursor; you must additionally include the PARALLEL RETRIEVE keywords in the command. You can declare a parallel retrieve cursor only within a transaction, and the cursor name that you specify when you declare the cursor must be unique within the transaction.

For example, the following commands begin a transaction and declare a parallel retrieve cursor named prc1 to retrieve the results from a specific query:

BEGIN;
DECLARE prc1 PARALLEL RETRIEVE CURSOR FOR <i>query</i>;

WarehousePG creates the endpoint(s) on the QD or QEs, depending on the query parameters:

• WarehousePG creates an endpoint on the QD when the query results must be gathered by the coordinator. For example, this DECLARE statement requires that the coordinator gather the query results:

  → WrapCopy

  DECLARE c1 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM t1 ORDER BY a;

  You may choose to run the EXPLAIN command on the parallel retrieve cursor query to identify when motion is involved. Consider using a regular cursor for such queries.

• When the query involves direct dispatch to a segment (the query is filtered on the distribution key), WarehousePG creates the endpoint(s) on specific segment host(s). For example, this DECLARE statement may result in the creation of single endpoint:

  → WrapCopy

  DECLARE c2 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM t1 WHERE a=1;

• WarehousePG creates the endpoints on all segment hosts when all hosts contribute to the query results. This example DECLARE statement results in all segments contributing query results:

  → WrapCopy

  DECLARE c3 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM t1;

The DECLARE command returns when the endpoints are ready and query execution has begun.

Listing a Parallel Retrieve Cursor's Endpoints

You can obtain the information that you need to initiate a retrieve connection to an endpoint by invoking the gp_get_endpoints() function or examining the gp_endpoints view in a session on the WarehousePG coordinator host:

SELECT * FROM gp_get_endpoints();
SELECT * FROM gp_endpoints;

These commands return the list of endpoints in a table with the following columns:

Refer to the gp_endpoints view reference page for more information about the endpoint attributes returned by these commands.

You can similarly invoke the gp_get_session_endpoints() function or examine the gp_session_endpoints view to list the endpoints created for the parallel retrieve cursors declared in the current session and by the current user.

Opening a Retrieve Session

After you declare a parallel retrieve cursor, you can open a retrieve session to each endpoint. Only a single retrieve session may be open to an endpoint at any given time.

Retrieve session authentication does not depend on the pg_hba.conf file, but rather on an authentication token (auth_token) generated by WarehousePG.

When you initiate a retrieve session to an endpoint:

• The user that you specify for the retrieve session must be the user that declared the parallel retrieve cursor (the username returned by gp_endpoints). This user must have WarehousePG login privileges.

• You specify the hostname and port returned by gp_endpoints for the endpoint.

• You authenticate the retrieve session by specifying the auth_token returned for the endpoint via the PGPASSWORD environment variable, or when prompted for the retrieve session Password.

• You must specify the gp_retrieve_conn server configuration parameter on the connection request, and set the value to true .

For example, if you are initiating a retrieve session via psql:

PGOPTIONS='-c gp_retrieve_conn=true' psql -h <hostname> -p <port> -U <username> -d <dbname>

To distinguish a retrieve session from other sessions running on a segment host, WarehousePG includes the [retrieve] tag on the ps command output display for the process.

Retrieving Data From the Endpoint

Once you establish a retrieve session, you retrieve the tuples associated with a query result on that endpoint using the RETRIEVE command.

You can specify a (positive) number of rows to retrieve, or ALL rows:

RETRIEVE 7 FROM ENDPOINT prc10000003300000003;
RETRIEVE ALL FROM ENDPOINT prc10000003300000003;

WarehousePG returns an empty set if there are no more rows to retrieve from the endpoint.

Waiting for Data Retrieval to Complete

Use the gp_wait_parallel_retrieve_cursor() function to display the status of data retrieval from a parallel retrieve cursor, or to wait for all endpoints to finishing retrieving the data. You invoke this function in the transaction block in which you declared the parallel retrieve cursor.

gp_wait_parallel_retrieve_cursor() returns true only when all tuples are fully retrieved from all endpoints. In all other cases, the function returns false and may additionally throw an error.

The function signatures of gp_wait_parallel_retrieve_cursor() follow:

gp_wait_parallel_retrieve_cursor( cursorname text )
gp_wait_parallel_retrieve_cursor( cursorname text, timeout_sec int4 )

You must identify the name of the cursor when you invoke this function. The timeout argument is optional:

• The default timeout is 0 seconds: WarehousePG checks the retrieval status of all endpoints and returns the result immediately.

• A timeout value of -1 seconds instructs WarehousePG to block until all data from all endpoints has been retrieved, or block until an error occurs.

• The function reports the retrieval status after a timeout occurs for any other positive timeout value that you specify.

gp_wait_parallel_retrieve_cursor() returns when it encounters one of the following conditions:

• All data has been retrieved from all endpoints.
• A timeout has occurred.
• An error has occurred.

Handling Data Retrieval Errors

An error can occur in a retrieve sesson when:

• You cancel or interrupt the retrieve operation.
• The endpoint is only partially retrieved when the retrieve session quits.

When an error occurs in a specific retrieve session, WarehousePG removes the endpoint from the QE. Other retrieve sessions continue to function as normal.

If you close the transaction before fully retrieving from all endpoints, or if gp_wait_parallel_retrieve_cursor() returns an error, WarehousePG terminates all remaining open retrieve sessions.

Closing the Cursor

When you have completed retrieving data from the parallel retrieve cursor, close the cursor and end the transaction:

CLOSE prc1;
END;

On closing, WarehousePG frees all resources associated with the parallel retrieve cursor and its endpoints.

Listing All Parallel Retrieve Cursors

The pg_cursors view lists all declared cursors that are currently available in the system. You can obtain information about all parallel retrieve cursors by running the following command:

SELECT * FROM pg_cursors WHERE is_parallel = true;

Listing Segment-Specific Retrieve Session Information

You can obtain information about all retrieve sessions to a specific QE endpoint by invoking the gp_get_segment_endpoints() function or examining the gp_segment_endpoints view:

SELECT * FROM gp_get_segment_endpoints();
SELECT * FROM gp_segment_endpoints;

These commands provide information about the retrieve sessions associated with a QE endpoint for all active parallel retrieve cursors declared by the current user. When the WarehousePG superuser invokes the command, it returns the retrieve session information for all endpoints on the QE created for all parallel retrieve cursors declared by all users.

You can obtain segment-specific retrieve session information in two ways: from the QD, or via a utility-mode connection to the endpoint:

• QD example:

  → WrapCopy

  SELECT * from gp_dist_random('gp_segment_endpoints');

  Display the information filtered to a specific segment:

  → WrapCopy

  SELECT * from gp_dist_random('gp_segment_endpoints') WHERE gp_segment_id = 0;

• Example utilizing a utility-mode connection to the endpoint:

  → WrapCopy

  $ PGOPTIONS='-c gp_session_role=utility' psql -h sdw3 -U localuser -p 6001 -d testdb
  
  testdb=> SELECT * FROM gp_segment_endpoints;

The commands return endpoint and retrieve session information in a table with the following columns:

Refer to the gp_segment_endpoints view reference page for more information about the endpoint attributes returned by these commands.

Limiting the Number of Concurrently Open Cursors

By default, WarehousePG does not limit the number of parallel retrieve cursors that are active in the cluster (up to the maximum value of 1024). The WarehousePG superuser can set the gp_max_parallel_cursors server configuration parameter to limit the number of open cursors.

Known Issues and Limitations

The parallel retrieve cursor implementation has the following limitations:

• The WarehousePG query optimizer (GPORCA) does not support queries on a parallel retrieve cursor.
• WarehousePG ignores the BINARY clause when you declare a parallel retrieve cursor.
• Parallel retrieve cursors cannot be declared WITH HOLD.
• Parallel retrieve cursors do not support the FETCH and MOVE cursor operations.
• Parallel retrieve cursors are not supported in SPI; you cannot declare a parallel retrieve cursor in a PL/pgSQL function.

Additional Documentation

Refer to the README in the WarehousePG github repository for additional information about the parallel retrieve cursor implementation. You can also find parallel retrieve cursor programming examples in the repository.

Example

Create a parallel retrieve cursor and use it to pull query results from a WarehousePG cluster:

1. Open a psql session to the WarehousePG coordinator host:

   → WrapCopy

   psql -d testdb

2. Start the transaction:

   → WrapCopy

   BEGIN;

3. Declare a parallel retrieve cursor named prc1 for a SELECT * query on a table:

   → WrapCopy

   DECLARE prc1 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM t1;

4. Obtain the endpoints for this parallel retrieve cursor:

   → WrapCopy

   SELECT * FROM gp_endpoints WHERE cursorname='prc1';
    gp_segment_id |            auth_token            | cursorname | sessionid | hostname | port | username | state |     endpointname     
   ---------------+----------------------------------+------------+-----------+----------+------+----------+-------+----------------------
                2 | 39a2dc90a82fca668e04d04e0338f105 | prc1       |        51 | sdw1     | 6000 | bill     | READY | prc10000003300000003
                3 | 1a6b29f0f4cad514a8c3936f9239c50d | prc1       |        51 | sdw1     | 6001 | bill     | READY | prc10000003300000003
                4 | 1ae948c8650ebd76bfa1a1a9fa535d93 | prc1       |        51 | sdw2     | 6000 | bill     | READY | prc10000003300000003
                5 | f10f180133acff608275d87966f8c7d9 | prc1       |        51 | sdw2     | 6001 | bill     | READY | prc10000003300000003
                6 | dda0b194f74a89ed87b592b27ddc0e39 | prc1       |        51 | sdw3     | 6000 | bill     | READY | prc10000003300000003
                7 | 037f8c747a5dc1b75fb10524b676b9e8 | prc1       |        51 | sdw3     | 6001 | bill     | READY | prc10000003300000003
                8 | c43ac67030dbc819da9d2fd8b576410c | prc1       |        51 | sdw4     | 6000 | bill     | READY | prc10000003300000003
                9 | e514ee276f6b2863142aa2652cbccd85 | prc1       |        51 | sdw4     | 6001 | bill     | READY | prc10000003300000003
   (8 rows)

5. Wait until all endpoints are fully retrieved:

   → WrapCopy

   SELECT gp_wait_parallel_retrieve_cursor( 'prc1', -1 );

6. For each endpoint:

   1. Open a retrieve session. For example, to open a retrieve session to the segment instance running on sdw3, port number 6001, run the following command in a different terminal window; when prompted for the password, provide the auth_token identified in row 7 of the gp_endpoints output:

      → WrapCopy

      $ PGOPTIONS='-c gp_retrieve_conn=true' psql -h sdw3 -U localuser -p 6001 -d testdb
      Password:

   2. Retrieve data from the endpoint:

      → WrapCopy

      -- Retrieve 7 rows of data from this session
      RETRIEVE 7 FROM ENDPOINT prc10000003300000003
      -- Retrieve the remaining rows of data from this session
      RETRIEVE ALL FROM ENDPOINT prc10000003300000003

   3. Exit the retrieve session:

      → WrapCopy

      \q

7. In the original psql session (the session in which you declared the parallel retrieve cursor), verify that the gp_wait_parallel_retrieve_cursor() function returned t. Then close the cursor and complete the transaction:

   → WrapCopy

   CLOSE prc1;
   END;