Updated on 2024-07-19 GMT+08:00

Function

get_meta_version(Oid)

Description: Obtains the metadata version information cached in a session on a DN. The input parameter is the OID of the primary table. The output parameter is the version information of all auxiliary tables related to the primary table, including the index, partition, and primary table.

Return type: record

The following table describes return columns.

Column

Type

Description

obj_oid

Oid

Oid of the metadata object

obj_type

char

Metadata type. The options are p (partition), i (index), and r (relation).

obj_parent_oid

Oid

OID of the primary table attached to the metadata object

meta_version

Xid

Version information of the metadata object

Example:

1
2
3
4
5
6
7
SELECT * FROM get_meta_version(16972);
 obj_oid | obj_type | obj_parent_oid | meta_version 
---------+----------+----------------+--------------
   16972 |        r |          16972 |       267910
   16952 |        p |          16972 |       267910
   16958 |        p |          16972 |       267910
(3 row)

get_meta_version()

Description: Obtains all metadata version information cached in a session on a DN. The output is the version information of all primary tables and related auxiliary tables, including indexes, partitions, and primary tables.

Return type: record

The following table describes return columns.

Column

Type

Description

obj_oid

Oid

Oid of the metadata object

obj_type

char

Metadata type. The options are p (partition), i (index), and r (relation)

obj_parent_oid

Oid

OID of the primary table attached to the metadata object

meta_version

Xid

Version information of the metadata object

Example:

1
2
3
4
5
6
7
SELECT * FROM get_meta_version();
 obj_oid | obj_type | obj_parent_oid | meta_version 
---------+----------+----------------+--------------
   16972 |        r |          16972 |       267910
   16952 |        p |          16972 |       267910
   16958 |        p |          16972 |       267910
(3 row)

pgxc_get_meta_version(schemaname, relname)

Description: Obtains the version information of specified metadata cached in sessions on all DNs. The input parameters are the schema name and table name of the primary table. The output parameters are the version information of all auxiliary tables related to the primary table, including the index, partition, and primary table.

Return type: record

The following table describes return columns.

Column

Type

Description

node_name

text

DN name

obj_oid

Oid

Oid of the metadata object

obj_type

char

Metadata type. The options are p (partition), i (index), and r (relation).

obj_parent_oid

Oid

OID of the primary table attached to the metadata object

meta_version

Xid

Version information of the metadata object

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
SELECT * FROM pgxc_get_meta_version('mtc', 't1');
 node_name | obj_oid | obj_type | obj_parent_oid | meta_version 
-----------|---------+----------+----------------+--------------
 datanode1 |   16972 |        r |          16972 |       267910
 datanode1 |   16952 |        p |          16972 |       267910
 datanode1 |   16958 |        p |          16972 |       267910
 datanode2 |   16972 |        r |          16972 |       267910
 datanode2 |   16952 |        p |          16972 |       267910
 datanode2 |   16958 |        p |          16972 |       267910
 datanode3 |   16972 |        r |          16972 |       267910
 datanode3 |   16952 |        p |          16972 |       267910
 datanode3 |   16958 |        p |          16972 |       267910
(9 row)

pgxc_get_meta_version()

Description: Obtains all metadata version information cached in sessions on all DNs. The output is the version information of all auxiliary tables related to the primary table, including the index, partition, and primary table itself.

Return type: record

The following table describes return columns.

Column

Type

Description

node_name

text

DN Name

obj_oid

Oid

Oid of the metadata object

obj_type

char

Metadata type. The options are p (partition), i (index), and r (relation).

obj_parent_oid

Oid

OID of the primary table attached to the metadata object

meta_version

Xid

Version information of the metadata object

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
SELECT * FROM pgxc_get_meta_version();
 node_name | obj_oid | obj_type | obj_parent_oid | meta_version 
-----------|---------+----------+----------------+--------------
 datanode1 |   16972 |        r |          16972 |       267910
 datanode1 |   16952 |        p |          16972 |       267910
 datanode1 |   16958 |        p |          16972 |       267910
 datanode2 |   16972 |        r |          16972 |       267910
 datanode2 |   16952 |        p |          16972 |       267910
 datanode2 |   16958 |        p |          16972 |       267910
 datanode3 |   16972 |        r |          16972 |       267910
 datanode3 |   16952 |        p |          16972 |       267910
 datanode3 |   16958 |        p |          16972 |       267910
