This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

Client connection functions

This section contains client connection management functions specific to Vertica.

This section contains client connection management functions specific to Vertica.

1 - CLOSE_ALL_RESULTSETS

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

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

Volatile

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.

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

Volatile

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)

3 - DESCRIBE_LOAD_BALANCE_DECISION

Evaluates if any load balancing routing rules apply to a given IP address and This function is useful when you are evaluating connection load balancing policies you have created, to ensure they work the way you expect them to.

Evaluates if any load balancing routing rules apply to a given IP address and describes how the client connection would be handled. This function is useful when you are evaluating connection load balancing policies you have created, to ensure they work the way you expect them to.

You pass this function an IP address of a client connection, and it uses the load balancing routing rules to determine how the connection will be handled. The logic this function uses is the same logic used when Vertica load balances client connections, including determining which nodes are available to handle the client connection.

This function assumes the client connection has opted into being load balanced. If actual clients have not opted into load balancing, the connections will not be redirected. See Load balancing in ADO.NET, Load balancing in JDBC, and Load balancing, for information on enabling load balancing on the client. For vsql, use the -C command-line option to enable load balancing.

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

Behavior type

Volatile

Syntax

DESCRIBE_LOAD_BALANCE_DECISION('ip_address')

Arguments

'ip_address'
An IP address of a client connection to be tested against the load balancing rules. This can be either an IPv4 or IPv6 address.

Return value

A step-by-step description of how the load balancing rules are being evaluated, including the final decision of which node in the database has been chosen to service the connection.

Privileges

None.

Examples

The following example demonstrates calling DESCRIBE_LOAD_BALANCE_DECISION with three different IP addresses, two of which are handled by different routing rules, and one which is not handled by any rule.

=> SELECT describe_load_balance_decision('192.168.1.25');
                        describe_load_balance_decision
--------------------------------------------------------------------------------
 Describing load balance decision for address [192.168.1.25]
Load balance cache internal version id (node-local): [2]
Considered rule [etl_rule] source ip filter [10.20.100.0/24]... input address
does not match source ip filter for this rule.
Considered rule [internal_clients] source ip filter [192.168.1.0/24]... input
address matches this rule
Matched to load balance group [group_1] the group has policy [ROUNDROBIN]
number of addresses [2]
(0) LB Address: [10.20.100.247]:5433
(1) LB Address: [10.20.100.248]:5433
Chose address at position [1]
Routing table decision: Success. Load balance redirect to: [10.20.100.248] port [5433]

(1 row)

=> SELECT describe_load_balance_decision('192.168.2.25');
                        describe_load_balance_decision
--------------------------------------------------------------------------------
 Describing load balance decision for address [192.168.2.25]
Load balance cache internal version id (node-local): [2]
Considered rule [etl_rule] source ip filter [10.20.100.0/24]... input address
does not match source ip filter for this rule.
Considered rule [internal_clients] source ip filter [192.168.1.0/24]... input
address does not match source ip filter for this rule.
Considered rule [subnet_192] source ip filter [192.0.0.0/8]... input address
matches this rule
Matched to load balance group [group_all] the group has policy [ROUNDROBIN]
number of addresses [3]
(0) LB Address: [10.20.100.247]:5433
(1) LB Address: [10.20.100.248]:5433
(2) LB Address: [10.20.100.249]:5433
Chose address at position [1]
Routing table decision: Success. Load balance redirect to: [10.20.100.248] port [5433]

(1 row)

=> SELECT describe_load_balance_decision('1.2.3.4');
                         describe_load_balance_decision
--------------------------------------------------------------------------------
 Describing load balance decision for address [1.2.3.4]
Load balance cache internal version id (node-local): [2]
Considered rule [etl_rule] source ip filter [10.20.100.0/24]... input address
does not match source ip filter for this rule.
Considered rule [internal_clients] source ip filter [192.168.1.0/24]... input
address does not match source ip filter for this rule.
Considered rule [subnet_192] source ip filter [192.0.0.0/8]... input address
does not match source ip filter for this rule.
Routing table decision: No matching routing rules: input address does not match
any routing rule source filters. Details: [Tried some rules but no matching]
No rules matched. Falling back to classic load balancing.
Classic load balance decision: Classic load balancing considered, but either
the policy was NONE or no target was available. Details: [NONE or invalid]

(1 row)

The following example demonstrates calling DESCRIBE_LOAD_BALANCE_DECISION repeatedly with the same IP address. You can see that the load balance group's ROUNDROBIN load balance policy has it switch between the two nodes in the load balance group:

=> SELECT describe_load_balance_decision('192.168.1.25');
                       describe_load_balance_decision
--------------------------------------------------------------------------------
 Describing load balance decision for address [192.168.1.25]
Load balance cache internal version id (node-local): [1]
Considered rule [etl_rule] source ip filter [10.20.100.0/24]... input address
does not match source ip filter for this rule.
Considered rule [internal_clients] source ip filter [192.168.1.0/24]... input
address matches this rule
Matched to load balance group [group_1] the group has policy [ROUNDROBIN]
number of addresses [2]
(0) LB Address: [10.20.100.247]:5433
(1) LB Address: [10.20.100.248]:5433
Chose address at position [1]
Routing table decision: Success. Load balance redirect to: [10.20.100.248]
port [5433]

