Multiple active result sets functions

• 1: CLOSE_ALL_RESULTSETS
• 2: CLOSE_RESULTSET

1 - CLOSE_ALL_RESULTSETS

Closes all result set sessions within Multiple Active Result Sets (MARS) and frees the MARS storage for other result sets.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Syntax

SELECT CLOSE_ALL_RESULTSETS ('session_id')

Parameters

session_id

A string that specifies the Multiple Active Result Sets session.

Privileges

None; however, without superuser privileges, you can only close your own session's results.

Examples

This example shows how you can view a MARS result set, then close the result set, and then confirm that the result set has been closed.

Query the MARS storage table. One session ID is open and three result sets appear in the output.

=> SELECT * FROM SESSION_MARS_STORE;

    node_name     |            session_id             | user_name | resultset_id | row_count | remaining_row_count | bytes_used
------------------+-----------------------------------+-----------+--------------+-----------+---------------------+------------
 v_vmart_node0001 | server1.company.-83046:1y28gu9    | dbadmin   |            7 |    777460 |              776460 |   89692848
 v_vmart_node0001 | server1.company.-83046:1y28gu9    | dbadmin   |            8 |    324349 |              323349 |   81862010
 v_vmart_node0001 | server1.company.-83046:1y28gu9    | dbadmin   |            9 |    277947 |              276947 |   32978280
(1 row)

Close all result sets for session server1.company.-83046:1y28gu9:

=> SELECT CLOSE_ALL_RESULTSETS('server1.company.-83046:1y28gu9');
             close_all_resultsets
-------------------------------------------------------------
 Closing all result sets from server1.company.-83046:1y28gu9
(1 row)

Query the MARS storage table again for the current status. You can see that the session and result sets have been closed:

=> SELECT * FROM SESSION_MARS_STORE;

    node_name     |            session_id             | user_name | resultset_id | row_count | remaining_row_count | bytes_used
------------------+-----------------------------------+-----------+--------------+-----------+---------------------+------------
(0 rows)

2 - CLOSE_RESULTSET

Closes a specific result set within Multiple Active Result Sets (MARS) and frees the MARS storage for other result sets.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Syntax

SELECT CLOSE_RESULTSET ('session_id', ResultSetID)

Parameters

session_id

A string that specifies the Multiple Active Result Sets session containing the ResultSetID to close.

ResultSetID

An integer that specifies which result set to close.

Privileges

None; however, without superuser privileges, you can only close your own session's results.

Examples

This example shows a MARS storage table opened. One session_id is currently open, and one result set appears in the output.

=> SELECT * FROM SESSION_MARS_STORE;
    node_name     |            session_id             | user_name | resultset_id | row_count | remaining_row_count | bytes_used
------------------+-----------------------------------+-----------+--------------+-----------+---------------------+------------
 v_vmart_node0001 | server1.company.-83046:1y28gu9    | dbadmin   |            1 |    318718 |              312718 |   80441904
(1 row)

Close user session server1.company.-83046:1y28gu9 and result set 1:

=> SELECT CLOSE_RESULTSET('server1.company.-83046:1y28gu9', 1);
            close_resultset
-------------------------------------------------------------
 Closing result set 1 from server1.company.-83046:1y28gu9
(1 row)

Query the MARS storage table again for current status. You can see that result set 1 is now closed:

SELECT * FROM SESSION_MARS_STORE;

    node_name     |            session_id             | user_name | resultset_id | row_count | remaining_row_count | bytes_used
------------------+-----------------------------------+-----------+--------------+-----------+---------------------+------------
(0 rows)