(9 row)

clean_dn_metadata(int)

Description: Clears all metadata cached in DN sessions. If this parameter is set to 1, the metadata cached in all sessions is cleared. If this parameter is set to other values, the metadata cached in the current session is cleared.

Return type: int

The following table describes return columns.

Column

Type

Description

cleaned_num

int

Number of deleted metadata caches

Example:

1
2
3
4
5
ELECT * FROM clean_dn_metadata(1);
 cleaned_num  
------------
          2
(1 row)

pgxc_clean_dn_metadata(int)

Description: Clears all metadata cached in all DN sessions. If this parameter is set to 1, the metadata cached in all sessions is cleared. If this parameter is set to other values, the metadata cached in the current session is cleared.

Return type: record

The following table describes return columns.

Column

Type

Description

node_name

text

DN name

cleaned_num

int

Number of deleted metadata caches

Example:

1
2
3
4
5
6
7
SELECT * FROM pgxc_clean_dn_metadata(1);
 node_name | cleaned_num  
-----------|-------------
 datanode1 |           2
 datanode2 |           2
 datanode3 |           2
(3 row)

get_global_meta_cache(int)

Description: Obtains the global cache metadata on a DN. The input parameter is the bucket number, which ranges from 0 to 511.

Return type: record

The following table describes return columns.

Column

Type

Description

bucket_idx

int

ID of the bucket where the metadata object resides

meta_seq

int

Location of the metadata object in the bucket

db_oid

Oid

OID of the database where the metadata object is located

meta_oid

Oid

Oid of the metadata object

meta_part_num

int

Number of partitions contained in a metadata object

meta_idx_num

int

Number of indexes contained in a metadata object

meta_version

text

Version information of the metadata object

Example:

1
2
3
4
5
EXECUTE DIRECT ON (datanode5) 'SELECT * FROM get_global_meta_cache(1)';
 bucket_idx | meta_seq | db_oid | meta_oid | meta_part_num | meta_idx_num |  meta_version 
------------+----------+--------+----------+---------------+--------------+----------------
          1 |        1 |  16852 |    18552 |             0 |            0 | 18552 2394152 , 
(1 row)

get_global_meta_cache()

Description: Obtains the global cache metadata on a DN.

Return type: record

The following table describes return columns.

Column

Type

Description

bucket_idx

int

ID of the bucket where the metadata object resides

meta_seq

int

Location of the metadata object in the bucket

db_oid

Oid

OID of the database where the metadata object is located

meta_oid

Oid

Oid of the metadata object

meta_part_num

int

Number of partitions contained in a metadata object

meta_idx_num

int

Number of indexes contained in a metadata object

meta_version

text

Version information of the metadata object

Example:

1
2
3
4
5
EXECUTE DIRECT ON (datanode5) 'SELECT * FROM get_global_meta_cache()';
 bucket_idx | meta_seq | db_oid | meta_oid | meta_part_num | meta_idx_num |  meta_version 
------------+----------+--------+----------+---------------+--------------+----------------
          1 |        1 |  16852 |    18552 |             0 |            0 | 18552 2394152 , 
(1 row)

pgxc_get_global_meta_cache()

Description: Obtains the global cache metadata on all DNs.

Return type: record

The following table describes return columns.

Column

Type

Description

node_name

text

DN name

bucket_idx

int

ID of the bucket where the metadata object resides

meta_seq

int

Location of the metadata object in the bucket

db_oid

Oid

OID of the database where the metadata object is located

meta_oid

Oid

Oid of the metadata object

meta_part_num

int

Number of partitions contained in a metadata object

meta_idx_num

int

Number of indexes contained in a metadata object

meta_version

text

Version information of the metadata object

Example:

1
2
3
4
5
6
7
SELECT * FROM pgxc_get_global_meta_cache();
 node_name | bucket_idx | meta_seq | db_oid | meta_oid | meta_part_num | meta_idx_num |  meta_version 
