EXECUTE DIRECT

Description

Executes an SQL statement on a specified node. Generally, the execution of SQL statements is automatically allocated to proper nodes by the cluster load. EXECUTE DIRECT is mainly used for database maintenance and testing.

Precautions

• When enable_nonsysadmin_execute_direct is off, only a system administrator has the EXECUTE DIRECT permission.
• To ensure data consistency across nodes, only the SELECT statement can be used. Transaction statements, DDL, and DML cannot be used.
• When the stddev aggregation calculation is performed on the specified DN using such statements, the result set is returned in triplet. For example, {3, 8, 30} indicates that the count result is 3, the sum result is 8, and the sum of squares is 30. When the AVG aggregation calculation is performed on the specified DN using such statements, the result set is returned in a binary tuple, for example, {4,2}. The result of count is 4, and that of sum is 2.
• When multiple nodes are specified, aggregate functions are not supported. If the query contains an aggregate function, the message "EXECUTE DIRECT on multinode not support agg functions." is returned.
• CN nodes do not store user table data. Therefore, do not execute SELECT for querying user tables on a CN.
• If the SQL statement to be executed is also EXECUTE DIRECT, do not nest it into EXECUTE DIRECT; instead, directly execute the inner EXECUTE DIRECT.
• The query result of the agg function is inconsistent with that on the CN. Multiple pieces of information are returned. The array_avg function is not supported.
• If the nodeoid or nodeoid list parameter is specified, this parameter takes effect only when the enable_direct_standby_datanodes parameter is successfully set in distributed mode. To query data on a specified DN, invalid or duplicate nodeoid values are not supported, and nodeoid and nodename cannot be used together.

Syntax

EXECUTE DIRECT ON ( nodename [, ... ] ) query ;
EXECUTE DIRECT ON ( nodeoid [, ... ] ) query; 
EXECUTE DIRECT ON { COORDINATORS | DATANODES | ALL } query;

Parameters

• nodename

  Specifies the node name.

  Value range: an existing node name

• nodeoid

  Node OID.

  Value range: an existing DN OID, which can be obtained from the PGXC_NODE system catalog.

• query

  Specifies the SQL statement to be executed.

• COORDINATORS

  Run the query statement on all CNs.

• DATANODES

  Run the query statement on all DNs.

• ALL

  Run the query statement on all CNs and DNs.

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

-- Query the node distribution status of the current cluster.
gaussdb=# SELECT * FROM pgxc_node;
 node_name   | node_type | node_port |   node_host    | node_port1 |   node_host1   | hostis_primary | nodeis_primary | nodeis_preferred |   node_id   | sctp_port | control_port | sctp_port1 | control_port1 
--------------+-----------+-----------+----------------+------------+----------------+----------------+----------------+------------------+-------------+-----------+--------------+------------+---------------
 cn_5001      | C         |      8050 | 10.180.155.74  |       8050 | 10.180.155.74  | t              | f              | f                |  1120683504 |         0 |            0 |          0 |             0
 cn_5003      | C         |      8050 | 10.180.157.130 |       8050 | 10.180.157.130 | t              | f              | f                |  -125853378 |         0 |            0 |          0 |             0
 dn_6001_6002 | D         |     40050 | 10.180.155.74  |      45050 | 10.146.187.231 | t              | f              | f                |  1644780306 |     40052 |        40052 |      45052 |         45052
 dn_6003_6004 | D         |     40050 | 10.146.187.231 |      45050 | 10.180.157.130 | t              | f              | f                |  -966646068 |     40052 |        40052 |      45052 |         45052
 dn_6005_6006 | D         |     40050 | 10.180.157.130 |      45050 | 10.180.155.74  | t              | f              | f                |   868850011 |     40052 |        40052 |      45052 |         45052
 cn_5002      | C         |      8050 | localhost      |       8050 | localhost      | t              | f              | f                | -1736975100 |         0 |            0 |          0 |             0
(6 rows)

-- Query records in the tpcds.customer_address table on dn_6001_6002.
gaussdb=# EXECUTE DIRECT ON(dn_6001_6002) 'select count(*) from tpcds.customer_address';
 count 
-------
 16922
(1 row)

-- Query records in the tpcds.customer_address table.
gaussdb=# SELECT count(*) FROM tpcds.customer_address;
 count 
-------
 50000
(1 row)