Other Functions
- 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 --------------------------------------------------------------------------------------------------------------------------------------- (sgnode,"localhost,XXX.XXX.XXX.XXX",28589,26000,/home/omm,/home/omm/data/single_node,gs_log) (1 row) - pg_catalog.plancache_clean()
Description: Clears the global plan cache that is not used on nodes.
Return type: Boolean
- pg_catalog.plancache_status()
Description: Displays information about the global plan cache on nodes. The information returned by the function is the same as that in GLOBAL_PLANCACHE_STATUS.
Return type: record
- textlen(text)
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 the historical active sessions stored in the memory of the current node.
Return type: record
- pg_stat_get_thread()
Description: Provides status information about all threads on the current node. users with the sysadmin or monitor admin permission can view information about all threads, and common users can view only their own thread information.
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, and common users can view only their own statistics.
Return type: record
- pg_stat_get_data_senders()
Description: Provides detailed information about the data-copy sender thread active at the moment.
Return type: record
- get_wait_event_info()
Description: Provides detailed information about the wait event.
Return type: record
- generate_wdr_report(begin_snap_id bigint, end_snap_id bigint, report_type cstring, report_scope cstring, node_name cstring)
Description: Generates system diagnosis reports based on two snapshots. You need to run the command in the system database. By default, the initial user or users with the monadmin role permission can access the database. The result can be queried only in the system database but cannot be queried in the user database.
Return type: record
Table 1 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 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. (You can run the select * from pg_node_env; command to query the node name.)
If report_scope is set to cluster, the value can be omitted, left blank, or NULL.
- cluster: omitted, empty, or NULL.
- node: node name in GaussDB.
- create_wdr_snapshot()
Description: Manually generates system diagnosis snapshots. This function requires the sysadmin permission.
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 permissions of the built-in role gs_role_replication.
Return type: void
- generate_asp_report(start_time timestamp with time zone, end_time timestamp with time zone, slot_count bigint)
Description: Generates ASP diagnosis reports based on timestamps, which can be accessed by the monadmin user. slot_count indicates the number of time segments for displaying metrics.
Return type: text
- dbe_perf.get_active_session_profile(start_ts timestamp with time zone, end_ts timestamp with time zone, need_final boolean)
Description: Queries data in the ASP memory and disk based on timestamps. need_final specifies whether to query blocking information. The default value is false.
Return type: record
- 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 the database node. (The sysadmin or monitor admin permission is required.)
Return type: Boolean
Table 2 reset_unique_sql parameter description Parameter
Type
Description
scope
text
Clearance scope type. The options are as follows:
- 'GLOBAL': Clears all nodes. If the value is 'GLOBAL', this function can be executed only on the primary node.
- '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 primary node 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, only the initial user has the permission to access the table in the test database.
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 the primary node of the database.
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/gs_log/${node_name}/dumpmem directory. threadid can be obtained from sessid in the GS_SESSION_MEMORY_DETAIL view. In the officially released version, only 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/gs_log/${node_name}/dumpmem directory. This function is provided only for internal development and test personnel to debug in the DEBUG version. 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_aio_completer_stat()
Description: Displays statistics about the AIO Completer thread in the instance.
Return type: record
Table 3 Return values of local_aio_completer_stat Parameter
Type
Description
node_name
text
Name of the current instance.
tid
int8
ID of the AIO Completer thread.
thread_type
text
AIO Completer thread type (read or write).
aio_submitted_num
int8
Number of committed asynchronous I/O requests of the AIO Completer thread.
aio_completed_num
int8
Number of completed asynchronous I/O requests of the AIO Completer thread.
aio_incompleted_num
int8
Number of asynchronous I/O requests that are not completed of the AIO Completer thread.
slot_count_left
int8
Indicates the number of idle slots.
- local_aio_slot_usage_status()
Description: Displays statistics about asynchronous I/O commit slots in the instance.
Return type: record
Table 4 Return values of local_aio_slot_usage_status Parameter
Type
Description
nodename
text
Name of the current instance.
slot_id
int4
Slot ID.
slot_type
char
Slot type. The value can be r (read) or w (write).
status
bool
Slot usage status.
buffer_id
int8
Buffer ID corresponding to the slot.
relfilenode_blocknum
text
Position of the physical page where the buffer corresponding to the slot is located.
lsn
int8
LSN corresponding to the page.
submitted_time
int8
Time when a page is committed asynchronously.
elapsed_time
int8
Waiting time of the page.
- gs_get_io_type()
Description: Displays the I/O mode of the instance.
Return type: text
- BIO: The instance is running in BIO mode (ADIO is disabled).
- DIO: The instance is running in DIO mode (ADIO is enabled).
- BIO->DIO (In progress): The current instance is switching from the BIO mode to the DIO mode.
- 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
-
gs_wlm_node_recover(boolean isForce)
Description: Obtains top SQL query statement-level statistics recorded in the current memory. If the input parameter is not 0, the information is cleared from the memory.
Return type: record
- gs_cgroup_map_ng_conf(group name)
Description: Reads the Cgroup configuration file of a specified logical database.
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 information about active client connections of a single node.
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
- gs_create_log_tables()
- 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 at the database level. The result can be queried only in the system database but cannot be queried in the user database.
Return type: record
Table 6 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 slow SQL information at the database level. 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_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 8 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 events are separated by \n.
- The value false indicates that events are separated by commas (,).
- pgxc_get_csn(tid)
Description: Returns the transaction commit sequence number (CSN) corresponding to a given transaction ID.
Return type: int8
- pgxc_get_csn(tid, bucketid)
Description: The hash bucket table is not supported in the current version in centralized mode. An error is reported when the function is called.
- pg_control_system()
Description: Returns the status of the system control file.
Return type: SETOF record
- pg_control_checkpoint()
Description: Returns the system checkpoint status.
Return type: SETOF record
- get_prepared_pending_xid()
Description: Returns nextxid when restoration is complete.
Parameter: nan
Return type: text
- pg_clean_region_info()
Description: Clears the regionmap.
Parameter: nan
Return type: character varying
- pg_get_replication_slot_name
Description: Obtains the slot name.
Parameter: nan
Return type: text
- pg_get_running_xacts()
Description: Obtains running xact.
Parameter: nan
Return type: handle integer, gxid xid, state tinyint, node text, xmin xid, vacuum boolean, timeline bigint, prepare_xid xid, pid bigint, and next_xid xid
- pg_get_variable_info()
Description: Obtains the shared memory variable cache.
Parameter: nan
Return type: node_name text, nextOid oid, nextXid xid, oldestXid xid, xidVacLimit xid, oldestXidDB oid, lastExtendCSNLogpage xid, startExtendCSNLogpage xid, nextCommitSeqNo xid, latestCompletedXid xid, and startupMaxXid xid
- pg_get_xidlimit()
Description: Obtains transaction ID information from the shared memory.
Parameter: nan
Return type: nextXid xid, oldestXid xid, xidVacLimit xid, xidWarnLimit xid, xidStopLimit xid, xidWrapLimit xid, and oldestXidDB oid
- pg_relation_compression_ratio()
Description: Queries the compression rate of a table. By default, 1.0 is returned.
Parameter: text
Return type: real
- pg_relation_with_compression()
Description: Queries whether a table is compressed.
Parameter: text
Return type: Boolean
- pg_stat_file_recursive()
Description: Lists all files in the path.
Parameter: location text
Return type: path text, filename text, size bigint, and isdir boolean
- pg_stat_get_activity_for_temptable()
Description: Returns records of backend threads related to the temporary table.
Parameter: nan
Return type: datid oid, timelineid integer, tempid integer, and sessionid bigint
- pg_stat_get_activity_ng()
Description: Returns records of backend threads related to nodegroup.
Parameter: pid bigint
Return type: datid oid, pid bigint, sessionid bigint, and node_group text
- pg_stat_get_cgroup_info()
Description: Returns Cgroup information.
Parameter: nan
Return type: cgroup_name text, percent integer, usage_percent integer, shares bigint, usage bigint, cpuset text, relpath text, valid text, and node_group text
- pg_stat_get_realtime_info_internal()
Description: Returns real-time information. Currently, this API is unavailable. FailedToGetSessionInfo is returned.
Parameter: oid, oid, bigint, cstring, and oid
Return type: text
- pg_test_err_contain_err()
Description: Tests the error type and return information.
Parameter: integer
Return type: void
- 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)
- pgxc_lock_for_sp_database(name Name)
- 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 9.
- 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 database instance. Note that after the GUC parameter enable_hadoop_env is enabled, row-store tables cannot be created in the database instance (the default value is 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 9 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
- 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 LOCAL is 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 or segment-page 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)
- 0–MaxBlockNumber: 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
- 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
- indexurq: indexurq page
- gsivfflat_index: gsivfflat vector index
- gsdiskann_index: gsdiskann vector index
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 -------------------------------------------------------------------- /gs_log_dir/dump/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 ----------------------------------------------------------------------- /gs_log_dir/dump/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 -------------------------------------------------------------------- /gs_log_dir/dump/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.
Return type: text
Parameters: LSN start position and LSN end position
Note: Only the system administrator or O&M administrator can execute this function.
-
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 or segment-page 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)
- 0–MaxBlockNumber: Information about the corresponding block
relation_type
text
- heap: Astore table
- uheap: Ustore table
- btree: B-tree index
- ubtree: UB-tree index
- segment: segment-page table
- 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 ordinary tables, 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)
- 0–MaxBlockNumber: information about the corresponding block
relation_type
text
- heap: Astore table
- uheap: Ustore table
- btree: B-tree index
- ubtree: UB-tree index
- indexurq: indexurq page
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_verify(oid oid, uint32 blkno)
Description: Checks whether the sequence of keys on the UB-tree index page or index tree is correct.
Return type: record
Table 14 gs_index_verify parameters Parameter
Type
Description
oid
oid
- Index file relfilenode, which can be queried using select relfilenode from pg_class where relname='Index file name'.
blkno
uint32
- 0: indicates that all pages in the index tree are checked.
- If the value is greater than 0, the index page whose page code is equal to the value of blkno is checked.
- gs_index_recycle_queue(Oid oid, int type, uint32 blkno)
Description: Parses the UB-tree index recycling queue information.
Return type: record
Table 15 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: The entire queue to be recycled is parsed.
- 1: The entire empty page queue is parsed.
- 2: 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.
- 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 16 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
Determines 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 17 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 18 gs_walwriter_flush_stat parameters Category
Parameter
Type
Description
Input parameter
operation
int
- –1 (default): disables the statistics function.
- 0: enables the statistics function.
- 1: queries statistics.
- 2: resets 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.
- gs_catalog_attribute_records()
Description: Returns the definition of each field in a specified system catalog. Only ordinary 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
- 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 database instance.
Parameter: nan
Return type: record
The query result of this function is displayed only when the user-mode network is deployed in a centralized environment and enable_dfx in comm_proxy_attr is set to true. In other scenarios, an error message is displayed, indicating that queries are not supported.
- 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_stat_anti_cache()
Description: Returns AntiCache statistics.
Parameter: nan
Return type: record
Category
Parameter
Type
Description
Output parameter
table_nums
int8
Number of partitions.
Output parameter
table_init_size
int8
Initial size of a partition.
Output parameter
table_max_size
int8
Upper limit of the partition size.
Output parameter
anti_cache_upper_limit_size
int8
Upper limit of the AntiCache size.
Output parameter
anti_cache_size
int8
Real-time AntiCache size.
Output parameter
anti_cache_max_table_size
int8
Real-time maximum partition size.
Output parameter
anti_cache_min_table_size
int8
Real-time minimum partition size.
- gs_stat_vlog_buffer()
Description: Returns verifyLog buffer statistics.
Parameter: nan
Return type: record
Category
Parameter
Type
Description
Output parameter
vbuffer_write_offset
int8
Vlog write location.
Output parameter
vbuffer_flushed_offset
int8
Vlog disk flushing location.
Output parameter
max_vbuffer_flushed_value
int8
Maximum number of bytes for vlog flushing.
Output parameter
min_vbuffer_flushed_vlaue
int8
Minimum number of bytes for vlog flushing.
Output parameter
ave_vbuffer_flushed_vlaue
int8
Average number of bytes for vlog flushing.
Output parameter
vbuffer_flush_latency
int8
Vlog refresh delay.
- gs_stat_vlog_related_io()
Description: Returns I/O statistics about read and write operations on the verifylog file.
Parameter: nan
Return type: record
Category
Parameter
Type
Description
Output parameter
read_data_iops
float8
Number of pages read from files per second.
Output parameter
vlog_read_iops
float8
Number of pages read from vlogs per second.
Output parameter
vlog_write_iops
float8
Number of pages written to vlogs per second.
- 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_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: outputs all statistics about cyclic queues and index pages.
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)
- 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 and 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/gs_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.
- gs_shared_storage_flush_stat(int operation)
Description: Collects statistics on the amount of written data, write speed, write time, amount of read data, read speed, and read time of shared disks.
Return type: record
Category
Parameter
Type
Description
Input parameter
operation
int
- -1: disables the statistics function.
- 0 (default): enables the statistics function.
- 1: queries statistics.
- 2: resets statistics.
After the statistics function is disabled and then enabled again, the previous statistics are cleared.
Output parameter
stat_switch
bool
Specifies whether the statistics function is enabled.
Output parameter
write_times
uint64
Number of times that the shared_storage_xlog_copy thread calls the dorado_write_xlog API.
Output parameter
avg_write_bytes
uint32
Amount of Xlogs to be written each time the dorado_write_xlog API is called, in byte.
Output parameter
avg_actual_write_bytes
uint32
Actual amount of Xlogs to be written each time the dorado_write_xlog API is called, in byte.
Output parameter
total_write_time
uint64
Total time for calling the dorado_write_xlog API, in μs.
Output parameter
avg_write_time
uint32
Average time for calling the dorado_write_xlog API each time, in μs.
Output parameter
avg_write_speed
uint32
Average speed of calling the dorado_write_xlog API to write Xlogs, in KB/s.
Output parameter
avg_actual_write_speed
uint32
Actual average speed of calling the dorado_write_xlog API to write Xlogs, in KB/s.
Output parameter
total_write_sleep_time
uint64
Total sleep time of the shared_storage_xlog_copy thread.
Output parameter
read_times
uint64
Number of times that the walreceiver thread calls the dorado_read_xlog API.
Output parameter
avg_read_bytes
uint32
Amount of Xlogs to be written each time the dorado_read_xlog API is called, in byte.
Output parameter
avg_actual_read_bytes
uint32
Actual amount of Xlogs to be written each time the dorado_read_xlog API is called, in byte.
Output parameter
total_read_time
uint64
Total time for calling the dorado_read_xlog API, in μs.
Output parameter
avg_read_time
uint32
Average time for calling the dorado_read_xlog API each time, in μs.
Output parameter
avg_read_speed
uint32
Average speed of calling the dorado_read_xlog API to write Xlogs, in KB/s.
Output parameter
avg_actual_read_speed
uint32
Actual average speed of calling the dorado_read_xlog API to write Xlogs, in KB/s.
Output parameter
total_read_sleep_time
uint64
Total sleep time of the walreceiver thread.
Output parameter
stat_start_time
timestamptz
Start time of the statistics. If the statistics are not reset, the value is the start time of GaussDB. If the statistics are reset, the value is the last reset time.
Output parameter
stat_end_time
timestamptz
End time of the current statistics collection. If the statistics collection function is enabled, the value is the current time. If the statistics collection function is disabled, the value is the last time when the statistics collection function is disabled.
Example:
gaussdb=# SELECT * FROM gs_shared_storage_flush_stat(1); stat_switch | write_times | avg_write_bytes | avg_actual_write_bytes | total_write_time | avg_write_time | avg_write_speed | avg_actual_write_speed | total_write_sleep_time | read_times | avg _read_bytes | avg_actual_read_bytes | total_read_time | avg_read_time | avg_read_speed | avg_actual_read_speed | total_read_sleep_time | stat_start_time | stat_end_time -------------+-------------+-----------------+------------------------+------------------+----------------+-----------------+------------------------+------------------------+------------+---- ------------+-----------------------+-----------------+---------------+----------------+-----------------------+-----------------------+-------------------------------+------------------------ ------- t | 43644 | 8624 | 8624 | 263922553 | 6047 | 1392 | 1392 | 26918503404 | 7451 | 140 13 | 14013 | 5321082 | 714 | 19163 | 19163 | 0 | 2023-12-18 07:18:39.766855+08 | 2023-12-18 16:29:22.757 535+08 (1 row) - 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); 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) gaussdb=# DROP PROCEDURE mypro1(); DROP PROCEDURE gaussdb=# DROP TABLE test; DROP TABLE
Feedback
Was this page helpful?
Provide feedbackThank 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
