这是本节的多页打印视图。点击此处打印.

客户端连接函数

1: CLOSE_ALL_RESULTSETS
2: CLOSE_RESULTSET
3: DESCRIBE_LOAD_BALANCE_DECISION
4: GET_CLIENT_LABEL
5: RESET_LOAD_BALANCE_POLICY
6: SET_CLIENT_LABEL
7: SET_LOAD_BALANCE_POLICY

此部分包含 Vertica 专用的客户端连接管理函数。

1 - CLOSE_ALL_RESULTSETS

关闭多个活动结果集 (MARS) 中的所有结果集会话，并为其他结果集释放 MARS 存储。

这是元函数。您必须在顶级 SELECT 语句中调用元函数。

行为类型

易变

语法

SELECT CLOSE_ALL_RESULTSETS ('session_id')

参数

session_id: 指定多个活动结果集会话的字符串。

特权

无；但是，在不具备超级用户权限的情况下，您只能关闭您自己会话的结果。

示例

此示例显示了如何查看 MARS 结果集，然后关闭该结果集，再确认该结果集已被关闭。

查询 MARS 存储表。一个会话 ID 处于打开状态，并在输出中显示三个结果集。

=> 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)

关闭会话 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)

再次查询 MARS 存储表，以了解当前状态。您可以看到，该会话和结果集已被关闭：

=> 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

关闭多个活动结果集 (MARS) 中的某一特定结果集，并释放其他结果集的 MARS 存储。

这是元函数。您必须在顶级 SELECT 语句中调用元函数。

行为类型

易变

语法

SELECT CLOSE_RESULTSET ('session_id', ResultSetID)

参数

session_id: 指定包含要关闭的 ResultSetID 的多个活动结果集会话的字符串。
ResultSetID: 指定要关闭哪些结果集的整数。

特权

无；但是，在不具备超级用户权限的情况下，您只能关闭您自己会话的结果。

示例

此示例显示了一个处于打开状态的 MARS 存储表。一个 session_id 目前处于打开状态，并在输出中显示一个结果集。

=> 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)

关闭用户会话 server1.company.-83046:1y28gu9 和结果集 1：

=> SELECT CLOSE_RESULTSET('server1.company.-83046:1y28gu9', 1);

            close_resultset
-------------------------------------------------------------
 Closing result set 1 from server1.company.-83046:1y28gu9
(1 row)

再次查询 MARS 存储表，以了解当前状态。您可以看到，结果集 1 现已关闭：

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

评估是否有任何负载均衡路由规则适用于给定的 IP 地址和描述如何处理客户端连接。当您评估已创建的连接负载均衡策略，以确保它们按您期望的方式工作时，此函数很有用。

您向此函数传递一个客户端连接的 IP 地址，它使用负载均衡路由规则来确定如何处理该连接。此函数使用的逻辑与 Vertica 负载均衡客户端连接时使用的逻辑相同，包括确定哪些节点可用于处理客户端连接。

此函数假定客户端连接已选择负载均衡。如果实际客户端未选择负载均衡，则不会重定向连接。有关在客户端启用负载均衡的信息，请参阅 ADO.NET 中的负载均衡、JDBC 中的负载均衡和负载均衡。对于 vsql，使用 -C 命令行选项来启用负载均衡。

这是元函数。您必须在顶级 SELECT 语句中调用元函数。

行为类型

易变

语法

DESCRIBE_LOAD_BALANCE_DECISION('ip_address')

参数

'ip_address': 要根据负载均衡规则测试的客户端连接的 IP 地址。它可以是 IPv4 或 IPv6 地址。

返回值

逐步描述如何评估负载均衡规则，包括最终决定选择数据库中的哪个节点来为连接提供服务。

特权

无。

示例

以下示例演示了使用三个不同的 IP 地址调用 DESCRIBE_LOAD_BALANCE_DECISION，其中两个由不同的路由规则处理，一个不由任何规则处理。

=> 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)

以下示例演示了使用相同 IP 地址重复调用 DESCRIBE_LOAD_BALANCE_DECISION。您可以看到负载均衡组的 ROUNDROBIN 负载均衡策略让它在负载均衡组的两个节点之间切换：

=> 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)

另请参阅

4 - GET_CLIENT_LABEL

返回当前会话的客户端连接标签。

这是元函数。您必须在顶级 SELECT 语句中调用元函数。

行为类型

易变

语法

GET_CLIENT_LABEL()

特权

无

示例

返回当前的客户端连接标记：

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

另请参阅

设置客户端连接标记

5 - RESET_LOAD_BALANCE_POLICY

重置群集中的每个主机所维护的计数器，该计数器用于跟踪当本机连接负载均衡方案设置为 ROUNDROBIN 时将客户端指向的主机。要重置计数器，请在所有群集节点上运行此函数。

这是元函数。您必须在顶级 SELECT 语句中调用元函数。

行为类型

易变

语法

RESET_LOAD_BALANCE_POLICY()

特权

超级用户

示例

=> SELECT RESET_LOAD_BALANCE_POLICY();


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

6 - SET_CLIENT_LABEL

将标签分配到当前会话的客户端连接。您可以使用此标签来区分客户端连接。

标签显示在 v_monitor.sessions 表中。但是，这些标签不会在数据收集器表中更新，因为更改发生在 Vertica 建立连接之后。

这是元函数。您必须在顶级 SELECT 语句中调用元函数。

行为类型

易变

语法

SET_CLIENT_LABEL('label‑name')

参数

label‑name: 分配到客户端连接标签的 VARCHAR 名称。

特权

无

示例

将标签 data_load_application 分配到当前客户端连接：

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

另请参阅

设置客户端连接标记

7 - SET_LOAD_BALANCE_POLICY

设置本机连接负载均衡如何选择主机来处理客户端连接。

这是元函数。您必须在顶级 SELECT 语句中调用元函数。

行为类型

易变

语法

SET_LOAD_BALANCE_POLICY('policy')

参数

policy

要使用的负载均衡策略的名称，为以下几项之一：

NONE （默认值）：禁用本机连接负载均衡。
ROUNDROBIN：从群集中处于启动状态的主机循环列表中选择下一个主机。例如，在包含三节点的群集中，依次迭代节点 1、节点 2 和节点 3，然后返回到节点 1。群集中的每个主机都在循环链表中维护自己的指向下一个主机的指针，而不存在一个群集范围内的状态。
RANDOM：从群集中所有处于启动的主机中随机选择一个主机。

注意

即使在服务器上将负载均衡策略设置为非 NONE 的项，客户端也必须通过设置连接属性，以指示其连接要进行负载均衡。

特权

超级用户

示例

以下示例演示了如何通过将负载均衡方案设置为 ROUNDROBIN 来在服务器上启用本机连接负载均衡：

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

另请参阅

关于本机连接负载均衡