Help Center> Data Warehouse Service (DWS)> 3.0 Developer Guide> Function

Updated on 2023-11-30 GMT+08:00

View PDF

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

-- 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:

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.

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:

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.

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:

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.

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:

 select * from pg_get_scan_residualfiles();
 handled |  dbname  |                                                         residualfile                                                         | size | inode | atime | mtime | ctime |                filepath                |
notes
---------+----------+------------------------------------------------------------------------------------------------------------------------------+------+-------+-------+-------+-------+----------------------------------------+-
------
 f       | postgres | /gaoruoyue-test/obsview/cudesc_check/n30048935/obs.cn-north-7.ulanqab.huawei.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.

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:

 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 | /gaoruoyue-test/obsview/cudesc_check/n30048935/obs.cn-north-7.ulanqab.huawei.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.

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:

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.

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:

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.

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:

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.

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:

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)

Previous topic: ALTER TABLE

Next topic: System Catalogs

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot