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:

1

SELECT hstore_light_merge('reason_select');

hstore_full_merge(rel_name text, partitionName text)

Description: This function is used to manually perform full cleanup on HStore tables. The second input parameter is optional and is used to specify a single partition for operations.

Return type: int

• 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.
• The second parameter partitionName is only supported by clusters of version 8.3.0.100 and later. However, these versions do not allow calling this function via call because it lacks reload capability.

Example:

1

SELECT hstore_full_merge('reason_select', 'part1');

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.

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_ui_type: number of records with a type of ui (small CU combination and upsert insertion through update). An ui record represents a single or batch insertion. This parameter is supported only by 8.3.0.100 and later versions.

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:

1
2
3
4
5
6
7

SELECT * FROM pgxc_get_hstore_delta_info('hs_part');
 node_name | part_name | live_tup | n_ui_type | n_i_type | n_d_type | n_x_type | n_u_type | n_m_type | data_size
-----------+-----------+----------+-----------+----------+----------+----------+----------+----------+-----------
 dn_1      | p1        |        2 |         0 |        2 |        0 |        0 |        0 |        0 |      8192
 dn_1      | p2        |        2 |         0 |        2 |        0 |        0 |        0 |        0 |      8192
 dn_1      | p3        |        2 |         0 |        2 |        0 |        0 |        0 |        0 |      8192
(3 rows)

pgxc_get_cstore_dirty_ratio(rel_name text, partition_name)

Description: This function is used to obtain the cu, delta, and cudesc dirty page rates and sizes of the target table on each DN. Only HStore_opt tables are supported.

The partition_name parameter is optional. If the partition name is specified, only the information about the partition is returned. If the partition name is not specified and the table is a primary table, the information about all partitions is returned. It is supported only by clusters of version 9.1.0.100 or later.

Return type: record

Return value:

node_name: DN name

database_name: name of the database where the table is located

rel_name: primary table name

part_name: partition name

cu_dirty_ratio: dirty page rate of CU files

cu_size: CU file size

delta_dirty_ratio: dirty page rate of the delta table

delta_size: delta table size

cudesc_dirty_ratio: dirty page rate of the cudesc table

cudesc_size: cudesc table size

Example:

1
2
3
4
5
6
7
8

SELECT * FROM pgxc_get_cstore_dirty_ratio('hs_opt_part');
 node_name | database_name |      rel_name      | partition_name | cu_dirty_ratio | cu_size | delta_dirty_ratio | delta_size | cudesc_dirty_ratio | cudesc_size
-----------+---------------+--------------------+----------------+----------------+---------+-------------------+------------+--------------------+-------------
 dn_1      | postgres      | public.hs_opt_part | p1             |              0 |       0 |                 0 |      16384 |                  0 |       24576
 dn_1      | postgres      | public.hs_opt_part | p2             |              0 |       0 |                 0 |      16384 |                  0 |       24576
 dn_1      | postgres      | public.hs_opt_part | p3             |              0 |       0 |                 0 |      16384 |                  0 |       24576
 dn_1      | postgres      | public.hs_opt_part | p4             |              0 |       0 |                 0 |      16384 |                  0 |       24576
 dn_1      | postgres      | public.hs_opt_part | other          |              0 | 1105920 |                 0 |     524288 |                  0 |       40960