Other Functions
pgxc_pool_check()
Description: Checks whether the connection data buffered in the pool is consistent with pgxc_node. This parameter has been deprecated in clusters of version 8.3.0 or later.
Return type: boolean
pgxc_pool_reload()
Description: Updates the connection information buffered in the pool.
Return type: boolean
pg_pool_validate(clear boolean, co_node_name cstring)
Description: Clears invalid backend threads on a CN. (These backend threads hold invalid pooler connections to standby DNs.)
Return type: record
pg_nodes_memory()
Description: queries the memory usage of all nodes.
Return type: record
table_skewness(text)
Description: queries the percentage of table data among all nodes.
Parameter: Indicates that the type of the name of the to-be-queried table is text.
Return type: record
table_skewness(table_name text, column_name text[, row_num text])
Description: Queries the proportion of column data distributed on each node based on the hash distribution rule. The results are sorted based on the data volumes of the nodes.
Parameters: table_name indicates a table name, column_name indicates a column name, and row_num indicates that all data in the current column is returned. The default value is 0. A value other than 0 indicates the number of data records whose statistics are sampled. (Records are randomly sampled.)
Return type: record
Example:
Distribute data by hash based on the a column in the tx table. Seven records are distributed on DN 1, two records on DN 2, and one record on DN 0.
1 2 3 4 5 6 7 | SELECT * FROM table_skewness('tx','a'); seqnum | num | ratio --------+-----+---------- 1 | 7 | 70.000% 2 | 2 | 20.000% 0 | 1 | 10.000% (3 rows) |
table_data_skewness(data_row record, locatorType "char")
Description: Calculates the bucket distribution index for the records concatenated using the columns in a specified table.
Parameters: data_row indicates the record concatenated using columns in the specified table. locatorType indicates the distribution rule. You are advised to set locatorType to H, indicating hash distribution.
Return type: smallint
Example:
Calculates the bucket distribution index based on the hash distribution rule for the records combined concatenated using column a in the tx table.
1 2 3 4 5 6 7 8 9 | select a, table_data_skewness(row(a), 'H') from tx; a | table_data_skewness ---+--------------------- 3 | 0 6 | 2 7 | 2 4 | 1 5 | 1 (5 rows) |
table_distribution(schemaname text, tablename text)
Description: queries the storage space occupied by a specified table on each node.
Parameter: Indicates that the types of the schema name and table name for the table to be queried are both text.
Return type: record
- To use this function to query the storage distribution information of a specified table, you must have the administrator permission or the gs_role_read_all_stats permission of the preset role.
- The performance of table_distribution is better than that of table_skewness. Especially in a large cluster with a large amount of data, table_distribution is recommended.
- To view storage distribution more clearly when using table_distribution, use dnsize/(SUM(dnsize) OVER ()) to calculate the exact proportion of space used by each DN.
table_distribution(regclass)
Description: queries the storage space occupied by a specified table on each node.
Parameter: indicates the name or OID of the table to be queried. The table name can be defined by the schema name. Parameter type: regclass
Return type: record
- To use this function to query the storage distribution information of a specified table, you must have the administrator permission or the gs_role_read_all_stats permission of the preset role.
- The performance of table_distribution is better than that of table_skewness. Especially in a large cluster with a large amount of data, table_distribution is recommended.
- To view storage distribution more clearly when using table_distribution, use dnsize/(SUM(dnsize) OVER ()) to calculate the exact proportion of space used by each DN.
table_distribution()
Description: queries the storage distribution of all tables in the current database.
Return type: record
- This function involves the query for information about all tables in the database. To execute this function, you must have the administrator rights or rights of the preset role gs_role_read_all_stats.
- Based on the table_distribution() function, DWS provides the PGXC_GET_TABLE_SKEWNESS view as an alternative way to query for data skew. You are advised to use this view when the number of tables in the database is less than 10000.
gs_table_distribution(schemaname text, tablename text)
Description: queries the storage space occupied by a specified table on each node.
Return type: record
| Name | Type | Description |
|---|---|---|
| schemaname | name | Schema name |
| tablename | name | Table name |
| relkind | character | Type.
|
| relpersistence | character | Type.
|
| nodename | name | Node name |
| dnsize | bigint | Storage space of the table on the node, in bytes. |
| sessionid | bigint | For a global temporary table, the session ID of each session is displayed. |
- To use this function to query the storage distribution information of a specified table, you must have the administrator permission or the gs_role_read_all_stats permission of the preset role.
- This function is based on the physical file storage space records in the PG_RELFILENODE_SIZE system catalog. Ensure that the GUC parameters use_workload_manager and enable_perm_space are enabled.
- The gs_table_distribution function performs slower than the table_distribution function when querying a single table. However, when querying the entire database, the gs_table_distribution function shows significantly better performance. For large clusters with a substantial amount of data, you are advised to use the gs_table_distribution function to query all tables in the database.
- For a global temporary table, the distribution of each session in the storage space is displayed. The sessionid column is used to distinguish sessions.
gs_table_distribution()
Description: quickly queries the storage distribution of all tables in the current database.
Return type: record
| Name | Type | Description |
|---|---|---|
| schemaname | name | Schema name |
| tablename | name | Table name |
| relkind | character | Type. Options:
|
| relpersistence | character | Type.
|
| nodename | name | Node name |
| dnsize | bigint | Storage space of the table on the node, in bytes. |
| sessionid | bigint | For a global temporary table, the session ID of each session is displayed. |
- To use this function to query the storage distribution information of a specified table, you must have the administrator permission or the gs_role_read_all_stats permission of the preset role.
- This function is based on the physical file storage space records in the PG_RELFILENODE_SIZE system catalog. Ensure that the GUC parameters use_workload_manager and enable_perm_space are enabled.
- The gs_table_distribution function performs slower than the table_distribution function when querying a single table. However, when querying the entire database, the gs_table_distribution function shows significantly better performance. For large clusters with a substantial amount of data, it is recommended to use the gs_table_distribution function to query all tables in the database.
- Based on the gs_table_distribution() function, DWS 8.2.1 and later versions provide the PGXC_WLM_TABLE_DISTRIBUTION_SKEWNESS view for data skew query. You are advised to use this view when the number of tables in the database is small (less than 10,000).
check_foreign_key_constraint(schema.table)
Description: Checks whether data in a foreign key table meets foreign key constraints. This is supported only in clusters of version 9.1.0.200 or later.
Return type: text
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | set info_constraint_options = 'foreign_key'; CREATE TABLE ptt1 (a int,b int,e varchar(50),f varchar(50),PRIMARY KEY (a,b)) DISTRIBUTE BY HASH(a,b); CREATE TABLE tt2 (a int , b int, c int, d int) DISTRIBUTE BY HASH(a,b); select check_foreign_key_constraint('table_constraints.tt2'); check_foreign_key_constraint ----------------------------------------- tt2 table has no foreign key constraint ALTER TABLE tt2 ADD CONSTRAINT fk_tt1_a FOREIGN KEY (a,b) REFERENCES ptt1(a,b); select check_foreign_key_constraint('table_constraints.tt2'); check_foreign_key_constraint ------------------------------------------- tt2 satisfied all Foreign key constraints |
plan_seed()
Description: Obtains the seed value of the previous query statement (internal use).
Return type: integer
pg_stat_get_env()
Description: Obtains the environment variable information about the current node.
Return type: record
pg_stat_get_thread()
Description: Provides information about the status of all threads under the current node.
Return type: record
pgxc_get_os_threads()
Description: Provides information about the status of threads under all normal nodes in a cluster.
Return type: record
pg_stat_get_sql_count()
Description: Provides statistics on the number of SELECT/UPDATE/INSERT/DELETE/MERGE INTO statements executed by all users on the current node, response time, and the number of DDL, DML, and DCL statements.
Return type: record
pgxc_get_sql_count()
Description: Provides statistics on the number of SELECT/UPDATE/INSERT/DELETE/MERGE INTO statements executed by all users on all nodes of the current cluster, response time, and the number of DDL, DML, and DCL statements.
Return type: record
pgxc_get_workload_sql_count()
Description: Provides statistics on the number of SELECT/UPDATE/INSERT/DELETE statements executed in all workload Cgroup on all CNs of the current cluster and the number of DDL, DML, and DCL statements.
Return type: record
pgxc_get_workload_sql_elapse_time()
Description: Provides statistics on response time of SELECT/UPDATE/INSERT/DELETE statements executed in all workload Cgroup on all CNs of the current cluster.
Return type: record
get_instr_unique_sql()
Description: Provides information about Unique SQL statistics collected on the current node. If the node is a CN, the system returns the complete information about the Unique SQL statistics collected on the CN. That is, the system collects and summarizes the information about the Unique SQL statistics on other CNs and DNs. If the node is a DN, the Unique SQL statistics on the DN is returned. For details, see the GS_INSTR_UNIQUE_SQL view.
Return type: record
reset_instr_unique_sql(cstring, cstring, INT8)
Description: Clears collected Unique SQL statistics. The input parameters are described as follows:
- GLOBAL/LOCAL: Data is cleared from all nodes or the current node.
- ALL/BY_USERID/BY_CNID/BY_GUC: ALL indicates that all data is cleared. BY_USERID/BY_CNID indicates that data is cleared by USERID or CNID. BY_GUC indicates that the clearance operation is caused by the decrease of the value of the GUC parameter instr_unique_sql_count.
- The third parameter corresponds to the second parameter. The parameter is invalid for ALL/BY_GUC.
Return type: bool
pgxc_get_instr_unique_sql()
Description: Provides complete information about Unique SQL statistics collected on all CNs in a cluster. This function can be executed only on CNs.
Return type: record
get_instr_unique_sql_remote_cns()
Description: Provides complete information about Unique SQL statements collected on all CNs in the cluster, except the CN on which the function is being executed. This function can be executed only on CNs.
Return type: record
pgxc_get_node_env()
Description: Provides the environment variable information about all nodes in a cluster.
Return type: record
gs_switch_relfilenode()
Description: Exchanges meta information of two tables or partitions. (This is only used for the redistribution tool. An error message is displayed when the function is directly used by users).
Return type: integer
copy_error_log_create()
Description: Creates the error table (public.pgxc_copy_error_log) required for creating the COPY FROM error tolerance mechanism.
Return type: boolean
- This function attempts to create the public.pgxc_copy_error_log table. For details about the table, see Table 3.
- Create the B-tree index on the relname column and execute REVOKE ALL on public.pgxc_copy_error_log FROM public to manage permissions for the error table (the permissions are the same as those of the COPY statement).
- public.pgxc_copy_error_log is a row-store table. Therefore, this function can be executed and COPY FROM error tolerance is available only when row-store tables can be created in the cluster. After the GUC parameter enable_hadoop_env is enabled, row-based tables cannot be created in the cluster. The default value is off.
- Same as the error table and the COPY statement, the function requires sysadmin or higher permissions.
- If the public.pgxc_copy_error_log table or the copy_error_log_relname_idx index already exists before the function creates it, the function will report an error and roll back.
| Column | Type | Description |
|---|---|---|
| relname | varchar | Table name in the form of Schema name.Table name |
| begintime | timestamp with time zone | Time when a data format error was reported |
| filename | varchar | Name of the source data file where a data format error occurs |
| rownum | bigint | Number of the row where a data format error occurs in a source data file |
| rawrecord | text | Raw record of a data format error in the source data file To prevent a field from being too long, the length of the field cannot exceed 1024 bytes. |
| detail | text | Error details |
| columnname | varchar | Name of the column whose data format is incorrect in the data source file. This function is supported only in clusters of version 8.2.1.100 or later. |
| errcode | varchar | Error code corresponding to the error information. The sqlstate error code is used. This function is supported only in clusters of version 8.2.1.100 or later. |
| queryid | bigint | ID of the SQL statement for executing the Copy statement. It uniquely identifies an SQL statement. This function is supported only in clusters of version 8.2.1.100 or later. |
pg_stat_get_status(tid, num_node_display)
Description: Queries for the blocking and waiting status of the backend threads and auxiliary threads in the current instance. For details about the returned results, see the PG_THREAD_WAIT_STATUS view. The input parameters are described as follows:
- tid: thread ID, which is of the bigint type. If this parameter is null, the waiting statuses of all backend threads and auxiliary threads are returned. Otherwise, only the waiting statuses of threads with the specified IDs are returned.
- num_node_display: integer type. Specifies the maximum number of waiting nodes displayed in the wait_status column for records whose waiting status is wait node.
- If this parameter is left empty or set to a value less than or equal to 0, only one waiting node is displayed.
- If the value is greater than 20, the maximum number of nodes can be displayed is 20.
- If the value is greater than 0 and less than or equal to 20, the smaller value between num_node_display and the actual number of waiting nodes is displayed. Use the SELECT * from pg_stat_get_status(NULL, 10) query for example. If the number of waiting nodes is greater than 10, the names of only 10 nodes are displayed randomly. If the number of waiting nodes is less than or equal to 10, the names of all waiting nodes are displayed. If the number of waiting nodes is greater than the number of displayed nodes, the displayed node names are randomly selected.
Return type: record
pgxc_get_thread_wait_status(num_node_display)
Description: Queries for the call hierarchy between threads generated by all SQL statements on each node in a cluster, as well as the block waiting status of each thread. For details about the returned results, see the PGXC_THREAD_WAIT_STATUS view. The type and meaning of the input parameter num_node_display are the same as those of the pg_stat_get_status function.
Return type: record
pgxc_os_run_info()
Description: Obtains the running status of the operating system on each node in a cluster.
Return type: record
| Column | Type | Description |
|---|---|---|
| id | integer | ID. |
| name | text | Name of the operating system status. |
| value | numeric | Value of the operating system status. |
| comments | text | Comment on the operating system status. |
| cumulative | boolean | Whether the value of the operating system status is cumulative. |
get_instr_wait_event()
Description: obtains the waiting status and events of the current instance. If the GUC parameter enable_track_wait_event is off, this function returns 0.
Return type: record
| Column | Type | Description |
|---|---|---|
| nodename | name | Node name. |
| type | text | Event type, which can be STATUS, LOCK_EVENT, LWLOCK_EVENT, or IO_EVENT. |
| event | text | Event name. |
| wait | bigint | Number of times the event occurred. This column and the following columns are cumulative values during process execution. |
| failed_wait | bigint | Number of wait failures. In the current version, this column is only used for lock timeouts or failures such as LOCK and LWLOCK. |
| total_wait_time | bigint | Total duration of the event. |
| avg_wait_time | bigint | Average duration of the event. |
| max_wait_time | bigint | Maximum wait time for the event. |
| min_wait_time | bigint | Minimum wait time for the event. |
pgxc_wait_events()
Description: Queries statistical information about various wait states and events on each node in the cluster. If the GUC parameter enable_track_wait_event is off, this function returns 0.
Return type: record
| Column | Type | Description |
|---|---|---|
| nodename | name | Node name. |
| type | text | Event type, which can be STATUS, LOCK_EVENT, LWLOCK_EVENT, or IO_EVENT. |
| event | text | Event name. |
| wait | bigint | Number of times the event occurred. This column and the following columns are cumulative values during process execution. |
| failed_wait | bigint | Number of wait failures. In the current version, this column is only used for lock timeouts or failures such as LOCK and LWLOCK. |
| total_wait_time | bigint | Total duration of the event. |
| avg_wait_time | bigint | Average duration of the event. |
| max_wait_time | bigint | Maximum wait time for the event. |
| min_wait_time | bigint | Minimum wait time for the event. |
pgxc_stat_bgwriter()
Description: Queries statistical information about the background writer process activity on each node in the cluster.
Return type: record
| Column | Type | Description |
|---|---|---|
| checkpoints_timed | bigint | Number of scheduled checkpoints performed. |
| checkpoints_req | bigint | Number of requested checkpoints performed. |
| checkpoint_write_time | double precision | Time spent writing files to disk during checkpoints, in milliseconds. |
| checkpoint_sync_time | double precision | Time spent synchronizing data to disk during checkpoints, in milliseconds. |
| buffers_checkpoint | bigint | Number of buffers written during checkpoints. |
| buffers_clean | bigint | Number of buffers written by the background writer. |
| maxwritten_clean | bigint | Number of times the background writer stopped a cleaning scan due to writing too many buffers. |
| buffers_backend | bigint | Number of buffers written directly by backends. |
| buffers_backend_fsync | bigint | Number of times a backend needed to perform an fsync. |
| buffers_alloc | bigint | Number of buffers allocated. |
| stats_reset | timestamp with time zone | Time at which these statistics were last reset. |
pgxc_stat_replication()
Description: Queries the log synchronization status information on each node in the cluster.
Return type: record
| Column | Type | Description |
|---|---|---|
| pid | bigint | PID of the thread. |
| usesysid | oid | User system ID. |
| usename | name | User name. |
| application_name | text | Application name. |
| client_addr | inet | Client address. |
| client_hostname | text | Client name. |
| client_port | integer | Client port number. |
| backend_start | timestamp with time zone | Application startup time. |
| state | text | Log replication state (catch-up state or consistent streaming state). |
| sender_sent_location | text | Log location sent by the sender. |
| receiver_write_location | text | Log location written by the receiver. |
| receiver_flush_location | text | Log location flushed by the receiver. |
| receiver_replay_location | text | Log location replayed by the receiver. |
| sync_priority | integer | Priority of synchronous replication (0 indicates asynchronous). |
| sync_state | text | Synchronization state (asynchronous replication, synchronous replication, or potential synchronous). |
pgxc_replication_slots()
Description: Queries the replication status information on each DN in the cluster.
Return type: record
| Column | Type | Description |
|---|---|---|
| slot_name | text | Name of the replication node. |
| plugin | name | Output plug-in name corresponding to the logical replication slot. |
| slot_type | text | Type of the replication node. |
| datoid | oid | Database OID of the replication node. |
| database | name | Database name of the replication node. |
| active | boolean | Whether the replication node is active. |
| xmin | xid | Transaction ID of the replication node. |
| catalog_xmin | text | Earliest decoded transaction ID corresponding to the logical replication slot. |
| restart_lsn | text | Xlog file information of the replication node. |
| dummy_standby | boolean | Whether the replication node is a dummy standby. |
pgxc_settings()
Description: Queries information about runtime parameters on each node in the cluster.
Return type: record
| Column | Type | Description |
|---|---|---|
| name | text | Parameter name. |
| setting | text | Current value of the parameter. |
| unit | text | Implicit structure of the parameter. |
| category | text | Logical group of the parameter. |
| short_desc | text | Brief description of the parameter. |
| extra_desc | text | Detailed description of the parameter. |
| context | text | Context for setting the parameter value, including internal, postmaster, sighup, backend, superuser, and user. |
| vartype | text | Parameter type, including bool, enum, integer, real, and string. |
| source | text | Method by which the parameter was assigned. |
| min_val | text | Minimum value of the parameter. If the parameter type is not numeric, this column value is null. |
| max_val | text | Maximum value of the parameter. If the parameter type is not numeric, this column value is null. |
| enumvals | text[] | Valid values for an enum type parameter. If the parameter type is not enum, this column value is null. |
| boot_val | text | Default value of the parameter at database startup. |
| reset_val | text | Default value of the parameter at database reset. |
| sourcefile | text | Configuration file that sets the parameter value. If the parameter is not assigned via a configuration file, this column value is null. |
| sourceline | integer | Line number in the configuration file that sets the parameter value. If the parameter is not assigned via a configuration file, this column value is null. |
| sync_to_stream | boolean | Whether to synchronize parameter changes in the stream thread. This column is only supported in clusters of version 9.1.1.300 or later. |
pgxc_instance_time()
Description: Queries the running time statistics of each node in the cluster and the time consumed by each execution phase.
Return type: record
| Column | Type | Description |
|---|---|---|
| stat_id | integer | Type ID. |
| stat_name | text | Name of the running time type. |
| value | bigint | Running time value. |
pg_stat_get_redo_stat()
Description: Queries the XLOG redo statistics on the current node.
Return type: record
| Column | Type | Description |
|---|---|---|
| phywrts | bigint | Number of physical writes. |
| phyblkwrt | bigint | Number of physical write blocks. |
| writetim | bigint | Time consumed by physical writes. |
| avgiotim | bigint | Average write time per operation. |
| lstiotim | bigint | Time of the last write. |
| miniotim | bigint | Minimum write time. |
| maxiowtm | bigint | Maximum write time. |
pgxc_redo_stat()
Description: Queries the XLOG redo statistics on each node in the cluster.
Return type: record
| Column | Type | Description |
|---|---|---|
| phywrts | bigint | Number of physical writes. |
| phyblkwrt | bigint | Number of physical write blocks. |
| writetim | bigint | Time consumed by physical writes. |
| avgiotim | bigint | Average write time per operation. |
| lstiotim | bigint | Time of the last write. |
| miniotim | bigint | Minimum write time. |
| maxiowtm | bigint | Maximum write time. |
get_local_rel_iostat()
Description: Obtains the disk I/O statistics of the current instance.
Return type: record
| Column | Type | Description |
|---|---|---|
| phyrds | bigint | Number of disk reads. |
| phywrts | bigint | Number of disk writes. |
| phyblkrd | bigint | Number of disk pages read. |
| phyblkwrt | bigint | Number of disk pages written. |
pgxc_rel_iostat()
Description: Queries the disk read and write statistics on each node in the cluster.
Return type: record
| Column | Type | Description |
|---|---|---|
| phyrds | bigint | Number of disk reads. |
| phywrts | bigint | Number of disk writes. |
| phyblkrd | bigint | Number of disk pages read. |
| phyblkwrt | bigint | Number of disk pages written. |
get_node_stat_reset_time()
Description: Obtains the time when statistics of the current instance were reset.
Return type: timestamptz
pgxc_node_stat_reset_time()
Description: Queries the time when the statistics on each node in the cluster were last reset.
Return type: record
- The instance is restarted or a cluster switchover occurs.
- The database is dropped.
- A reset operation is performed. For example, the statistics counter in the database is reset using the pgstat_recv_resetcounter function or the Unique SQL statements are cleared using the reset_instr_unique_sql function.
If any of the preceding events occurs, DWS will record the time when the statistics are reset. You can query the time using the get_node_stat_reset_time function.
pgxc_parallel_query(text, text)
Description: Runs a specified SQL query statement on a data instance of a specified type and returns the query result to the current CN. This function is supported in version 8.1.2 or later.
The first parameter text specifies on which instances the SQL statement is executed. Currently supported valid input values are dn, datanode, cn, coordinator, and all. Any other value will cause the function to report an error. dn and datanode indicate execution on all DNs. cn and coordinator indicate execution on all CNs. all indicates execution on all CNs and all DNs.
The second parameter text specifies the SQL statement to be sent to remote nodes for execution. The function internally validates the objects queried in the SQL statement. User tables, distributed tables, and custom multi-result-set functions are not supported.
Return type: record
- This function is provided solely for the convenience of developers in efficiently collecting information or status views of instance execution within the cluster. It is not recommended for direct use by users.
- This function is a multi-result-set function and returns data of type record. Therefore, an AS statement must be appended after the function call to explicitly specify the output column names and data types, as shown below:
SELECT * FROM pgxc_parallel_query('all', 'select node_name, db_name, thread_name, query_id, tid, lwtid, ptid, tlevel, smpid, wait_status, wait_event from pg_thread_wait_status') AS (node_name text, db_name text, thread_name text, query_id bigint, tid bigint, lwtid integer , ptid integer, tlevel integer , smpid integer, wait_status text, wait_event text); - The data types of the output results of the SQL statement specified in the second parameter must match the data types specified after the AS clause. Otherwise, an error may occur during execution due to type mismatch.
- The SQL statement specified in the second parameter must not trigger cross-node query actions. Otherwise, an execution error will be triggered.
- The SQL statement specified in the second parameter can only be one of SELECT, UPDATE, DELETE, or INSERT, and is subject to the following restrictions:
- Statements with a RETURNING clause are not supported.
- The user calling the function must have the corresponding operation permissions on the objects in the SQL statement.
- For INSERT statements, INSERT OVERWRITE, UPSERT, and INSERT INTO are not supported.
- For UPDATE/DELETE/INSERT statements, only the initialization user (in in-place upgrade mode) or the administrator user (in redistribution mode) is allowed to execute them. The number of records modified by the statement on each instance must be the same. Otherwise, an error will be reported. The function outputs a single-column bigint value, which indicates the number of records modified by the statement on each instance.
SELECT * FROM pgxc_parallel_query('cn', 'UPDATE pg_partition SET relpages = 0') AS (updated bigint);
generate_wdr_report(begin_snap_id bigint, end_snap_id bigint, report_type cstring, report_scope cstring, node_name cstring)
Description: Creates a load analysis report.
The input parameters are described as follows:
- begin_snap_id and end_snap_id: IDs of the start and end snapshots, respectively. The IDs are of the bigint type. The value of begin_snap_id must be less than that of end_snap_id, and the time for the start and end snapshots cannot overlap. You can check whether the snapshot time overlaps by querying select s1.end_ts < s2.start_ts from (select * from dbms_om.snapshot where snapshot_id=end_snap_id) as s2; in the dbms_om.snapshot table. If true is returned, the snapshot time does not overlap. Otherwise, the snapshot time overlaps.
- report_type: report type. The value is a cstring and can be summary, detail, or all.
- report_scope: report scope. The value is a cstring and can be cluster or node.
- node_name: node name. The value is a cstring. If report_scope is node, the value of this parameter must be pg_catalog, which indicates the CN or DN name in the node_name column of the pgxc_node table.
- Only the database administrator SYSADMIN can execute this function.
- This function can be executed only on CNs. If it is executed on DNs, the following message will be returned: "WDR report can only be created on coordinator."
- If the report is created successfully, message "Report %s has been generated" will be returned.
- The statistics cannot be reset between the time the start snapshot is taken and the time the end snapshot is taken. Otherwise, error message "Instance reset time is different" will be displayed. For details about the events that cause a statistics reset, see the pgxc_node_stat_reset_time function.
wdr_xdb_query(db_name text, snapshot_id bigint, view_name text)
Description: Queries a specified view in a specified database. The query results of some views vary depending on databases. For example, the global_table_stat view is used to query the statistics of a table. The results of querying this view vary because tables in different databases are different. The wdr_xdb_query function can access the database specified by db_name in the current connection and query the view specified by view_name in the database. The input parameters are described as follows:
- db_name: specifies the name of a database. The value is of the text type.
- snapshot_id: specifies the snapshot ID. The value is of the bigint type. For details, see "Performance View Snapshot".
- view_name: specifies the name of a view. The value is of the text type. The view name must be in the following whitelist:
- global_table_stat
- global_table_change_stat
- global_column_table_io_stat
- global_row_table_io_stat
The return value type is record. The first column is snapshot_id bigint, and the second column is db_name text. The names, types, and sequences of other columns are the same as those of the views specified by view_name.
Example:
1 2
select snapshot_id, db_name, schemaname, relname, distribute_mode, seq_scan ,seq_tuple_read ,index_scan ,index_tuple_read ,tuple_inserted ,tuple_updated ,tuple_deleted ,tuple_hot_updated ,live_tuples ,dead_tuples from wdr_xdb_query('postgres'::text, 1, 'global_table_stat'::text) as i(snapshot_id bigint, db_name text, schemaname name, relname name, distribute_mode char, seq_scan bigint, seq_tuple_read bigint, index_scan bigint, index_tuple_read bigint, tuple_inserted bigint, tuple_updated bigint, tuple_deleted bigint, tuple_hot_updated bigint, live_tuples bigint, dead_tuples bigint);
- This function is supported only in 8.1.2 or later.
- Only the database administrator SYSADMIN can execute this function.
- This function can be used to query only the views in the whitelist. If you use this function to query other views, the error message Input view name is invalid. will be displayed.
vac_fileclear_relation(oid)
Description: Forcibly clears VACUUM rewritten files in a specified column-store table to reclaim space.
Parameter: OID of a column-store table.
Return type: integer
- Before using this function, set colvacuum_threshold_scale_factor and ensure that the files are cleared and space reclaimed only after the VACUUM process has rewritten the files of the specified column-store table.
- This function exclusively locks a specified column-store table.
- This function cannot be used after DDL delay is enabled.
vac_fileclear_all_relation()
Description: Forcibly clears VACUUM rewritten files in all specified column-store tables to reclaim space.
Return type: record
This function cannot be used after DDL delay is enabled.
get_col_file_info(table_name)
Description: Queries the number of empty CU files and the total number of CU files in a specified column-store table. This function is supported only in clusters of version 8.2.0 or later.
Parameter: Name of a column-store table.
Return type: int
Fields in the returned value:
- total_file_num int: total number of CU files. The value ranges from -1 to INT_MAX. -1 indicates a failure, which could be caused by unsupported table types. The values in the range 0 to INT_MAX indicates the total number of files.
- empty_file_num int: number of empty CU files. The value ranges from -1 to INT_MAX. -1 indicates a failure, which could be caused by unsupported table types. The values in the range 0 to INT_MAX indicates the total number of empty files.
Example:
1 2 3 4 5 | call get_col_file_info('t4'); total_file_num | empty_file_num ----------------+---------------- 10 | 7 (1 row) |
get_all_col_file_info()
Description: Queries the number of empty CU files and the total number of CU files in all column-store tables. This function is supported only in clusters of version 8.2.0 or later.
Return type: record
Fields in the returned value:
- space_name text: schema to which the column-store tables belong
- table_name text: name of a column-store table
- total_file_num int: total number of CU files. The value ranges from -1 to INT_MAX. -1 indicates a failure, which could be caused by unsupported table types. The values in the range 0 to INT_MAX indicates the total number of files.
- empty_file_num int: number of empty CU files. The value ranges from -1 to INT_MAX. -1 indicates a failure, which could be caused by unsupported table types. The values in the range 0 to INT_MAX indicates the total number of empty files.
Example:
1 2 3 4 5 6 7 | call get_all_col_file_info(); space_name | table_name | total_file_num | empty_file_num ------------+------------+----------------+---------------- public | t4 | 10 | 7 public | t2 | 1 | 1 public | t1 | 3 | 0 (3 rows) |
pg_get_bucket_epoch(relid)
Description: Queries the epoch value of the v3 table on the current node. To print the epoch values of all subpartitions in a partitioned table, you must provide the OID of the parent table. This function can be executed only in VW write scenarios. This function is supported only in clusters of version 9.1.0.200 or later.
Return type: record
Fields in the returned value:
- relfilenode oid: column-store relfilenode of a non-partitioned table. For a partitioned table, the value is 0.
- partfilenode oid: relfilenode of a column-store partitioned table. For a non-partitioned table, the value is 0.
- epoch xid: epoch value corresponding to the table on the current node.
Example:
1 2 3 4 5 | select * from pg_get_bucket_epoch(2147483732); relfilenode | partfilenode | epoch -------------+--------------+-------- 2137383742 | 0 | 1000001 (1 row) |
pg_get_bucket_epoch(tablespaceid, databaseid, relfilenode)
Description: Queries the epoch value of the v3 table on the current node. Enter the OID of the tablespace where the v3 table is located, the OID of the database, and the relfilenode corresponding to the table. This function can be executed in both VW write and read scenarios. This function is supported only in clusters of version 9.1.0.200 or later.
Return type: xid
Fields in the returned value:
epoch xid: epoch value corresponding to the table on the current node.
Example:
1 2 3 4 5 | select * from pg_get_bucket_epoch(2147483729, 16792, 2147483732); epoch ------- 1000001 (1 row) |
pgxc_get_bucket_epoch(tablespaceid, databaseid, relfilenode)
Description: Queries the epoch values of the v3 table on all DNs. Enter the OID of the tablespace where the v3 table is located, the OID of the database, and the relfilenode corresponding to the table. This function can be executed in both VW write and read scenarios. This function is supported only in clusters of version 9.1.0.200 or later.
Return type: record
Fields in the returned value:
- nodename text: node name
- epoch xid: epoch value corresponding to the table on the current node.
Example:
1 2 3 4 5 6 7 | select * from pgxc_get_bucket_epoch(2147483729, 16792, 2147483732); nodename | epoch -----------+--------- datanode3 | 1000001 datanode1 | 0 datanode2 | 0 (1 row) |
pgxc_check_bucket_info(tablename regclass, stoponerror)
Description: Checks whether the CUID and file ID mapping information on all DNs where a specified v3 table resides is correct. This function must be executed on a CN. This function is supported only in clusters of version 9.1.1.200 or later.
Parameters:
tablename: table name.
- When set to true, if an error is found on any DN, an error is reported immediately, and other DNs are not checked.
- When set to false, the CUID and file ID on all DNs are checked.
Return type: SETOF record
| Field | Type | Description |
|---|---|---|
| nodename | text | Node name |
| is_valid | bool | Whether the check on the current node is passed |
| error_msg | text | Error message in the check result |
Example: Check whether the CUID and file ID mapping information on all DNs where the v3 table is located is correct.
1 2 3 4 5 6 | SELECT * FROM pgxc_check_bucket_info('t1'); nodename | is_valid | error_msg -----------+----------+----------- datanode2 | t | datanode1 | t | (2 rows) |
current_temp_schema()
Description: Returns the temporary schema of the current session. If the current session does not have a temporary schema, null is returned. This function is supported by clusters of version 8.2.1.220 or later.
Return type: name
Example:
1 2 3 4 5 | select current_temp_schema(); current_temp_schema --------------------- (1 row) |
1 2 3 4 5 6 7 | create temp table tmp1(a int,b int) distribute by hash(a); CREATE TABLE select current_temp_schema(); current_temp_schema ------------------------------------------ pg_temp_coordinator1_2_2_140718823485088 (1 row) |
get_volatile_pg_class()
Description: Obtains the pg_class metadata related to all volatile temporary tables in the current session. This parameter is supported by version 8.2.0 or later clusters.
Return type: record
Fields in the returned value:
- oid: OID of the volatile temporary table.
- Other fields: same as the fields (excluding hidden fields) in the pg_class system catalog.
get_volatile_pg_class(relname text)
Description: Obtains the pg_class metadata related to a specified volatile temporary table in the current session. This parameter is supported by version 8.2.0 or later clusters.
Parameter: name of the volatile temporary table in the current session.
Return type: record
Fields in the returned value:
- oid: OID of the volatile temporary table.
- Other fields: same as the fields (excluding hidden fields) in the pg_class system catalog.
Example:
1 2 3 4 5 6 7 8 9 10 11 | SELECT * FROM get_volatile_pg_class('tx1'); oid | relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | reltoastidxid | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassub class | relcmprs | relhasclusterkey | relrowmovement | parttype | relfrozenxid | relacl | reloptions | relreplident | relfrozenxid64 -------+---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+---------------+---------------+ -------------+----------------+--------------+-------------+-------------+----------------+---------+----------+-----------+------------+------------+-------------+----------------+---------- ------+----------+------------------+----------------+----------+--------------+--------+----------------------------------+--------------+---------------- 16772 | tx1 | 16770 | 16774 | 0 | 10 | 0 | 16772 | 1665 | 0 | 0 | 0 | 16775 | 0 | 0 | 0 | 0 | 0 | f | f | v | r | 2 | 0 | f | f | f | f | f | 1 | f | f | n | 11815 | | {orientation=row,compression=no} | d | 11815 (1 row) |
get_volatile_pg_attribute()
Description: Obtains the pg_attribute metadata related to all volatile temporary tables in the current session. This parameter is supported by version 8.2.0 or later clusters.
Return type: record
- oid: OID of the column.
- Other fields: same as the fields (excluding hidden fields) in the pg_attribute catalog.
get_volatile_pg_attribute(relname text, attrname text)
Description: Obtains the pg_attribute metadata related to a specified volatile temporary table in the current session. This parameter is supported by version 8.2.0 or later clusters.
Parameter:
- relname: table name (must be in the current session).
- attrname: column name.
Return type: record
Fields in the returned value:
- oid: OID of the column.
- Other fields: same as the fields (excluding hidden fields) in the pg_attribute catalog.
Example:
1 2 3 4 5 6 7 8 | SELECT * FROM get_volatile_pg_attribute('tx1', 'b'); attrelid | attname | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | attalign | attnotnull | atthasdef | attisdropped | attislocal | attcmprmode | attinhcount | attcollation | attacl | attoptions | attfdwoptions | attinitdefval | attkvtype ----------+---------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+------------+-----------+--------------+------------+ -------------+-------------+--------------+--------+------------+---------------+---------------+----------- 16772 | b | 25 | -1 | -1 | 2 | 0 | -1 | -1 | f | x | i | f | f | f | t | 127 | 0 | 100 | | | | | 0 (1 row) |
pg_get_publication_tables(pubname text)
Description: Returns the relid list of tables to be published based on the publication name. This function is supported by version 8.2.0.100 or later clusters.
Parameter: pubname
Return type: set of OID
Example:
1 2 3 4 5 6 | SELECT * FROM pg_get_publication_tables('mypub'); relid ------- 16757 16776 (2 rows) |
pg_relation_is_publishable(relname regclass)
Description: Checks whether a table can be published. This function is supported by version 8.2.0.100 or later clusters.
Parameter: relname
Return type: Boolean
Example:
1 2 3 4 5 | SELECT * FROM pg_relation_is_publishable('t1'); pg_relation_is_publishable ---------------------------- t (1 row) |
get_col_cu_info(schema_name text, table_name text, row_count int8, dirty_percent int8)
Description: Queries the CU information of a column-store table. The CU information of each partition is collected separately. This function is supported by version 8.2.0.100 or later clusters.
Parameters: schema name (mandatory), table name (mandatory), threshold for the number of rows in a small CU (optional, 200 by default, ranging from 1 to 60000, and percentage threshold for deleting dirty CUs (optional, 70 by default, ranging from 1 to 100)
Return type: record
Fields in the returned value:
node_name: DN name.
part_name: partition name. This column is empty for a common table.
zero_size_cu_count: number of CUs whose cuSize is 0 and number of rows is less than or equal to row_count.
small_cu_count: number of CUs whose cuSize is ALIGNOF_CUSIZE(8192) and number of rows is less than or equal to row_count.
dirty_cu_count: number of CUs whose dead tuple percentage exceeds dirty_percent due to deletion.
total_cu_count: total number of CUs.
small_cu_size: total size of 8 KB CUs.
total_cu_size: total CU size.
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SELECT * FROM get_col_cu_info('public','hs_part'); node_name | part_name | zero_size_cu_count | small_cu_count | dirty_cu_count | total_cu_count | small_cu_size | total_cu_size -----------+-----------+--------------------+----------------+----------------+----------------+---------------+--------------- dn_1 | p1 | 3 | 0 | 0 | 3 | 0 bytes | 0 bytes dn_1 | p2 | 3 | 0 | 0 | 3 | 0 bytes | 0 bytes dn_1 | p3 | 3 | 0 | 0 | 3 | 0 bytes | 0 bytes (3 rows) SELECT * FROM get_col_cu_info('public','hs_part', 200, 90); node_name | part_name | zero_size_cu_count | small_cu_count | dirty_cu_count | total_cu_count | small_cu_size | total_cu_size -----------+-----------+--------------------+----------------+----------------+----------------+---------------+--------------- dn_1 | p1 | 3 | 0 | 0 | 3 | 0 bytes | 0 bytes dn_1 | p2 | 3 | 0 | 0 | 3 | 0 bytes | 0 bytes dn_1 | p3 | 3 | 0 | 0 | 3 | 0 bytes | 0 bytes (3 rows) |
get_col_file_vacuum_info(schema_name text, table_name text, force_get_rewritten_file_num bool)
Description: Queries the vacuum information of a column-store table. The vacuum information of each partition is collected separately. This function is supported by version 8.2.0.100 or later clusters.
Parameters: schema name (mandatory), table name (mandatory), and whether to forcibly obtain the precise number of files that can be cleared (mandatory, false by default)
Return type: record
Fields in the returned value:
node_name: DN name.
part_name: partition name. This column is empty for a common table.
total_file_num: total number of CU files.
rewritable_file_num: number of files that can be rewritten but have not been rewritten.
rewritten_file_num: number of files that have been rewritten but have not been cleared. The value is obtained from data in the memory. If the memory data is lost due to reasons such as restart, you can set force_get_rewritten_file_num=true to forcibly obtain the accurate number of files that can be cleared.
empty_file_num: number of cleared files.
Example:
1 2 3 4 5 6 7 8 9 10 | SELECT * FROM get_col_file_vacuum_info('public','pa',false); node_name | part_name | total_file_num | rewritable_file_num | rewritten_file_num | empty_file_num -----------+-----------+----------------+---------------------+--------------------+---------------- datanode1 | pa1 | 1 | 0 | 0 | 0 datanode1 | pa2 | 1 | 0 | 0 | 0 datanode2 | pa1 | 1 | 0 | 0 | 0 datanode2 | pa2 | 1 | 0 | 0 | 0 datanode3 | pa1 | 1 | 0 | 0 | 0 datanode3 | pa2 | 1 | 0 | 0 | 0 (6 rows) |
get_col_file_vacuum_info(schema_name text, table_name text, colvacuum_threshold_scale_factor int)
Description: Queries the vacuum information of a column-store table. The vacuum information of each partition is collected separately. This function is supported by version 8.2.0.100 or later clusters.
Parameters: schema name (mandatory), table name (mandatory), and colvacuum_threshold_scale_factor (mandatory. The value range is 0 to 100, indicating the ratio of dead tuples.)
Return type: record
Return value:
node_name: DN name.
part_name: partition name. This column is empty for a common table.
total_file_num: total number of CU files.
rewritable_file_num: number of files that can be rewritten but have not been rewritten.
rewritten_file_num: number of files that have been rewritten but have not been cleared. The value is obtained from data in the memory. If the memory data is lost due to reasons such as restart, you can set force_get_rewritten_file_num=true to forcibly obtain the accurate number of files that can be cleared.
empty_file_num: number of cleared files.
Example:
1 2 3 4 5 6 7 8 9 10 | SELECT * FROM get_col_file_vacuum_info('public','pa',10); node_name | part_name | total_file_num | rewritable_file_num | rewritten_file_num | empty_file_num -----------+-----------+----------------+---------------------+--------------------+---------------- datanode1 | pa1 | 1 | 0 | 0 | 0 datanode1 | pa2 | 1 | 0 | 0 | 0 datanode2 | pa1 | 1 | 0 | 0 | 0 datanode2 | pa2 | 1 | 0 | 0 | 0 datanode3 | pa1 | 1 | 0 | 0 | 0 datanode3 | pa2 | 1 | 0 | 0 | 0 (6 rows) |
get_all_col_cu_info(row_count int8)
Description: Queries the CU information of all column-store tables in the database. This function is supported by version 8.2.0.100 or later clusters.
Parameter: threshold for the number of rows in a small CU (optional, 200 by default, and ranging from 1 to 60000)
Return type: record
Fields in the returned value:
node_name: DN name.
schema_name: schema name.
table_name: table name.
zero_size_cu_count: number of CUs whose cuSize is 0 and number of rows is less than or equal to row_count.
small_cu_count: number of CUs whose cuSize is ALIGNOF_CUSIZE(8192) and number of rows is less than or equal to row_count.
total_cu_count: total number of CUs.
small_cu_size: total size of 8 KB CUs.
total_cu_size: total CU size.
Example:
1 2 3 4 5 6 7 8 9 10 | SELECT * FROM get_all_col_cu_info(200); node_name | schema_name | table_name | zero_size_cu_count | small_cu_count | total_cu_count | small_cu_size | total_cu_size -----------+-------------+----------------------+--------------------+----------------+----------------+---------------+--------------- datanode1 | public | udi_48076 | 5 | 1 | 6 | 8192 bytes | 8192 bytes datanode1 | public | udi_48077 | 5 | 1 | 6 | 8192 bytes | 8192 bytes datanode2 | public | udi_48076 | 5 | 1 | 6 | 8192 bytes | 8192 bytes datanode2 | public | udi_48077 | 5 | 1 | 6 | 8192 bytes | 8192 bytes datanode3 | public | udi_48076 | 5 | 1 | 6 | 8192 bytes | 8192 bytes datanode3 | public | udi_48077 | 5 | 1 | 6 | 8192 bytes | 8192 bytes (6 rows) |
get_all_col_file_vacuum_info(force_get_rewritten_file_num bool)
Description: Queries the vacuum information of all column-store tables in the database. This function is supported by version 8.2.0.100 or later clusters.
Parameter: whether to forcibly obtain the accurate number of files that can be cleared (mandatory. It can be true or false .)
Return type: record
Fields in the returned value:
node_name: DN name.
schema_name: schema name.
table_name: table name.
total_file_num: total number of CU files.
rewritable_file_num: number of files that can be rewritten but have not been rewritten.
rewritten_file_num: number of files that have been rewritten but have not been cleared. The value is obtained from data in the memory. If the memory data is lost due to reasons such as restart, you can set force_get_rewritten_file_num=true to forcibly obtain the accurate number of files that can be cleared.
empty_file_num: number of cleared files.
Example:
1 2 3 4 5 6 7 8 9 | SELECT * FROM get_all_col_file_vacuum_info(false); node_name | schema_name | table_name | total_file_num | rewritable_file_num | rewritten_file_num | empty_file_num -----------+-------------+----------------------+----------------+---------------------+--------------------+---------------- datanode1 | public | udi_57373 | 2 | 0 | 0 | 1 datanode1 | public | udi_57374 | 2 | 0 | 0 | 1 datanode2 | public | udi_57373 | 2 | 0 | 0 | 1 datanode2 | public | udi_57374 | 2 | 0 | 0 | 1 datanode3 | public | udi_57373 | 2 | 0 | 0 | 1 datanode3 | public | udi_57374 | 2 | 0 | 0 | 1 |
show_tsc_info()
Description: Queries the TimeStamp-Counter (TSC) information obtained from the current database node. This function is supported by version 8.2.1 or later clusters.
Return type: record
| Name | Type | Description |
|---|---|---|
| node_name | text | Node name |
| tsc_mult | bigint | TSC conversion multiplier |
| tsc_shift | bigint | TSC conversion shifts |
| tsc_frequency | float8 | TSC frequency. |
| tsc_use_frequency | boolean | Indicates whether to use the TSC frequency for time conversion. |
| tsc_ready | boolean | Indicates whether the TSC frequency can be used for time conversion |
| tsc_scalar_error_info | text | Error information about obtaining TSC conversion information |
| tsc_freq_error_info | text | Error information about obtaining TSC frequency information |
Example:
1 2 3 4 | SELECT * FROM show_tsc_info(); node_name | tsc_mult | tsc_shift | tsc_frequency | tsc_use_frequency | tsc_ready | tsc_scalar_error_info | tsc_freq_error_info --------------+----------+-----------+---------------+-------------------+-----------+-------------------------------+--------------------- coordinator1 | | | 2400 | t | t | TSC scalar is not initialized | |
get_tsc_info()
Description: Re-obtains the TimeStamp-Counter (TSC) information of the current database node. This function is supported by version 8.2.1 or later clusters.
Return type: record
| Column | Type | Description |
|---|---|---|
| node_name | text | Node name |
| tsc_mult | bigint | TSC conversion multiplier |
| tsc_shift | bigint | TSC conversion shifts |
| tsc_frequency | float8 | TSC frequency |
| tsc_use_frequency | boolean | Indicates whether to use the TSC frequency for time conversion. |
| tsc_ready | boolean | Indicates whether the TSC frequency can be used for time conversion |
| tsc_scalar_error_info | text | Error information about obtaining TSC conversion information |
| tsc_freq_error_info | text | Error information about obtaining TSC frequency information |
Example:
1 2 3 4 | SELECT * FROM get_tsc_info(); node_name | tsc_mult | tsc_shift | tsc_frequency | tsc_use_frequency | tsc_ready | tsc_scalar_error_info | tsc_freq_error_info --------------+----------+-----------+---------------+-------------------+-----------+-------------------------------+--------------------- coordinator1 | | | 2400 | t | t | TSC scalar is not initialized | |
test_tsc_info(time float8, loops int)
Description: Tests the accuracy of the time converted using the TimeStamp-Counter (TSC) on the current node. This function is supported by version 8.2.1 or later clusters.
The input parameters are described as follows:
- time: indicates the test time difference (unit: s). The test duration must be less than or equal to 60s.
- loops: indicates the number of tests. The value ranges from 1 to 10.
Return type: record
Fields in the returned value:
- id: number of cycles.
- real_time_diff: time difference obtained using gettimeofday (unit: us).
- est_time_scalar: time difference (unit: μs) converted using TSC conversion information.
- est_time_frequency: time difference (unit: μs) converted using the TSC frequency.
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SELECT * FROM test_tsc_info(0.01,10); id | real_time_diff | est_time_scalar | est_time_frequency ----+----------------+-----------------+-------------------- 1 | 10057 | | 10056.9 2 | 10057 | | 10057.4816666667 3 | 10056 | | 10055.2841666667 4 | 10054 | | 10054.4908333333 5 | 10055 | | 10054.2875 6 | 10055 | | 10054.7483333333 7 | 10055 | | 10054.4725 8 | 10054 | | 10054.0766666667 9 | 10058 | | 10058.1016666667 10 | 10057 | | 10056.3733333333 (10 rows) |
gs_set_hold_table_level_oldestxmin()
Description: Marks a transaction as a long transaction after the table-level oldestXmin feature is enabled. When calculating oldestXmin of any table, this transaction is considered as an active transaction and is calculated in oldestXmin. This function is available only for clusters of version 9.1.1.100 or later.
Return type: void
This function can be used in the following scenarios:
- There is a potential long transaction, that is, the run time of the transaction is greater than old_txn_threshold and multiple tables are involved.
- A statement may run for a long time on a CN due to lock waits. For example, you use COPY and GDS for import and export.
- A long transaction is a transaction that has been running for a period longer than the value of old_txn_threshold (600 seconds by default) but has not been committed.
- Executing this function affects the calculation rules of table-level oldestXmin. The transaction is deemed active, regardless of the transaction running time is greater than the value of old_txn_threshold or whether the table is related to the transactions. As a result, the calculation of oldestXmin for all tables is affected.
- After a statement is executed, you are advised to call the gs_unset_hold_table_level_oldestxmin() function to cancel the marking in a timely manner. Otherwise, the VACUUM clearance effect will be affected.
gs_unset_hold_table_level_oldestxmin()
Description: Cancels the transaction marked as a long transaction after the table-level oldestXmin feature is enabled. This function is available only for clusters of version 9.1.1.100 or later.
Return type: void
A long transaction is a transaction that has been running for a period longer than the value of old_txn_threshold (600 seconds by default) but has not been committed.
What is your overall rating for this page?
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.
For any further questions, feel free to contact us through the chatbot.
Chatbot