-----------+------------+----------+--------+----------+---------------+--------------+----------------
 datanode1 |          1 |        1 |  16852 |    18552 |             0 |            0 | 18552 2394152 , 
 datanode2 |          1 |        1 |  16852 |    18552 |             0 |            0 | 18552 2394152 , 
 datanode3 |          1 |        1 |  16852 |    18552 |             0 |            0 | 18552 2394152 , 
(3 row)

global_meta_cache_reset()

Description: Clears global cached metadata on DNs.

Return type: record

The following table describes return columns.

Column

Type

Description

meta_num

int

Number of metadata records to be deleted

Example:

1
2
3
4
5
ELECT * FROM global_meta_cache_reset();
 meta_num 
----------
        1
(1 row)

pgxc_global_meta_cache_reset()

Description: Clears the global cache metadata on all DNs.

Return type: record

The following table describes return columns.

Column

Type

Description

node_name

text

DN name

meta_num

int

Number of metadata records to be deleted

Example:

1
2
3
4
5
6
7
SELECT * FROM pgxc_global_meta_cache_reset();
 node_name | meta_num  
-----------+----------
 datanode1 |        1
 datanode2 |        1
 datanode3 |        1
(3 row)

pg_obs_file_size(scheme_name.tablename)

Description: Obtains the CU file name and size of a table or partition on OBS. This function is valid only for tables whose colversion is 3.

Return type: record

The function parameter fields are as follows:

Column

Type

Description

scheme_name.tablename

regclass

schema.tablename/tablename/oid of the primary table or OID of the partitioned table. If the OID of the primary table is the same as that of the partition, you are advised to use the table name as the input parameter.

Example:

 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
26
27
-- The input parameter is tablename.
elect pg_obs_file_size('t2_col_part_obs');
      pg_obs_file_size
----------------------------
 (C1_16777266462721.0,1024)
 (C1_16777266429953.0,1024)
 (C1_16777249734657.0,1024)
 (C1_16777249701889.0,1024)
(4 rows)
-- The input parameter is schema.tablename.
select pg_obs_file_size('public.t2_col_part_obs');
      pg_obs_file_size
----------------------------
 (C1_16777266462721.0,1024)
 (C1_16777266429953.0,1024)
 (C1_16777249734657.0,1024)
 (C1_16777249701889.0,1024)
(4 rows)
-- The input parameter is oid.
select pg_obs_file_size(16593);
      pg_obs_file_size
----------------------------
 (C1_16777266462721.0,1024)
 (C1_16777266429953.0,1024)
 (C1_16777249734657.0,1024)
 (C1_16777249701889.0,1024)
(4 rows)

pg_obs_file_size(scheme_name.tablename,partition_name)

Description: Obtains the column-store CU file name and file size of a partitioned table on OBS. This function is valid only for column-store tables whose colversion is 3.

Return type: record

The function parameter fields are as follows:

Column

Type

Description

scheme_name.tablename

regclass

schema.tablename/tablename/oid of the primary table

partition_name

cstring

Partition table name

Example:

1
2
3
4
5
6
7
8
elect pg_obs_file_size('public.t2_col_part_obs','p1');
      pg_obs_file_size
----------------------------
 (C1_16777266462721.0,1024)
 (C1_16777266429953.0,1024)
 (C1_16777249734657.0,1024)
 (C1_16777249701889.0,1024)
(4 rows)

pg_scan_residualfiles()

Description: Scans all residual file records in the database where the current node resides. When it is executed on a CN, it scans the database of the CN and OBS for residual files. When it is executed on a CN, it scans the database of the DN for residual files. This function is a database-level function and applies only to the current database. This function cannot be executed on the standby node.

Return type: record

The following table describes return columns.

Column

Type

Description

pgscrf

text

Local path of the metadata file that records residual file information

Example:
1
2
3
4
5
select * from pg_scan_residualfiles();
                       pgscrf
---------------------------------------------------------
 pg_residualfiles/pgscrf_meta_15842_20230912182912146379
(1 row)

pgxc_scan_residualfiles()

Description: Scans all nodes for the residual files of the current database. This function is a cluster-level function and can be executed only on a CN. It is related to the database where the CN is located. This function cannot be executed on the standby node.

Parameter type: none

Return type: record

The following table describes return columns.

Column

Type

Description

node_name

text

Unified name shared by the active and standby nodes

