PGXC_BULKLOAD_INFO

Updated on 2024-12-18 GMT+08:00

View PDF

By querying the PGXC_BULKLOAD_INFO view on CNs, you can obtain historical statistics information for interconnection, GDS, COPY, and \COPY business executions after they have completed. This view summarizes the historical execution information of import and export business that have already completed on each node of the current cluster (including the interconnection cluster address, import and export business type, maximum, minimum, and total number of rows and bytes written to disk on DNs, etc.), to obtain historical information on import and export business execution and assist in performance troubleshooting.

This view does not record abnormal interruptions of import and export jobs. The data is directly obtained from the system catalog GS_WLM_SESSION_INFO, and the loader_status field is parsed to obtain import and export service information.

System administrator rights are required to access this view.

**Table 1** PGXC_BULKLOAD_INFO columns
Column	Type	Description
datid	oid	OID of the database the backend is connected to.
dbname	text	Name of the database the backend is connected to.
schemaname	text	Schema name.
nodename	text	Name of the CN where the statement is run.
username	text	Username for connecting to the backend.
application_name	text	Name of the application that is connected to the backend.
client_addr	inet	IP address of the client connected to the backend. If this column is null, it indicates that the client is connected via a Unix socket on the server machine or that it is an internal process, such as autovacuum.
client_hostname	text	Host name of the client, which is obtained by reverse DNS lookup of client_addr. This column is only non-null when log_hostname is enabled and IP connection is used.
client_port	integer	TCP port number used by the client to communicate with the backend. If a Unix socket is used, it is –1.
query_band	text	Job type, which can be set through the GUC parameter query_band and is null string by default.
block_time	bigint	Blocking time before statement execution, including statement parsing and optimization time, in milliseconds.
start_time	timestamp with time zone	Start time of statement execution.
finish_time	timestamp with time zone	End time of statement execution.
status	text	End status of statement execution: finished for normal and aborted for abnormal. The statement status recorded here should be the database server execution status. When the server-side execution is successful and an error occurs when the result set is returned, the statement should be finished.
queryid	bigint	Internal query ID used for statement execution.
query	text	Executed statement.
session_id	text	A session uniquely identified in the database system, in the format of session_start_time.tid.node_name.
address	text	Server address of the interconnection peer cluster. When not empty, it indicates an interconnection service, and the source cluster will additionally obtain the remote cluster port number.
direction	text	Type of import and export service, including gds to file, gds from file, gds to pipe, gds from pipe, copy from, and copy to.
min_done_lines	json	Minimum number of rows of a statement across all DNs.
max_done_lines	json	Maximum number of rows of a statement across all DNs.
total_done_lines	json	Total number of rows of a statement across all DNs.
min_done_bytes	json	Minimum number of bytes of a statement across all DNs.
max_done_bytes	json	Maximum number of bytes of a statement across all DNs.
total_done_bytes	json	Total number of bytes of a statement across all DNs.

NOTE:

Abnormal interruptions of import and export jobs are not recorded in the view.
The implementation mechanism of GDS foreign tables and interconnection foreign tables is different. When querying, GDS records the full amount, while interconnection records the actual amount.
For non-full import and export foreign tables with a limit, due to the special execution plan of limit, the data displayed is collected from one DN, which appears as a maximum value of all and a minimum value of 0.
If the import and export table is a non-partitioned table:
- When the GDS partitioned table is small, if one DN has finished collecting data and the other DNs have not started collecting data, they will not collect data. Therefore, when the data volume of GDS from non-partitioned tables is small, the minimum value may be 0, but it is not 0 when the table data volume is large.
- When exporting non-partitioned tables from the interconnection source cluster, all DNs will be recorded, and only one DN's data will be collected, so the minimum value is 0.
- When exporting replication tables from the interconnection remote cluster, only one DN will be recorded, so it is equivalent to having only one DN, and the minimum and maximum values are the same.
Historical monitoring of import and export is implemented by reusing the historical TopSQL function, which follows the precautions, prerequisites, and operation steps of TopSQL. For details, refer to Historical Top SQL.
Due to the large amount of data recorded by TopSQL, you are advised to query and use it as needed by combining fields such as start_time and finish_time to improve query performance, or to reduce query frequency.

Example

Use the PGXC_BULKLOAD_INFO view to query interconnection import service.

SELECT * FROM PGXC_BULKLOAD_INFO;
 datid |  dbname  |   schemaname   |   nodename   |    username    | application_name | client_addr | client_hostname | client_port | query_band | block_time |          start_time           |          finish_time          |  statu
s  |      queryid      |                                                                                                                                               query                                                          
                                                                                      |               session_id                |      address      |   direction   | min_done_lines | max_done_lines | total_done_lines | min_done_by
tes | max_done_bytes | total_done_bytes 
-------+----------+----------------+--------------+----------------+------------------+-------------+-----------------+-------------+------------+------------+-------------------------------+-------------------------------+-------
---+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------+-----------------------------------------+-------------------+---------------+----------------+----------------+------------------+------------
----+----------------+------------------
 16134 | postgres | "$user",public | coordinator1 | interconn_user | gsql             |             |                 |          -1 |            |          0 | 2023-09-25 10:27:47.184696+08 | 2023-09-25 10:27:48.709665+08 | finish
ed | 72339069014639035 | INSERT INTO interconn_user.lineitem_dest SELECT * FROM interconn_user.ft_lineitem_local;                                                                                                                     
                                                                                      | 1695608841.140482657154648.coordinator1 | 10.90.45.56:63755 | gds from pipe | 19479          | 20971          | 60175            | 3251258    
    | 3500876        | 10038234
 16134 | postgres | "$user",public | coordinator1 | interconn_user | interconnection  | 10.90.45.56 |                 |       47668 |            |          0 | 2023-09-25 10:27:47.256095+08 | 2023-09-25 10:27:48.582366+08 | finish
ed | 72339069014639046 | INSERT INTO pg_temp.ft_lineitem_local_72339069014639035_wo SELECT l_orderkey, l_partkey, l_suppkey, l_linenumber, l_quantity, l_extendedprice, l_discount, l_tax, l_returnflag, l_linestatus, l_shipdate, l_c
ommitdate, l_receiptdate, l_shipinstruct, l_shipmode, l_comment FROM public.lineitem; | 1695608867.140482657156768.coordinator1 | 10.90.45.56       | gds to pipe   | 19476          | 20934          | 60175            | 3249308    
    | 3489789        | 10038234
(2 rows)

Parent topic: System Views

Previous topic: PGXC_BULKLOAD_PROGRESS

Next topic: PGXC_BULKLOAD_STATISTICS