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

Other Functions

  • pgxc_pool_check()

    Description: Checks whether the connection data buffered in the pool is consistent with pgxc_node.

    Return type: Boolean

  • pgxc_pool_reload()

    Description: Updates the connection information buffered in the pool.

    Return type: Boolean

  • reload_active_coordinator()

    Description: Updates the connection information buffered in the pool for all active CNs.

    Return type: void

  • pgxc_lock_for_backup()

    Description: Locks a cluster for taking backup that would be restored on the new node to be added.

    Return type: Boolean

    pgxc_lock_for_backup locks a cluster before gs_dump or gs_dumpall is used to back up the cluster. After a cluster is locked, operations changing the system structure are not allowed. This function does not affect DML statements.

  • pg_pool_validate(clear bool, node_name text)

    Description: Displays invalid connections in the pooler between the CN and node_name. When the value of clear is true, invalid connections are cleared.

    Return type: record

  • gs_validate_node_conn(validate_type cstring, validate_node_name cstring)

    Description: Displays or clears the connections received by the current node from a specified node.

    Return type: record

    Table 1 gs_validate_node_conn parameters

    Category

    Parameter

    Type

    Description

    Input parameter

    validate_type

    cstring

    Specifies an input parameter type.

    • 'check': displays the connections received by the current node from a specified node.
    • 'clear': clears the connections received by the current node from a specified node.

    Input parameter

    validate_node_name

    cstring

    Specific a node name.

    Output parameter

    pid

    bigint

    Displays the ID of a thread where the current node receives connections from the specified node. If the thread is from a thread pool, the value is 0.

    Output parameter

    sessionid

    bigint

    Displays the ID of a session where the current node receives connections from the specified node. If the thread is not from a thread pool, the value is 0.

    Output parameter

    node_name

    text

    Displays the name of a node from which the current node receives connections.

  • pgxc_pool_connection_status()

    Description: Checks whether the pooler connection status is normal.

    Return type: Boolean

  • 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(text, text, text)

    Description: Queries the percentage of a specified column in the table data among all nodes.

    Parameters: name of the table to be queried, specified column name, and number of records in the specified table. The default value is 0, indicating that all records are queried. All parameters are of the text type.

    Return type: record

    Return value description: Node ID, number of data rows in a specified column, and percentage of the data volume of the current node to the total data volume

    Example:

    Return the distribution of the first five rows of data in the a column of the t table on a node.
    gaussdb=# select table_skewness('t', 'a',5);
     table_skewness
    ----------------
     (1,3,60.000%)
     (2,2,40.000%)
    (2 rows)
    
    Return the distribution of all data in the a column of the t table on a node.
    gaussdb=# select table_skewness('t', 'a');
     table_skewness
    ----------------
     (1,7,70.000%)
     (2,2,20.000%)
     (0,1,10.000%)
    (3 rows)
  • table_skewness_with_schema(text, text)

    Description: Checks the proportion of table data on all nodes. The function is the same as that of table_skewness(text).

    text indicates that the types of the schema name and table name for the table to be queried are both text.

    Return type: record

  • table_data_skewness(colrecord, type)

    Description: Queries the node where the table data is located.

    Parameter description:

    colrecord: column name record of the table to be queried. The value is of the record type.

    type: hash distribution type

    Return type: smallint

    Example:

    -- Return the node where the index column data of the 'test1' table is located.
    gaussdb=# select table_data_skewness(row(index), 'R') from test1;
     table_data_skewness
    ---------------------
                       4
                       3
                       1
                       2
    (4 rows)
  • table_distribution(schemaname text, tablename text)

    Description: Queries the storage space occupied by a specified table on each node.

    text indicates that the types of the schema name and table name for the table to be queried are both text.

    Return type: record

    • To query the storage distribution of a specified table by using this function, you must have the SELECT permission for the table.
    • The performance of table_distribution is better than that of table_skewness. In the scenario with a large amount of data, table_distribution is recommended.
    • When you use table_distribution and want to view the space usage, you can use dnsize or (sum(dnsize) over ()) to view the percentage.
  • 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.
    • Based on the table_distribution() function, GaussDB provides the PGXC_GET_TABLE_SKEWNESS view as an alternative way to query data skew. You are advised to use this view when the number of tables in the database is less than 10000.
  • plan_seed()

    Description: Obtains the seed value of the previous query statement (internal use).

    Return type: int

  • pg_stat_get_env()

    Description: Obtains the environment variable information of the current node. Only users with the sysadmin or monitor admin permission can access the environment variable information.

    Return type: record

    Example:

    gaussdb=# select pg_stat_get_env();
                                                                                  pg_stat_get_env
    ---------------------------------------------------------------------------------------------------------------------------------------
     (coordinator1,localhost,144773,49100,/data1/GaussDB_Kernel_TRUNK/install,/data1/GaussDB_Kernel_TRUNK/install/data/coordinator1,pg_log)
    (1 row)
  • pg_catalog.plancache_clean()

    Description: Clears the global plan cache that is not used on nodes.

    Return type: Boolean

  • pg_stat_get_thread()

    Description: Provides thread status information on the current node. Users with the sysadmin or monitor admin permission can view information about all threads. Common users can view only their own thread information.

    Return type: record

  • pgxc_get_os_threads()

    Description: Provides thread status information about all normal nodes in the entire cluster.

    Return type: record

  • pg_stat_get_sql_count()

    Description: Provides the counts of the SELECT, UPDATE, INSERT, DELETE, and MERGE INTO statements executed on the current node. Users with the sysadmin or monitor admin permission can view information about all users. Common users can view only their own statistics.

    Return type: record

  • pgxc_get_sql_count()

    Description: Provides the counts of the SELECT, UPDATE, INSERT, DELETE, and MERGE INTO statements executed on all the nodes in the entire cluster.

    Return type: record

  • pgxc_get_node_env()

    Description: Provides the environment variable information about all nodes in a cluster.

    Return type: record

  • pgxc_disaster_read_set(text)

    Description: Configures node information about the DR cluster on ETCD. Only the DR cluster is available and can be called only by initial users.

    Return type: Boolean

  • pgxc_disaster_read_init()

    Description: Initializes readable DR resources and status information. Only the DR cluster is available and can be called only by initial users.

    Return type: Boolean

  • pgxc_disaster_read_clear()

    Description: Clears readable DR resources and status information. Only the DR cluster is available and can be called only by initial users.

    Return type: Boolean

  • pgxc_disaster_read_status()

    Description: Provides node information about the DR cluster. This function is available only for the DR 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: int

    This function may cause clearing of statistics. You are advised to collect statistics again after this function is called.

  • pg_catalog.plancache_clean()

    Description: Clears the global plan cache that is not used on the current node.

    Return type: Boolean

  • DBE_PERF.global_plancache_clean()

    Description: Clears the global plan cache that is not used on all nodes.

    Return type: Boolean

  • 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 2.
    • In addition, it creates a B-tree index on the relname column and executes REVOKE ALL on public.pgxc_copy_error_log FROM public to manage permissions on 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 error tolerance is available only when row-store tables can be created in the cluster. Row-store tables cannot be created in the cluster if the GUC parameter enable_hadoop_env is set to on (by default, this parameter set to off for GaussDB).
    • 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 exists before the function creates it, the function will report an error and roll back.
    Table 2 Error table public.pgxc_copy_error_log

    Column

    Type

    Description

    relname

    character varying

    Table name in the form of Schema name.Table name

    begintime

    timestamp with time zone

    Time when a data format error was reported

    filename

    character varying

    Name of the source data file where a data format error occurs

    lineno

    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

    detail

    text

    Error details

  • pg_stat_get_data_senders()

    Description: Provides detailed information about the data-copy sender thread active at the moment.

    Return type: record

  • textlen()

    Description: Provides the method of querying the logical length of text.

    Return type: int

  • threadpool_status()

    Description: Displays the status of worker threads and sessions in the thread pool.

    Return type: record

  • get_local_active_session()

    Description: Provides sampling records of historical active sessions stored in the memory by current node. Users with the sysadmin or monitor admin permission can view all historical active session records of the current node. Common users can view the historical active session records of the current session.

    Return type: record

  • dbe_perf.get_global_active_session()

    Description: Provides sampling records of the historical active sessions stored in the memory of all nodes.

    Return type: record

  • dbe_perf.get_global_gs_asp(timestamp, timestamp)

    Description: Provides sampling records of the historical active sessions stored in the gs_asp system catalog of all nodes.

    Return type: record

  • get_wait_event_info()

    Description: Provides detailed information about the wait event.

    Return type: record

  • dbe_perf.get_datanode_active_session(text)

    Description: Provides sampling records of historical active sessions stored in the memory of DN, which is queried from CN.

    Return type: record

    Note: This function queries the records in the local_active_session view on the target DN and matches the records with those in the local_active_session view on all CNs to obtain the query string. Therefore, a large amount of memory is occupied.

  • dbe_perf.get_datanode_active_session_hist(text, timestamp, timestamp)

    Description: Provides sampling records of historical active sessions stored in the gs_asp system catalog of DN, which is queried from CN.

    Return type: record

    Note: This function queries the gs_asp records of a specified period on the target DN. If the period is specified too long, too many records will be queried, which takes a long time.

  • generate_wdr_report(bigint, bigint, cstring, cstring,cstring)

    Description: Generates a system diagnosis report based on two snapshots. By default, the initial user or monitor administrator can access the report. The result can be queried only in the system database but cannot be queried in the user database.

    Return type: text

    Table 3 generate_wdr_report parameter description

    Parameter

    Description

    Range

    begin_snap_id

    Snapshot ID that starts the diagnosis report period.

    N/A

    end_snap_id

    Snapshot ID that ends the diagnosis report period. By default, the value of end_snap_id is greater than that of begin_snap_id.

    N/A

    report_type

    Specifies the type of the generated report.

    • summary
    • detail
    • all: Both summary and detail types are included.

    report_scope

    Specifies the scope for a report to be generated.

    • cluster: database-level information
    • node: node-level information.

    node_name

    • When report_scope is set to node, set this parameter to the name of the corresponding node.
    • If report_scope is set to cluster, this parameter can be omitted or set to NULL.
    • node: node name in GaussDB.
    • cluster: This value is omitted, left blank or set to NULL.
  • create_wdr_snapshot()

    Description: Manually generates system diagnosis snapshots. This function requires the sysadmin permission and can be executed only on the CCN.

    Return type: text

  • kill_snapshot()

    Description: Kills the WDR snapshot backend thread. Users who call this function must have the SYSADMIN permission, the REPLICATION permission, or inherit the gs_role_replication permission of the built-in role.

    Return type: void

  • capture_view_to_json(text, integer)

    Description: Saves the view result to the directory specified by GUC: perf_directory. If is_crossdb is set to 1, the view is accessed once for all databases. If the value of is_crossdb is 0, the current database is accessed only once. Only users with the sysadmin or monitor admin permission can execute this function.

    Return type: int

  • reset_unique_sql(text, text, bigint)

    Description: Clears the Unique SQL statements in the memory of CN/DN. (The sysadmin or monitor admin permission is required.)

    Return type: Boolean

    Table 4 reset_unique_sql parameter description

    Parameter

    Type

    Description

    scope

    text

    Clearance scope type. The options are as follows:

    'GLOBAL': Clears all CNs/DNs. If the value is 'GLOBAL', this function can be executed only on the CN.

    'LOCAL': Clears the current node.

    clean_type

    text

    'BY_USERID': Unique SQL statements are cleared based on user IDs.

    'BY_CNID': Unique SQL statements are cleared based on CN IDs.

    'ALL': All data is cleared.

    clean_value

    int8

    Clearance value corresponding to the clearance type. If the second parameter is set to ALL, the third parameter does not take effect and can be set to any value.

  • wdr_xdb_query(db_name_str text, query text)

    Description: Provides the capability of executing local cross-database queries. For example, when connecting to the testdb database, access tables in the test database. Only the initial user has the permission to run this command.

    select col1 from wdr_xdb_query('dbname=test','select col1 from t1') as dd(col1 int);

    Return type: record

  • pg_wlm_jump_queue(pid int)

    Description: Moves a task to the top of the queue of CN.

    Return type: Boolean

    • true: success
    • false: failure
  • gs_wlm_switch_cgroup(pid int, cgroup text)

    Description: Moves a job to another Cgroup to change the job priority.

    Return type: Boolean

    • true: success
    • false: failure
  • pv_session_memctx_detail(threadid tid, MemoryContextName text)

    Description: Records information about the memory context MemoryContextName of the thread tid into the threadid_timestamp.log file in the $GAUSSLOG/pg_log/${node_name}/dumpmem directory. threadid can be obtained from sessid in the PV_SESSION_MEMORY_DETAIL table. In the officially released version, only the MemoryContextName that is an empty string (two single quotation marks indicate that the input is an empty string) is accepted. In this case, all memory context information is recorded. Otherwise, no operation is performed. This function can be executed only by the administrator.

    Return type: Boolean

    • true: success
    • false: failure
  • pg_shared_memctx_detail(MemoryContextName text)

    Description: Records information about the memory context MemoryContextName into the threadid_timestamp.log file in the $GAUSSLOG/pg_log/${node_name}/dumpmem directory. Calling this function in the officially released version does not involve any operation. Only the administrator can execute this function.

    Return type: Boolean

    • true: success
    • false: failure
  • local_bgwriter_stat()

    Description: Displays the information about pages flushed by the bgwriter thread of this instance, number of pages in the candidate buffer chain, and buffer elimination information.

    Return type: record

  • local_candidate_stat()

    Description: Displays the number of pages in the candidate buffer chain of this instance and buffer elimination information, including the normal buffer pool and segment buffer pool.

    Return type: record

  • local_ckpt_stat()

    Description: Displays the information about checkpoints and flushing pages of the current instance.

    Return type: record

  • local_double_write_stat()

    Description: Displays the doublewrite file status of the current instance.

    Return type: record
    Table 5 local_double_write_stat parameters

    Parameter

    Type

    Description

    node_name

    text

    Instance name

    curr_dwn

    int8

    Sequence number of the doublewrite file

    curr_start_page

    int8

    Start page for restoring the doublewrite file

    file_trunc_num

    int8

    Number of times that the doublewrite file is reused

    file_reset_num

    int8

    Number of reset times after the doublewrite file is full

    total_writes

    int8

    Total number of I/Os of the doublewrite file

    low_threshold_writes

    int8

    Number of I/Os for writing doublewrite files with low efficiency (the number of I/O flushing pages at a time is less than 16)

    high_threshold_writes

    int8

    Number of I/Os for writing doublewrite files with high efficiency (the number of I/O flushing pages at a time is more than 421)

    total_pages

    int8

    Total number of pages that are flushed to the doublewrite file area

    low_threshold_pages

    int8

    Number of pages that are flushed with low efficiency

    high_threshold_pages

    int8

    Number of pages that are flushed with high efficiency

    file_id

    int8

    ID of the current doublewrite file.

  • local_single_flush_dw_stat()

    Description: Displays the elimination of dual-write files on a single page in the instance.

    Return type: record

  • local_pagewriter_stat()

    Description: Displays the page flushing information and checkpoint information of the current instance.

    Return type: record

  • local_redo_stat()

    Description: Displays the replay status of the current standby instance.

    Return type: record

    Note: The returned replay status includes the current replay position and the replay position of the minimum restoration point.

  • local_recovery_status()

    Description: Displays log flow control information about the primary and standby nodes.

    Return type: record

  • local_rto_status()

    Description: Displays log flow control information about the primary and standby nodes.

    Return type: record

  • gs_wlm_switch_cgroup(sess_id int8, cgroup name)

    Description: Switches the Cgroup of a specified session.

    Return type: record

  • comm_client_info()

    Description: Queries active client connections of a single node. For details about the returned result, see COMM_CLIENT_INFO.

    Return type: SETOF record

  • pg_get_flush_lsn()

    Description: Returns the position of the Xlog flushed from the current node.

    Return type: text

  • pg_get_sync_flush_lsn()

    Description: Returns the position of the Xlog flushed by the majority on the current node.

    Return type: text

  • pgxc_wlm_rebuild_user_resource_pool()

    Description: Rebuilds user and resource pool cache information. Only the system administrator can execute this function.

    Return type: Boolean

  • locktag_decode(locktag text)

    Description: Parses lock details from locktag.

    Example:

    gaussdb=# select locktag_decode('271b:0:0:0:0:6');
                   locktag_decode
    ---------------------------------------------
     locktype:transactionid, transactionid:10011
    (1 row)

    Return type: text

  • disable_conn(disconn_mode text, host text, port integer)

    Description: Specifies that the CM Agent processes commands delivered by the CM Server. When a DN is selected as the primary DN, it is configured to reject connections to all DNs, forcibly connect to a DN, or connect to all DNs in polling mode. Only the initial user and system administrator can call this function.

    Return type: void

    Table 6 disable_conn parameter description

    Parameter

    Type

    Description

    disconn_mode

    text

    DN connection mode:

    • 'prohibit_connection': rejects to connect to all DNs.
    • 'specify_connection': forcibly connects to a DN.
    • 'polling_connection': connects to all DNs in polling mode.

    host

    text

    IP address of the DN

    port

    integer

    Port number of the DN

  • dbe_perf.get_global_full_sql_by_timestamp(start_timestamp timestamp with time zone, end_timestamp timestamp with time zone)

    Description: Obtains full SQL information about a cluster. The result can be queried only in the system database but cannot be queried in the user database.

    Return type: record

    Table 7 dbe_perf.get_global_full_sql_by_timestamp parameter description

    Parameter

    Type

    Description

    start_timestamp

    timestamp with time zone

    Start point of the SQL start time range.

    end_timestamp

    timestamp with time zone

    End point of the SQL start time range.

  • dbe_perf.get_global_slow_sql_by_timestamp(start_timestamp timestamp with time zone, end_timestamp timestamp with time zone)

    Description: Obtains cluster-level slow SQL information. The result can be queried only in the system database but cannot be queried in the user database.

    Return type: record

    Table 8 dbe_perf.get_global_slow_sql_by_timestamp parameter description

    Parameter

    Type

    Description

    start_timestamp

    timestamp with time zone

    Start point of the SQL start time range.

    end_timestamp

    timestamp with time zone

    End point of the SQL start time range.

  • statement_detail_decode(detail text, format text, pretty boolean)

    Description: Parses the details column in a full or slow SQL statement. The result can be queried only in the system database but cannot be queried in the user database.

    Return type: text

    Table 9 statement_detail_decode parameter description

    Parameter

    Type

    Description

    detail

    text

    Set of events generated by the SQL statement (unreadable).

    format

    text

    Parsing output format. The value is plaintext.

    pretty

    Boolean

    Specifies whether to display the text in pretty format when format is set to plaintext. The options are as follows:

    • The value true indicates that \n is used to separate events.
    • The value false indicates that events are separated by commas (,).
  • pgxc_get_csn(tid)

    Description: Returns the transaction submission sequence number (CSN) corresponding to a given transaction ID.

    Return type: int8

  • get_global_user_transaction()

    Description: Returns transaction information about each user on all nodes.

    Return type: node_name name, usename name, commit_counter bigint, rollback_counter bigint, resp_min bigint, resp_max bigint, resp_avg bigint, resp_total bigint, bg_commit_counter bigint, bg_rollback_counter bigint, bg_resp_min bigint, bg_resp_max bigint, bg_resp_avg bigint, and bg_resp_total bigint

  • pg_collation_for()

    Description: Returns the sorting rule corresponding to the input parameter string.

    Parameter: any (Explicit type conversion is required for constants.)

    Return type: text

  • pgxc_unlock_for_sp_database(name Name)

    Description: Releases a specified database lock.

    Parameter: database name

    Return type: Boolean

  • pgxc_lock_for_sp_database(name Name)

    Description: Locks a specified database.

    Parameter: database name

    Return type: Boolean

  • pgxc_unlock_for_transfer(name Name)

    Description: Releases the lock used for data transmission (data redistribution).

    Parameter: database name

    Return type: Boolean

  • pgxc_lock_for_transfer(name Name)

    Description: Locks the database for data transmission (data redistribution).

    Parameter: database name

    Return type: Boolean

  • gs_comm_proxy_thread_status()

    Description: Collects statistics on data packets sent and received by the proxy communications library comm_proxy when a user-mode network is configured for the cluster. This function is not supported in the current version.

    Parameter: nan

    Return type: record

  • gs_catalog_attribute_records()

    Description: Returns the definition of each field in a specified system catalog. Only common system catalogs whose OIDs are less than 10000 are supported. Indexes and TOAST tables are not supported.

    Parameter: OID of the system catalog

    Return type: record

  • dynamic_func_control(scope text, function_name text, action text, "{params}" text[])

    Description: Dynamically enables built-in functions. Currently, only full SQL statements can be dynamically enabled.

    Return type: record

    Table 10 Parameter description of dynamic_func_control

    Parameter

    Type

    Description

    scope

    text

    Scope where the function is to be dynamically enabled. Currently, only GLOBAL and LOCAL are supported.

    function_name

    text

    Function name. Currently, only STMT is supported.

    action

    text

    When function_name is set to STMT, the value of action can only be TRACK, UNTRACK, LIST, or CLEAN.
    • TRACK: records the full SQL information of normalized SQL statements.
    • UNTRACK: cancels the recording of full SQL information of normalized SQL statements.
    • LIST: lists normalized SQL information that is recorded in the current track.
    • CLEAN: cleans normalized SQL information that is recorded in the current track.

    params

    text[]

    When function_name is set to STMT, the parameters corresponding to different actions are set as follows:
    • TRACK: '{"Normalized SQLID", "L0/L1/L2"}'
    • UNTRACK: '{"Normalized SQLID"}'
    • LIST: '{}'
    • CLEAN: '{}'
  • gs_parse_page_bypath(path text, blocknum bigint, relation_type text, read_memory boolean)

    Description: Parses a specified table page and returns the path for storing the parsed content.

    Return type: text

    Note: Only the system administrator or O&M administrator can execute this function.

    Table 11 gs_parse_page_bypath parameters

    Parameter

    Type

    Description

    path

    text

    • For an ordinary table, the relative path is Tablespace name/Database OID/Relfilenode of the table (physical file name), for example, base/16603/16394.
    • For the visibility map of an ordinary table, the relative path is Tablespace name/Database OID/Visibility map of the ordinary table. For example, base/16603/16394_vm.
    • For clog files, the parsed content is stored in the pg_clog directory. For example: 000000000000.
    • For csnlog files, the parsed content is stored in the pg_csnlog directory. For example: 000000000000.
    • For undo record files, the relative path is undo/UNDOPERSISTENCE/zondid.segno in the undo directory. Example: undo/permanent/00000.0000009.
    • For undo meta files, the relative path is undo/UNDOPERSISTENCE/zondid.meta.segno in the undo directory. Example: undo/permanent/00000.meta.0000004.
    • You can run the pg_relation_filepath(table_name text) command to query the relative path of the table file. To obtain the path of the partitioned table, view the pg_partition system catalog and call pg_partition_filepath(partition_oid).
    • Valid path formats are as follows:
      • global/relNode
      • base/dbNode/relNode
      • pg_tblspc/spcNode/version_dir/dbNode/relNode

    blocknum

    bigint

    • –1: Information about all blocks (forcibly parsed from disks)
    • 0MaxBlockNumber: Information about the corresponding block
    • For B-tree/UB-tree indexes, 0 indicates the index meta-page.
    • For undo record files, the logical block number is used. The block number of the corresponding file is greater than or equal to segno x 128 and less than (segno + 1) x 128.
    • For undo meta files, the logical block number is used. The block number of the corresponding file is greater than or equal to segno x 4 and less than (segno + 1) x 4.

    relation_type

    text

    • heap: Astore table
    • uheap: Ustore table
    • btree: B-tree index
    • ubtree: UB-tree index
    • segment: segment-page. This parameter is reserved and is not supported currently.
    • vm: visibility map of the Astore ordinary table
    • clog (commit log): transaction status log
    • csnlog (commit sequence number log): snapshot timestamp log
    • undo_slot: transaction slot information
    • undo_record: undo record information

    read_memory

    Boolean

    • false: The system parses the page from the disk file.
    • true: The system attempts to parse the page from the shared buffer. If the page does not exist in the shared buffer, the system parses the page from the disk file.

    Example:

    # Parse the information of all pages in the B-tree index file.
    # Before calling the function, ensure that the file path exists based on the parameter description.
    gaussdb=# select gs_parse_page_bypath('base/16603/16394', -1, 'btree', false);
                            gs_parse_page_bypath                        
    --------------------------------------------------------------------
     /data_dir/1663_16603_16394_-1.page
    (1 row)
    
    # Parse the visibility result of all blocks in the visibility map file.
    gaussdb=# select gs_parse_page_bypath('base/12828/16771_vm', -1, 'vm', false);
                             gs_parse_page_bypath                          
    -----------------------------------------------------------------------
     /data_dir/1663_12828_16771_-1_vm.page
    (1 row)
    
    # Parse the commit log of block 0 in the Clog file.
    gaussdb=# select gs_parse_page_bypath('000000000000', 0, 'clog', false); 
                            gs_parse_page_bypath                        
    --------------------------------------------------------------------
     /data_dir/000000000000.clog
    (1 row)

    The following is an example of an exception error:

    # An error is reported when the value of the block number exceeds the value range.
    gaussdb=# select gs_parse_page_bypath('base/12828/16777', -10, 'heap', false);
    ERROR:  Blocknum should be between -1 and 4294967294.
    CONTEXT:  referenced column: gs_parse_page_bypath
  • gs_xlogdump_lsn(start_lsn text, end_lsn text)

    Description: Parses Xlogs within the specified LSN range and returns the path for storing the parsed content. You can use pg_current_xlog_location() to obtain the current Xlog position.

    Parameters: LSN start position and LSN end position

    Return type: text

    Note: Only the system administrator or O&M administrator can execute this function.

  • gs_xlogdump_xid(c_xid xid)

    Description: Parses Xlogs of a specified XID and returns the path for storing the parsed content. You can use txid_current() to obtain the current XID.

    Parameter: XID

    Return type: text

    Note: Only the system administrator or O&M administrator can execute this function.

  • gs_xlogdump_tablepath(path text, blocknum bigint, relation_type text)

    Description: Parses logs corresponding to a specified table page and returns the path for storing the parsed content.

    Return type: text

    Note: Only the system administrator or O&M administrator can execute this function.

    Table 12 gs_xlogdump_tablepath parameters

    Parameter

    Type

    Description

    path

    text

    • For an ordinary table, the relative path is Tablespace name/Database OID/Relfilenode of the table (physical file name), for example, base/16603/16394.
    • You can run the pg_relation_filepath(table_name text) command to query the relative path of the table file. To obtain the path of the partitioned table, view the pg_partition system catalog and call pg_partition_filepath(partition_oid).
    • Valid path formats are as follows:
      • global/relNode
      • base/dbNode/relNode
      • pg_tblspc/spcNode/version_dir/dbNode/relNode

    blocknum

    bigint

    • –1: Information about all blocks (forcibly parsed from disks)
    • 0MaxBlockNumber: Information about the corresponding block

    relation_type

    text

    • heap: Astore table
    • btree: B-tree index
    • segment: segment-page. This parameter is reserved and is not supported currently.
  • gs_xlogdump_parsepage_tablepath(path text, blocknum bigint, relation_type text, read_memory boolean)

    Description: Parses the specified table page and logs corresponding to the table page and returns the path for storing the parsed content. It can be regarded as one execution of gs_parse_page_bypath and gs_xlogdump_tablepath. The prerequisite for executing this function is that the table file exists. To view logs of deleted tables, call gs_xlogdump_tablepath.

    Return type: text

    Note: Only the system administrator or O&M administrator can execute this function.

    Table 13 gs_xlogdump_parsepage_tablepath parameters

    Parameter

    Type

    Description

    path

    text

    • For an ordinary table, the relative path is Tablespace name/Database OID/Relfilenode of the table (physical file name), for example, base/16603/16394.
    • You can run the pg_relation_filepath(table_name text) command to query the relative path of the table file. To obtain the path of the partitioned table, view the pg_partition system catalog and call pg_partition_filepath(partition_oid).
    • Valid path formats are as follows:
      • global/relNode
      • base/dbNode/relNode
      • pg_tblspc/spcNode/version_dir/dbNode/relNode

    blocknum

    bigint

    • –1: Information about all blocks (forcibly parsed from disks)
    • 0MaxBlockNumber: Information about the corresponding block

    relation_type

    text

    • heap: Astore table
    • btree: B-tree index
    • segment: segment-page. This parameter is reserved and is not supported currently.

    read_memory

    Boolean

    • false: The system parses the page from the disk file.
    • true: The system attempts to parse the page from the shared buffer. If the page does not exist in the shared buffer, the system parses the page from the disk file.
  • gs_index_recycle_queue(Oid oid, int type, uint32 blkno)

    Description: Parses the UB-tree index recycling queue information.

    Return type: record

    Table 14 gs_index_recycle_queue parameters

    Parameter

    Type

    Description

    oid

    Oid

    • Index file relfilenode, which can be queried using select relfilenode from pg_class where relname='Index file name'.

    type

    int

    • 0 indicates that the entire queue to be recycled is parsed.
    • 1 indicates that the entire empty page queue is parsed.
    • 2 indicates that a single page is parsed.

    blkno

    uint32

    ID of the recycling queue page. This parameter is valid only when type is set to 2. The value of blkno ranges from 1 to 4294967294.

    This function is not supported in the distributed version. An error message will be displayed if it is used in the distributed version.

  • gs_stat_wal_entrytable(int64 idx)

    Description: Exports the content of the write-ahead log insertion status table in the Xlog.

    Return type: record

    Table 15 gs_stat_wal_entrytable parameters

    Category

    Parameter

    Type

    Description

    Input parameter

    idx

    int64

    • –1: queries all elements in an array.
    • 0-Maximum value: content of a specific array element.

    Output parameter

    idx

    uint64

    Records the indexes in the corresponding array.

    Output parameter

    endlsn

    uint64

    Records the LSN label.

    Output parameter

    lrc

    int32

    Records the corresponding LRC.

    Output parameter

    status

    uint32

    Specifies whether the Xlog corresponding to the current entry has been completely copied to the WAL buffer.

    • 0: not copied
    • 1: copied
  • gs_walwriter_flush_position()

    Description: Outputs the refresh position of write-ahead logs.

    Return type: record

    Table 16 gs_walwriter_flush_position parameters

    Category

    Parameter

    Type

    Description

    Output parameter

    last_flush_status_entry

    int32

    Index obtained after the Xlog flushes the tblEntry of the last flushed disk.

    Output parameter

    last_scanned_lrc

    int32

    LRC obtained after the Xlog flushes the last tblEntry scanned last time.

    Output parameter

    curr_lrc

    int32

    Latest LRC usage in the WALInsertStatusEntry status table. The LRC indicates the LRC value corresponding to the WALInsertStatusEntry when the next Xlog record is written.

    Output parameter

    curr_byte_pos

    uint64

    The latest Xlog position after the Xlog is written to the WAL file, which is also the next Xlog insertion point.

    Output parameter

    prev_byte_size

    uint32

    Length of the previous Xlog record.

    Output parameter

    flush_result

    uint64

    Position of the current global Xlog flush.

    Output parameter

    send_result

    uint64

    Xlog sending position on the current host.

    Output parameter

    shm_rqst_write_pos

    uint64

    The write position of the LogwrtRqst request in the XLogCtl recorded in the shared memory.

    Output parameter

    shm_rqst_flush_pos

    uint64

    The flush position of the LogwrtRqst request in the XLogCtl recorded in the shared memory.

    Output parameter

    shm_result_write_pos

    uint64

    The write position of the LogwrtResult request in the XLogCtl recorded in the shared memory.

    Output parameter

    shm_result_flush_pos

    uint64

    The flush position of the LogwrtResult request in the XLogCtl recorded in the shared memory.

    Output parameter

    curr_time

    text

    Current time.

  • gs_walwriter_flush_stat(int operation)

    Description: Collects statistics on the frequency of writing and synchronizing write-ahead logs, data volume, and Xlog file information.

    Return type: record

    Table 17 gs_walwriter_flush_stat parameters

    Category

    Parameter

    Type

    Description

    Input parameter

    operation

    int

    • –1: Disables the statistics function. (Default value)
    • 0: Enable the statistics function.
    • 1: Query statistics.
    • 2: Reset statistics.

    Output parameter

    write_times

    uint64

    Number of times that the Xlog calls the write API.

    Output parameter

    sync_times

    uint64

    Number of times that the Xlog calls the sync API.

    Output parameter

    total_xlog_sync_bytes

    uint64

    Total number of backend thread requests for writing data to Xlogs.

    Output parameter

    total_actual_xlog_sync_bytes

    uint64

    Total number of Xlogs that call the sync API for disk flushing.

    Output parameter

    avg_write_bytes

    uint32

    Number of Xlogs written each time the XLogWrite API is called.

    Output parameter

    avg_actual_write_bytes

    uint32

    Number of Xlogs written each time the write API is called.

    Output parameter

    avg_sync_bytes

    uint32

    Average number of Xlogs for each synchronization request.

    Output parameter

    avg_actual_sync_bytes

    uint32

    Actual Xlog amount of disk flushing by calling sync each time.

    Output parameter

    total_write_time

    uint64

    Total time of calling the write operation (unit: μs).

    Output parameter

    total_sync_time

    uint64

    Total time for calling the sync API (unit: μs).

    Output parameter

    avg_write_time

    uint32

    Average time for calling the write API each time (unit: μs).

    Output parameter

    avg_sync_time

    uint32

    Average time for calling the sync API each time (unit: μs).

    Output parameter

    curr_init_xlog_segno

    uint64

    ID of the latest Xlog segment file.

    Output parameter

    curr_open_xlog_segno

    uint64

    ID of the Xlog segment file that is being written.

    Output parameter

    last_reset_time

    text

    Time when statistics were last collected.

    Output parameter

    curr_time

    text

    Current time.

  • pg_ls_tmpdir()

    Description: Returns the name, size, and last modification time of each file in the temporary directory (pgsql_tmp) of the default tablespace.

    Parameter: nan

    Return type: record

    Note: Only the system administrator or monitor administrator can execute this function.

    Category

    Parameter

    Type

    Description

    Output parameter

    name

    text

    File name.

    Output parameter

    size

    int8

    File size (unit: byte).

    Output parameter

    modification

    timestamptz

    Last file modification time.

  • pg_ls_tmpdir(oid)

    Description: Returns the name, size, and last modification time of each file in the temporary directory (pgsql_tmp) of the specified tablespace.

    Parameter: oid

    Return type: record

    Note: Only the system administrator or monitor administrator can execute this function.

    Category

    Parameter

    Type

    Description

    Input parameter

    oid

    oid

    Tablespace ID.

    Output parameter

    name

    text

    File name.

    Output parameter

    size

    int8

    File size (unit: byte).

    Output parameter

    modification

    timestamptz

    Last file modification time.

  • pg_ls_waldir()

    Description: Returns the name, size, and last modification time of each file in the WAL directory.

    Parameter: nan

    Return type: record

    Note: Only the system administrator or monitor administrator can execute this function.

    Category

    Parameter

    Type

    Description

    Output parameter

    name

    text

    File name.

    Output parameter

    size

    int8

    File size (unit: byte).

    Output parameter

    modification

    timestamptz

    Last file modification time.

  • gs_write_term_log(void)

    Description: Writes a log to record the current term value of a DN. The standby DN returns false. After the data is successfully written to the primary DN, true is returned.

    Return type: Boolean

  • gs_stat_space(bool init)

    Description: Queries the status of extended pages when the INSERT operation is performed on the Ustore table.

    Return type: record

    Category

    Parameter

    Type

    Description

    Input parameter

    init

    bool

    Specifies whether to reset the statistics.

    Output parameter

    access_func

    int8

    Total number of access times of the relation_get_buffer_for_utuple API.

    Output parameter

    cache_blk

    int8

    Number of times that the relation_get_buffer_for_utuple API obtains buffers.

    Output parameter

    cache_succ

    int8

    Number of times that the relation_get_buffer_for_utuple API successfully obtains buffers.

    Output parameter

    nblk_first

    int8

    Number of times that relation_get_buffer_for_utuple obtains nblocks-1 for the first time.

    Output parameter

    nblk_first_succ

    int8

    Number of times that relation_get_buffer_for_utuple obtains nblocks-1 successfully for the first time.

    Output parameter

    nblk_second

    int8

    Number of times that relation_get_buffer_for_utuple obtains nblocks-1 for the second time.

    Output parameter

    nblk_second_succ

    int8

    Number of times that relation_get_buffer_for_utuple obtains nblocks-1 successfully for the second time.

    Output parameter

    fsm_first

    int8

    Number of times that FSM is accessed for the first time.

    Output parameter

    fsm_first_success

    int8

    Number of times that FSM is accessed successfully for the first time.

    Output parameter

    fsm_rewrite

    int8

    Number of FSM writeback times.

    Output parameter

    fsm_second

    int8

    Number of times that FSM is accessed for the second time.

    Output parameter

    fsm_second_sucess

    int8

    Number of times that FSM is accessed successfully for the second time.

    Output parameter

    prune_count

    int8

    Number of prune times in relation_get_buffer_for_utuple.

    Output parameter

    prune_space

    int8

    Total prune space in relation_get_buffer_for_utuple.

    Output parameter

    coprune_count

    int8

    Number of times that joint prune is performed.

    Output parameter

    coprune_scan_blocks

    int8

    Total number of pages scanned for joint prune.

    Output parameter

    coprune_prune_count

    int8

    Number of joint prune times.

    Output parameter

    coprune_prune_space

    int8

    Total space for joint prune.

    Output parameter

    con_extend_count

    int8

    Number of concurrent extended pages.

    Output parameter

    con_extend_time

    int8

    Total time for concurrent page extension.

    Output parameter

    single_extend_time

    int8

    Number of single-page extension times.

    Example:

    gaussdb=# select * from gs_stat_space(false);
     access_func | cache_blk | cache_succ | nblk_first | nblk_first_succ | nblk_sencond | nblk_sencond_succ | fsm_first | fsm_first_success | fsm_rewrite | fsm_
    second | fsm_second_success | prune_count | prune_space | coprune_count | coprune_scan_blocks | coprune_prune_count | coprune_prune_space | con_extend_count
     | con_extend_time | single_extend_count
    -------------+-----------+------------+------------+-----------------+--------------+-------------------+-----------+-------------------+-------------+-----
    -------+--------------------+-------------+-------------+---------------+---------------------+---------------------+---------------------+-----------------
    -+-----------------+---------------------
        10082397 |  10082397 |   10082235 |         88 |              78 |        47021 |             47021 |       162 |                74 |      360996 |
    360996 |             270948 |        6711 |           0 |         45497 |              222619 |                   0 |                   0 |             3675
     |        25542884 |               26791
    (1 row)
    

    Note: Pay special attention to the value of cache_succ. If it is small, the system cache is invalid. If the value of prune_space is small, the Ustore data page cleaning mechanism may be faulty. If the value of con_extend_time is too large, the Ustore concurrent page extension takes a long time.

  • gs_redo_upage(directory_path text, backup_path text, blocknum bigint, relation_type text, xlog_path text, lsn text)

    Description:Redoes a specific Ustore data page that is backed up to a specified LSN and verifies the page in this process. If a damaged page is detected, the page is flushed to the disk and the disk flushing path, page LSN, and damage information is returned; otherwise, the page is redone to the specified LSN and flushed to the disk, and the result is returned. Only system administrators or O&M administrators can execute this function.

    Return type: record

    Category

    Parameter

    Type

    Description

    Input parameter

    directory_path

    text

    Specifies the directory for storing the backup file.

    Input parameter

    backup_path

    text

    Relative path of the backup table file, which is combined with the directory where the backup file is located to form the complete path of the table file, for example, base/15635/12488. If the backup file does not exist, set this parameter to null.

    Input parameter

    blocknum

    bigint

    0 to MaxBlockNumber: block number of the corresponding page.

    Input parameter

    relation_type

    text

    • uheap: Ustore data page
    • ubtree: Ustore index page
    • indexurq: Urq page
    • undo_record: Undo record page
    • undo_slot: Transaction slot page

    Input parameter

    xlog_path

    text

    Absolute path of the archive log directory.

    Input parameter

    lsn

    text

    The LSN consists of two hexadecimal numbers (32 bits each), which are separated by a slash (/), for example, 2/962D1DF8. If the value is 0, the latest version is used.

    Output parameter

    output_filename

    text

    Path and name of the file to be flushed to the disk.

    Output parameter

    output_lsn

    text

    LSN of the last page redo.

    Output parameter

    corruption_desc

    text

    Page damage description.

  • gs_xlogdump_bylastlsn(last_lsn text, blocknum bigint, relation_type text)

    Description: Inputs a page LSN and block number, parses the WAL corresponding to the LSN, obtains the last LSN of the corresponding block number, continues parsing until the last LSN is 0 or the WAL of an earlier version has been reused and recycled, and flushes the parsed log to a specified path. Only system administrators or O&M administrators can execute this function. This system function cannot be called by the standby node.

    Return type: text

    Category

    Parameter

    Type

    Description

    Input parameter

    last_lsn

    text

    Parses the LSN of a specified page in hexadecimal notation, for example, 12BA/32CDEDDD. The LSN can be obtained using a page parsing tool (such as gs_parse_page_bypath).

    Input parameter

    blocknum

    bigint

    Specifies the logical block number of a page.

    Value range: -1 to MaxBlockNumber.

    If the block number is set to -1, the default block number is obtained from the WAL.

    Input parameter

    relation_type

    text

    Specifies the type of the page to be parsed.

    Valid value: uheap, ubtree, heap, btree, undo_record, and undo_slot.

    Output parameter

    output_filepath

    text

    Specifies the path for flushing WAL parsing results to disks.

    Example:
    # Obtain the page LSN.
    # Before calling the function, ensure that the file path exists based on the parameter description.
    gaussdb=# select * from gs_parse_page_bypath('base/15833/16768', 0, 'uheap', false);
                            output_filepath
    ---------------------------------------------------------------
     /data1/database/cluster/primary/data/1663_15833_16768_0.page
    (1 row)
    gaussdb=# select * from gs_xlogdump_bylastlsn('0/4593570', -1, 'uheap');
                            output_filepath
    -------------------------------------------------------------------
     /data1/database/cluster/primary/data/pg_log/dump/4593570_-1.xlog
    (1 row)
    gaussdb=# select * from gs_xlogdump_bylastlsn('0/4593570', 0, 'ubtree');
    ERROR:  The input lsn 0/4593570 related xlog is not ubtree.
  • dbe_perf.get_full_sql_by_parent_id_and_timestamp(parent_id bigint, start_timestamp timestamp with time zone, end_timestamp timestamp with time zone)

    Description: Obtains the records of database-level full SQL statements and their substatements that execute a stored procedure in a specified period based on parent_id. The result can be queried only in the system database but cannot be queried in the user database.

    Return type: record

    Parameter

    Type

    Description

    parent_id

    bigint

    Specifies the unique_sql_id of the statement calling a stored procedure.

    start_timestamp

    timestamp with time zone

    Start point of the SQL start time range.

    end_timestamp

    timestamp with time zone

    End point of the SQL start time range.

    Example:

    gaussdb=# CREATE TABLE test(a int,b int);
    NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'a' as the distribution column by default.
    HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
    CREATE TABLE
    gaussdb=# INSERT INTO test values(1,1);
    INSERT 0 1
    gaussdb=# CREATE PROCEDURE mypro1() as num int;
    gaussdb$# begin
    gaussdb$# INSERT INTO test values(2,2);
    gaussdb$# DELETE FROM test where a = 2;
    gaussdb$# end;
    gaussdb$# /
    CREATE PROCEDURE
    
    # Enable the parameter to trace the substatements of the stored procedure.
    gaussdb=# SET instr_unique_sql_track_type = 'all';
    SET
    
    # Enable the parameter. Full statement records are generated in the dbe_perf.statement_history table.
    gaussdb=# SET track_stmt_stat_level = 'L0,L0';
    SET
    
    gaussdb=# CALL mypro1();
     mypro1
    --------
    
    (1 row)
    
    gaussdb=# SET track_stmt_stat_level = 'off,L0';
    SET
    
    gaussdb=# SET instr_unique_sql_track_type = 'top';
    SET
    
    # Query key information, which is used as a function parameter.
    gaussdb=# SELECT query,unique_query_id,start_time,finish_time FROM dbe_perf.statement_history;
                    query                 | unique_query_id |          start_time           |          finish_time
    --------------------------------------+-----------------+-------------------------------+-------------------------------
     set track_stmt_stat_level = 'L0,L0'; |       636388010 | 2023-06-02 17:40:49.176155+08 | 2023-06-02 17:40:49.176543+08
     call mypro1();                       |       536458473 | 2023-06-02 17:40:59.028144+08 | 2023-06-02 17:40:59.032027+08
     delete from test where a = ?         |       583323884 | 2023-06-02 17:40:59.029955+08 | 2023-06-02 17:40:59.031577+08
     insert into test values(?,?)         |       769279931 | 2023-06-02 17:40:59.029219+08 | 2023-06-02 17:40:59.029947+08
    (4 rows)
    
    # Use unique_query_id, start time, and end time of the outer query statement as parameters to query information about the specified stored procedure and its substatements within the period.
    gaussdb=# SELECT query FROM dbe_perf.get_full_sql_by_parent_id_and_timestamp(536458473,'2023-06-02 17:40:59.028144+08','2023-06-02 17:40:59.032027+08');
                query
    ------------------------------
     call mypro1();
     delete from test where a = ?
     insert into test values(?,?)
    (3 rows)
  • gs_index_dump_read(int8 reset, text out_type)

    Description: Queries the buffer read information generated in the cyclic queue when an index is used to obtain a new page. The buffer read information traverses leaf pages from left to right using the same key as the index page.

    Return type: record

    Category

    Parameter

    Type

    Description

    Input parameter

    reset

    int8

    • 0: The statistics are reset to the initial value 0 and the statistics are collected again.
    • 1: The current statistics are displayed.

    Input parameter

    out_type

    text

    • urq: outputs statistics about cyclic queues.
    • ubtree: outputs statistics on index pages.
    • all: All statistics about cyclic queues and index pages are output.

    Output parameter

    relfilenode

    oid

    Index relfilenode corresponding to the maximum buffer read value.

    Output parameter

    max_count

    int8

    Maximum buffer read value.

    Output parameter

    ave_count

    int8

    Average buffer read value.

    • Currently, this API supports only Ustore index tables.
    • When this API is executed, the reset operation is performed to clear all records and set all records to 0. If you query the information again, the query result is always 0 until the information is collected next time. The following are query examples:
      gaussdb=# SELECT * FROM gs_index_dump_read(0, 'all');
      relfilenode | max_count | ave_count
      --------------+-------------+-------------
      |                     |
      (1 row)
      gaussdb=# SELECT * FROM gs_index_dump_read(1, 'all');
      relfilenode | max_count | ave_count
      --------------+-------------+-------------
      0 |                  0 |                  0
      0 |                  0 |                  0
      (2 rows)