instance_id

text

Name of the node where the residual file is.

pgscrf

text

Local path of the metadata file that records residual file information

Example:
1
2
3
4
5
6
select * from pgxc_scan_residualfiles();
  node_name   | instance_id  |                         pgscrf
--------------+--------------+---------------------------------------------------------
 datanode1    | datanode1    | pg_residualfiles/pgscrf_meta_15854_20231106095437555205
 coordinator1 | coordinator1 | pg_residualfiles/pgscrf_meta_15854_20231106095438240991
(1 row)

pg_get_scan_residualfiles()

Description: Obtains all residual file records of the current node. This function is an instance-level function and is irrelevant to the current database. It can run on any instance. This function cannot be executed on the standby node.

Return type: record

The following table describes return columns.

Column

Type

Description

handled

bool

Whether the residual file has been handled

dbname

text

Database name

residualfile

text

Path of the residual file

size

int

Size of the residual file. The value of this parameter is 0 for residual files in the OBS path.

inode

int

Index node ID of the residual file in the file system. The index node ID of the residual file on OBS is 0.

atime

time

Last access time of the residual file. This parameter is left blank for residual files in an OBS path.

mtime

time

Last modified time of the residual file. This parameter is left blank for residual files in an OBS path.

ctime

time

Last status change time of the residual file. This parameter is left blank for residual files in an OBS path.

filepath

text

Local path of the metadata file that records residual file information

notes

text

Notes

Example:
1
2
3
4
5
6
7
 select * from pg_get_scan_residualfiles();
 handled |  dbname  |                                                         residualfile                                                         | size | inode | atime | mtime | ctime |                filepath                |
notes
---------+----------+------------------------------------------------------------------------------------------------------------------------------+------+-------+-------+-------+-------+----------------------------------------+-
------
 f       | postgres | /test/obsview/cudesc_check/user1/obs.xxx.com/cu_obs_tbs/tablespace_secondary/15854/19865 |    0 |     0 |       |       |       | pgscrf_meta_15854_20231106095438240991 |
(1 row)

pgxc_get_scan_residualfiles()

Description: Obtains residual file records on all nodes. This function is a cluster-level function and can be executed only on CNs. It is irrelevant to the current database. This function cannot be executed on the standby node.

Return type: record

The following table describes return columns.

Column

Type

Description

node_name

text

Unified name shared by the active and standby nodes

instance_id

text

Name of the node where the residual file is.

handled

bool

Whether the residual file has been handled

dbname

text

Database name

residualfile

text

Path of the residual file

size

int

Size of the residual file. The value of this parameter is 0 for residual files on OBS.

inode

int

Index node ID of the residual file in the file system. The value of this parameter is 0 for residual file ins an OBS path.

atime

time

Last access time of the residual file. This parameter is left blank for residual files in an OBS path.

mtime

time

Last modified time of the residual file. This parameter is left blank for residual files in an OBS path.

ctime

time

Last status change time of the residual file. This parameter is left blank for residual files in an OBS path.

filepath

text

Local path of the metadata file that records residual file information

notes

text

Notes

Example:
1
2
3
4
5
6
7
 select * from pgxc_get_scan_residualfiles();
  node_name   | instance_id  | handled |  dbname  |                                                         residualfile                                                         | size |  inode  |         atime          |
mtime          |         ctime          |                filepath                | notes
--------------+--------------+---------+----------+------------------------------------------------------------------------------------------------------------------------------+------+---------+------------------------+------------------------+------------------------+----------------------------------------+-------
 datanode1    | datanode1    | f       | postgres | base/15854/19863                                                                                                             |    0 | 2939427 | 2023-11-06 09:54:15+08 | 2023-11-06 09:54:15+08 | 2023-11-06 09:54:15+08 | pgscrf_meta_15854_20231106095437555205 |
 coordinator1 | coordinator1 | f       | postgres | /test/obsview/cudesc_check/user1/obs.xxx.com/cu_obs_tbs/tablespace_secondary/15854/19865 |    0 |       0 |                            |             |                        | pgscrf_meta_15854_20231106095438240991 |
(2 rows)

pg_archive_scan_residualfiles()

