EXECUTE DIRECT

Function

EXECUTE DIRECT 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, EXECUTE DIRECT can execute only SELECT; it cannot execute DDL, DML, or transaction statements.
• 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. Note: When data is stored in columns, the result of calling the AVG function is not defined. Use the stddev_samp function.
• 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 node.
• 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.

Syntax

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

Parameter Description

• nodename

  Specifies the node name.

  Value range: an existing node name

• 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.
openGauss=# 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.
openGauss=# EXECUTE DIRECT ON(dn_6001_6002) 'select count(*) from tpcds.customer_address';
 count 
-------
 16922
(1 row)

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