Hybrid Data Warehouse Functions¶
hstore_light_merge(rel_name text)¶
Description: This function is used to manually perform lightweight cleanup on HStore tables and holds the level-3 lock of the target table.
Return type: int
Example:
SELECT hstore_light_merge('reason_select');
hstore_full_merge(rel_name text)¶
Description: This function is used to manually perform full cleanup on HStore tables.
Return type: int
Important
This operation forcibly merges all the visible operations of the delta table to the primary table, and then creates an empty delta table. During this period, this operation holds the level-8 lock of the table.
The duration of this operation depends on the amount of data in the delta table. You must enable the HStore clearing thread to ensure unnecessary data in the HStore table is cleared in a timely manner.
Example:
SELECT hstore_full_merge('reason_select');
pgxc_get_small_cu_info(rel_name text, row_count int)¶
Description: Obtains the small CU information of the target table. The second parameter row_count is optional and indicates the small CU threshold. If the number of live tuples in a CU is fewer than the threshold, the CU is considered as a small CU. The default value is 200. This function is supported only by clusters of version 8.2.1.300 or later.
Return type: record
Return value:
node_name: DN name.
part_name: partition name. This column is empty for non-partitioned tables.
zero_cu_count: number of 0 CUs. If all data in a CU is deleted, the CU is called 0 CU.
small_cu_count: number of small CUs. When a CU has live data that is less than the threshold, the CU is called a small CU.
total_cu_count: total number of CUs.
It should be noted that a CU may contain multiple columns.
Example:
SELECT * FROM pgxc_get_small_cu_info('hs');
node_name | part_name | zero_cu_count | small_cu_count | total_cu_count
-----------+-----------+---------------+----------------+----------------
dn_1 | | 1 | 0 | 1
dn_2 | | 1 | 0 | 1
(2 rows)
gs_hstore_compaction(rel_name text, row_count int)¶
Description: Merges small CUs of the target table. The second parameter row_count is optional and indicates the small CU threshold. If the number of live tuples in a CU is fewer than the threshold, the CU is considered as a small CU. The default value is 100. This function is supported only by 8.2.1.300 and later versions.
Return type: int
Return value: numCompactCU, which indicates the number of small CUs to be merged.
Note
A CU may contain multiple columns.
The partition name cannot be input in the function. Currently, a single partition cannot be specified in this function.
Example:
select gs_hstore_compaction('hs', 10);
pgxc_get_hstore_delta_info(rel_name text)¶
Description: This function is used to obtain the delta table information of the target table, including the delta table size and the number of INSERT, DELETE, and UPDATE records. This function is supported only by clusters of version 8.2.1.100 or later.
Return type: record
Return value:
node_name: DN name.
part_name: partition name. This column is set to non-partition table if the table is not a partitioned table.
live_tup: number of live tuples.
n_i_type: number of records whose type is i (insert). An i record indicates one insertion, which can be single insertion or batch insertion.
n_d_type: number of records whose type is d (delete). One d record indicates one deletion, which can be single deletion or batch deletion.
n_x_type: number of records whose type is x (deletions generated by update).
n_u_type: number of records whose type is u (lightweight update).
n_m_type: number of records whose type is m (merge).
data_size: table size.
Example:
SELECT * FROM pgxc_get_hstore_delta_info('hs_part');
node_name | part_name | live_tup | n_i_type | n_d_type | n_x_type | n_u_type | n_m_type | data_size
-----------+-----------+----------+----------+----------+----------+----------+----------+-----------
dn_1 | p1 | 2 | 2 | 0 | 0 | 0 | 0 | 8192
dn_1 | p2 | 2 | 2 | 0 | 0 | 0 | 0 | 8192
dn_1 | p3 | 2 | 2 | 0 | 0 | 0 | 0 | 8192
(3 rows)