Description: Archives all residual file records of the current node. This function is an instance-level function and is irrelevant to the current database. It can run on any instance. This function cannot be executed on the standby node.

Return type: record

The following table describes return columns.

Column

Type

Description

archive

text

Archived folder path Residual files in the OBS path are archived in the corresponding OBS database directory.

count

int

Number of files in the archived folder

size

int

Size of the file in the archived folder

Example:
1
2
3
4
5
select * from pg_archive_scan_residualfiles();
                             archive                               |  count | size 
-------------------------------------------------------------------+--------+------
 pg_residualfiles/archive/pgscrf_archive_15842_20230912182934335330|      1 |    0
(1 row)

pgxc_archive_scan_residualfiles()

Description: Archives residual file records on all nodes. This function is a cluster-level function and can be executed only on CNs. It is irrelevant to the current database. This function cannot be executed on the standby node.

Return type: record

The following table describes return columns.

Column

Type

Description

node_name

text

Unified name shared by the active and standby nodes

instance_id

text

Name of the node where the residual file is.

archive

text

Archived folder path Residual files in the OBS path are archived in the corresponding OBS database directory.

count

int

Number of files in the archived folder

size

int

Size of the file in the archived folder

Example:
1
2
3
4
5
6
select * from pgxc_archive_scan_residualfiles();
  node_name   | instance_id  |                           archive                            | count | size
--------------+--------------+--------------------------------------------------------------+-------+------
 datanode1    | datanode1    | pg_residualfiles/archive/pgscrf_archive_20231106103246489550 |     1 |    0
 coordinator1 | coordinator1 | pg_residualfiles/archive/pgscrf_archive_20231106103246592449 |     1 |    0
(2 rows)

pg_rm_scan_residualfiles_archive()

Description: Deletes files from the archived file list on the current instance. This function is an instance-level function and is irrelevant to the current database. It can run on any instance. This function cannot be executed on the standby node.

Return type: record

The following table describes return columns.

Column

Type

Description

count

int

Number of deleted residual files. For residual files in the local path, the number of deleted files is counted. For residual files in the OBS path, the number of deleted table directories is counted.

size

int

Total size of local files in the deleted residual files. The value of this parameter is 0 for all residual files in the OBS path.

Example:
1
2
3
4
5
select * from pg_rm_scan_residualfiles_archive();
 count | size
-------+------
     1 |    0 
(1 row)

pgxc_rm_scan_residualfiles_archive()

Description: Deletes files in the archive directory from all nodes. This function is a cluster-level function and can be executed only on CNs. It is irrelevant to the current database. This function cannot be executed on the standby node.

Return type: record

The following table describes return columns.

Column

Type

Description

node_name

text

Unified name shared by the active and standby nodes

instance_id

text

Name of the node where the residual file is.

count

int

Number of deleted residual files. For residual files in the local path, the number of deleted files is counted. For residual files in the OBS path, the number of deleted table directories is counted.

size

int

Total size of local files in the deleted residual files. The value of this parameter is 0 for all residual files in the OBS path.

Example:
1
2
3
4
5
6
select * from pgxc_rm_scan_residualfiles_archive();
  node_name   | instance_id  | count | size
--------------+--------------+-------+------
 datanode1    | datanode1    |     1 |    0
 coordinator1 | coordinator1 |     1 |    0
(2 rows)

analyze_table(scheme_name, rel_name, sample_rate, random_rate default null, prarallel_degree default null)

Description: Samples data to a temporary table in parallel, performs Full Analyze on the temporary table, and updates statistics.

Return type: record

The function parameter columns are as follows:

Column

Type

Description

scheme_name

name

Scheme name of the primary table.

rel_name

name

Primary table name

sample_rate

float8

Sampling rate percentage. The value ranges from 0 to 100. Calculation: min((10w/total_rows of the table) x 100%)

random_seed

float8

Random seed. If it is not set, the default value 0 is used.

prarallel_degree

int

Concurrency. If it is not set, the default value is 10.

Example:

1
call analyze_table('public','t1_col_obs',10,0,20);

pgxc_clear_disk_cache()

Description: Deletes all disk cache files.

Return type: void

Example:

postgres=# select pgxc_clear_disk_cache();
 pgxc_clear_disk_cache
-----------------------

(1 row)