(1 row)

=> SELECT describe_load_balance_decision('192.168.1.25');
                        describe_load_balance_decision
--------------------------------------------------------------------------------
 Describing load balance decision for address [192.168.1.25]
Load balance cache internal version id (node-local): [1]
Considered rule [etl_rule] source ip filter [10.20.100.0/24]... input address
does not match source ip filter for this rule.
Considered rule [internal_clients] source ip filter [192.168.1.0/24]... input
address matches this rule
Matched to load balance group [group_1] the group has policy [ROUNDROBIN]
number of addresses [2]
(0) LB Address: [10.20.100.247]:5433
(1) LB Address: [10.20.100.248]:5433
Chose address at position [0]
Routing table decision: Success. Load balance redirect to: [10.20.100.247]
port [5433]

(1 row)

=> SELECT describe_load_balance_decision('192.168.1.25');
                         describe_load_balance_decision
--------------------------------------------------------------------------------
 Describing load balance decision for address [192.168.1.25]
Load balance cache internal version id (node-local): [1]
Considered rule [etl_rule] source ip filter [10.20.100.0/24]... input address
does not match source ip filter for this rule.
Considered rule [internal_clients] source ip filter [192.168.1.0/24]... input
address matches this rule
Matched to load balance group [group_1] the group has policy [ROUNDROBIN]
number of addresses [2]
(0) LB Address: [10.20.100.247]:5433
(1) LB Address: [10.20.100.248]:5433
Chose address at position [1]
Routing table decision: Success. Load balance redirect to: [10.20.100.248]
port [5433]

(1 row)

See also

4 - GET_CLIENT_LABEL

Returns the client connection label for the current session.

Returns the client connection label for the current session.

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

Behavior type

Volatile

Syntax

GET_CLIENT_LABEL()

Privileges

None

Examples

Return the current client connection label:

=> SELECT GET_CLIENT_LABEL();
   GET_CLIENT_LABEL
-----------------------
 data_load_application
(1 row)

See also

Setting a client connection label

5 - RESET_LOAD_BALANCE_POLICY

Resets the counter each host in the cluster maintains, to track which host it will refer a client to when the native connection load balancing scheme is set to ROUNDROBIN.

Resets the counter each host in the cluster maintains, to track which host it will refer a client to when the native connection load balancing scheme is set to ROUNDROBIN. To reset the counter, run this function on all cluster nodes.

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

Behavior type

Volatile

Syntax

RESET_LOAD_BALANCE_POLICY()

Privileges

Superuser

Examples

=> SELECT RESET_LOAD_BALANCE_POLICY();

                        RESET_LOAD_BALANCE_POLICY
-------------------------------------------------------------------------
Successfully reset stateful client load balance policies: "roundrobin".
(1 row)

6 - SET_CLIENT_LABEL

Assigns a label to a client connection for the current session.

Assigns a label to a client connection for the current session. You can use this label to distinguish client connections.

Labels appear in the SESSIONS system table. However, only certain Data collector tables show new client labels set by SET_CLIENT_LABEL. For example, DC_REQUESTS_ISSUED reflects changes by SET_CLIENT_LABEL, while DC_SESSION_STARTS, which collects login data before SET_CLIENT_LABEL can be run, does not.

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

Behavior type

Volatile

Syntax

SET_CLIENT_LABEL('label-name')

Parameters

label-name
VARCHAR name assigned to the client connection label.

Privileges

None

Examples

Assign label data_load_application to the current client connection:

=> SELECT SET_CLIENT_LABEL('data_load_application');
             SET_CLIENT_LABEL
-------------------------------------------
 client_label set to data_load_application
(1 row)

See also

Setting a client connection label

7 - SET_LOAD_BALANCE_POLICY

Sets how native connection load balancing chooses a host to handle a client connection.

Sets how native connection load balancing chooses a host to handle a client connection.

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

Behavior type

Volatile

Syntax

SET_LOAD_BALANCE_POLICY('policy')

Parameters

policy
The name of the load balancing policy to use, one of the following:

  • NONE (default): Disables native connection load balancing.

  • ROUNDROBIN: Chooses the next host from a circular list of hosts in the cluster that are up—for example, in a three-node cluster, iterates over node1, node2, and node3, then wraps back to node1. Each host in the cluster maintains its own pointer to the next host in the circular list, rather than there being a single cluster-wide state.

  • RANDOM: Randomly chooses a host from among all hosts in the cluster that are up.

Privileges

Superuser

Examples

The following example demonstrates enabling native connection load balancing on the server by setting the load balancing scheme to ROUNDROBIN:

=> SELECT SET_LOAD_BALANCE_POLICY('ROUNDROBIN');
                  SET_LOAD_BALANCE_POLICY
--------------------------------------------------------------------------------
Successfully changed the client initiator load balancing policy to: roundrobin
(1 row)

See also

About native connection load balancing