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 MONADMIN permission can access the environment variable information. In the multi-tenancy scenario, no information is returned in a PDB.
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. This is not supported in the multi-tenancy scenario.
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. This is not supported in the multi-tenancy scenario.
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.
In the multi-tenancy scenario, global data is visible in a non-PDB. On a primary node, PDB data is visible in its own PDB. Thread group isolation is not performed on a standby node, and no data is returned when data is queried in a PDB.
Return type: record
get_local_active_session()
Description: Provides sampling records of the historical active sessions stored in the memory of the current node. In the multi-tenancy scenario, PDB data is visible in its own PDB, and global data is visible in a non-PDB.
Return type: record
pg_stat_get_thread()
Description: Provides status information about all threads on the current node. Users with the SYSADMIN or MONADMIN permission can view information about all threads, and common users can view only their own thread information. In the multi-tenancy scenario, the global information is returned if this function is called in a non-PDB, and only the information about a local PDB is returned if this function is called in a PDB.
Return type: record
pg_stat_get_sql_count()
Description: Provides the counts of the SELECT, UPDATE, INSERT, DELETE, and MERGE INTO statements executed by the user on the current node, as well as the counts of DDL, DML, and DCL statements. The DDL statements include the TRUNCATE, VACUUM, ANALYZE, CREATE (excluding CREATE ROLE and CREATE USER), DROP (excluding DROP ROLE and DROP USER), ALTER (excluding ALTER ROLE, ALTER USER, and ALTER DEFAULT PRIVILEGES), REINDEX, and COMMENT statements. The DML statements include the UPDATE, INSERT, DELETE, MERGE, PREPARE, EXPLAIN, SHOW, LOCK, COPY, CLUSTER, ANONYMOUS, DELTA, EXECUTE, MOVE, FETCH, CLOSE, DEALLOCATE, DECLARE, and LOAD statements. The DCL statements include the CREATE ROLE, CREATE USER, DROP ROLE, DROP USER, ALTER ROLE, ALTER USER, ALTER DEFAULT PRIVILEGES, GRANT, REVOKE, REASSIGN, and SET statements. Users with the SYSADMIN or MONADMIN permission can view information about all users. Common users can view only their own statistics. In the multi-tenancy scenario, the global information is returned if this function is called in a non-PDB, and only the information about a local PDB is returned if this function is called in a PDB.
Return type: record
pg_stat_get_data_senders()
Description: Provides detailed information about the data-copy sender thread active at the moment. In the multi-tenancy scenario, no information is returned in a PDB.
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 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
|
Parameter |
Description |
Value 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. |
|
|
report_scope |
Specifies the scope for a report to be generated. |
|
|
node_name |
|
|
generate_wdr_db_report(db_name cstring, begin_snap_id bigint, end_snap_id bigint, report_type cstring, report_scope cstring, node_name cstring)
Description: Generates database-level 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 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
|
Parameter |
Description |
Value Range |
|---|---|---|
|
db_name |
Specifies the name of a database. |
N/A |
|
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. |
|
|
report_scope |
Specifies the scope for a report to be generated. |
|
|
node_name |
|
|
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 or 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, node_name text)
Description: Generates ASP based on timestamps, which can be accessed by MONADMIN. slot_count indicates the number of time segments for displaying metrics. node_name is used to generate the ASP of a specified node. The value can be the name of a primary/standby CN or can be left empty. When the value is left empty, this function is used to generate the report of the current node. This function can be used only on the primary DN.
Return type: text
dbe_perf.get_active_session_profile(start_ts timestamp with time zone, end_ts timestamp with time zone, need_final boolean,node_name text)
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. node_name is used to distinguish the active and standby DNs, for example, dn_6001 or dn_6002. This parameter cannot be left empty. In the multi-tenancy scenario, PDB data is visible in its own PDB, and global data is visible in a non-PDB.
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 MONADMIN 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 MONADMIN permission is required.) In the multi-tenancy scenario, only PDB data is cleared if this function is called in a PDB, and global data is cleared if this function is called in a non-PDB.
Return type: Boolean
|
Parameter |
Type |
Description |
|---|---|---|
|
scope |
text |
Clearance scope type. The options are as follows:
|
|
clean_type |
text |
|
|
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. In the multi-tenancy scenario, this function runs properly if it is called in a non-PDB, and an error is reported if it is called in a PDB.
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 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/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
|
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
|
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. In the multi-tenancy scenario, the non-PDB runs properly when calling this function, and an empty list is returned if this function is called in a PDB.
Return type: record
local_double_write_stat()
Description: Displays the doublewrite file status of the current instance. In the multi-tenancy scenario, all information is returned if this function is called in a non-PDB, and an empty list is returned if this function is called in a PDB.
Return type: record
|
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. Value range: an int4 value in the range [1,5]. |
local_single_flush_dw_stat()
Description: Displays the elimination of doublewrite 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. In the multi-tenancy scenario, the non-PDB runs properly when calling this function, and an empty list is returned if this function is called in a PDB.
Return type: record
local_redo_stat()
Description: Displays the replay status of the current standby instance. In the multi-tenancy scenario, the non-PDB runs properly when calling this function, and an empty list is returned if this function is called in a PDB.
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. In the multi-tenancy scenario, the non-PDB runs properly when calling this function, and an empty list is returned if this function is called in a PDB.
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
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
|
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. An error is reported during function execution if start_timestamp is greater than or equal to end_timestamp. |
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
|
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. An error is reported during function execution if start_timestamp is greater than or equal to end_timestamp. |
statement_detail_decode(detail text, format text, pretty boolean)
Description: Parses the detail 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
|
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:
|
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. In the multi-tenancy scenario, the global information is returned if this function is called in a non-PDB, and only the information about a local PDB is returned if this function is called in a PDB.
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, and dbid oid
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 XID 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 a path. In the multi-tenancy scenario, this function is disabled in a PDB.
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. In the multi-tenancy scenario, the global information is returned if this function is called in a non-PDB, and only the information about a local PDB is returned if this function is called in a PDB.
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, bg_resp_total bigint, and dbid oid
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)
This function is unavailable in the current version.
pgxc_lock_for_sp_database(name Name)
This function is unavailable in the current version.
copy_error_log_create()
Description: The error table required by the COPY FROM error tolerance mechanism has been changed to the system catalog created during initialization. Therefore, the current function always returns true for compatibility.
Return type: Boolean
|
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
|
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.
|
|
params |
text[] |
When function_name is set to STMT, the parameters corresponding to different actions are set as follows:
|
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 SYSADMIN or OPRADMIN can execute this function.
|
Parameter |
Type |
Description |
|---|---|---|
|
path |
text |
|
|
blocknum |
bigint |
|
|
relation_type |
text |
|
|
read_memory |
Boolean |
|
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 SYSADMIN or OPRADMIN 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 SYSADMIN or OPRADMIN 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. In the multi-tenancy scenario, this function is disabled in a PDB.
Return type: text
Note: Only SYSADMIN or OPRADMIN can execute this function.
|
Parameter |
Type |
Description |
|---|---|---|
|
path |
text |
|
|
blocknum |
bigint |
|
|
relation_type |
text |
|
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. In the multi-tenancy scenario, this function is disabled in a PDB.
Return type: text
Note: Only SYSADMIN or OPRADMIN can execute this function.
|
Parameter |
Type |
Description |
|---|---|---|
|
path |
text |
|
|
blocknum |
bigint |
|
|
relation_type |
text |
|
|
read_memory |
Boolean |
|
gs_shared_storage_xlogdump_lsn(start_lsn text, end_lsn text, xlog_file_path text)
Description: Parses Xlogs within the specified LSN range of a specified shared disk 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
Note: Only SYSADMIN or OPRADMIN can execute this function.
|
Parameter |
Type |
Description |
|---|---|---|
|
start_lsn |
text |
Start LSN of the Xlog to be parsed. |
|
end_lsn |
text |
End LSN of the Xlog to be parsed. |
|
xlog_file_path |
text |
(Optional) Path of the shared volume where the Xlog to be parsed is located. If not specified, Xlogs of all shared disks on the current DN are parsed. |
gs_shared_storage_ctlinfo()
Description: Obtains information about the metadata of the shared volume on the current DN.
Return type: record
|
Category |
Parameter |
Type |
Description |
|---|---|---|---|
|
Output parameter |
xlog_file_path |
text |
Path of the shared volume. |
|
Output parameter |
system_id |
text |
The system_id value stored on the current shared volume. |
|
Output parameter |
term |
text |
The term value stored on the current shared volume. |
|
Output parameter |
xlog_file_size |
text |
Size of the space for storing Xlogs on the shared volume. |
|
Output parameter |
insert_tail |
text |
Minimum valid LSN on the shared volume. |
|
Output parameter |
insert_head |
text |
Maximum valid LSN on the shared volume. |
|
Output parameter |
shared_storage_flush_lsn |
text |
LSN that has been flushed to disk on the shared volume. |
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
|
Parameter |
Type |
Description |
|---|---|---|
|
oid |
oid |
|
|
blkno |
uint32 |
|
gs_index_recycle_queue(Oid oid, int type, uint32 blkno)
Description: Parses the UB-tree index recycling queue information.
Return type: record
|
Parameter |
Type |
Description |
|---|---|---|
|
oid |
oid |
|
|
type |
int |
|
|
blkno |
uint32 |
|
gs_stat_wal_entrytable(int64 idx)
Description: Exports the content of the write-ahead log insertion status table in the Xlog.
Return type: record
|
Category |
Parameter |
Type |
Description |
|---|---|---|---|
|
Input parameter |
idx |
int64 |
|
|
Output parameter |
idx |
uint64 |
Records the subscripts in the corresponding array. |
|
Output parameter |
endlsn |
uint64 |
Records the LSN label. Determines whether the Xlog corresponding to the current entry has been completely copied to the WAL buffer.
|
|
Output parameter |
lrc |
int32 |
Records the corresponding LRC. |
|
Output parameter |
status |
uint32 |
This parameter is deprecated in the current version. The query result is 0. |
gs_walwriter_flush_position()
Description: Outputs the refresh position of write-ahead logs.
Return type: record
|
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 WALs, data volume, and Xlog file information.
Return type: record
|
Category |
Parameter |
Type |
Description |
|---|---|---|---|
|
Input parameter |
operation |
int |
|
|
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 sync 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 for calling the write operation (unit: μs). |
|
Output parameter |
total_sync_time |
uint64 |
Total time for calling the sync operation (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 column in a system catalog specified by OID. 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 SYSADMIN or MONADMIN 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 SYSADMIN or MONADMIN 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 SYSADMIN or MONADMIN 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 a 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. In the multi-tenancy scenario, this function is disabled in a PDB.
Return type: record
|
Category |
Parameter |
Type |
Description |
|---|---|---|---|
|
Input parameter |
reset |
int8 |
|
|
Input parameter |
out_type |
text |
|
|
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 SYSADMIN or OPRADMIN can execute this function. In the multi-tenancy scenario, this function is disabled in a PDB.
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 |
|
|
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 SYSADMIN or OPRADMIN 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. |
# 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. In the multi-tenancy scenario, this function runs properly if it is called in a non-PDB, and an error is reported if it is called in a PDB.
Return type: record
|
Category |
Parameter |
Type |
Description |
|---|---|---|---|
|
Input parameter |
operation |
int |
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 WAL receiver 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 WAL receiver 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
gin_clean_pending_list(index regclass)
Description: Clears the pending list of a specified UGIN index, moves items in the pending list to the primary index page in batches, and returns the number of cleared pages. If the fastupdate option is not enabled for the index, the pending list will not be cleared, and the result is 0.
Return type: bigint
gs_stat_freespace(Oid relid, Oid partid, char type, int sample_ratio)
Description: Estimates the bloat rate of a table, partitioned table, or index based on statistics or sampling, and outputs space usage statistics and bloat rate information.
Return type: record
|
Category |
Parameter |
Type |
Description |
|---|---|---|---|
|
Input parameter |
relation_oid |
OID |
OID of a data table, main table of the partitioned table, or index to be queried. |
|
Input parameter |
partation_oid |
OID |
OID of a partitioned table, level-2 partitioned table, or local partitioned index.
|
|
Input parameter |
relation_type |
CHAR |
Table type:
|
|
Input parameter |
sample_ratio |
INT8 |
Sampling ratio. The value ranges from 0 to 100.
|
|
Input parameter |
read_memory |
BOOL |
Specifies whether to read pages from the memory.
|
|
Output parameter |
OID |
OID |
OID of a data table, partitioned table, level-2 partitioned table, or index to be queried. |
|
Output parameter |
INFO |
TEXT |
|
Currently, this API supports only row-store tables. Column-store tables, time series tables, segment-page tables, hash bucket tables, local temporary tables, and global temporary tables are not supported.
Example:
-- Query ordinary table information. Create a table: drop table if exists t; create table t(a int) with (storage_type=ustore); insert into t values(generate_series(1, 110000)); Update statistics: vacuum analyze t; Estimate bloat rate: gaussdb=# select * from gs_stat_freespace((select oid from pg_class where relname='t'), 0, 'r', 0, false); oid | info -------+----------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------- 16735 | live_tuples: 110000, dead_tuples: 0, avg_tuple_len: 4, tuple_header_size: 11, rowptr_size: 440000, page_header_size: 56, nblocks: 419, approx imate_table_size: 2113464 (Bytes), approximate expansion rate: 1.62 (1 row) Sampling information: gaussdb=# select * from gs_stat_freespace((select oid from pg_class where relname='t'), 0, 'r', 80, false); oid | info -------+----------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------- 16732 | Average page stat info: page_header_size: 28, tuple has storage: 222.22, normal tuple: 222.22, dead tuple: 0.00, unused tuple: 0.00, redirecte d tuple: 0.00, td_count: 0.00, potential_freespace: 0.00, actual_freespace: 152.00. Freespace stat info: nblocks: 45, approximate_table_size: 342495 (B ytes), approximate expansion rate: 1.08. (1 row) -- Query information about a single partition in a partitioned table. Create a table: drop table if exists t; create table t(a int) with (storage_type=ustore) partition by range(a) (partition p1 values less than(3000), partition p2 values less than(maxvalue)); insert into t values(generate_series(1, 10000)); Update statistics: vacuum analyze t; Query the OID: select oid from pg_class where relname='t'; gaussdb=# select oid from pg_class where relname='t'; oid ------- 16802 (1 row) select oid, relname, parttype from pg_partition; gaussdb=# select oid, relname, parttype from pg_partition; oid | relname | parttype -------+---------+---------- 16805 | t | r 16806 | p1 | p 16807 | p2 | p (3 rows) Estimate bloat rate: gaussdb=# select * from gs_stat_freespace(16802, 16806, 'r', 0, false); oid | info -------+----------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------- 16806 | live_tuples: 5998, dead_tuples: 0, avg_tuple_len: 4, tuple_header_size: 11, rowptr_size: 23992, page_header_size: 56, nblocks: 12, approximat e_table_size: 114634 (Bytes), approximate expansion rate: 0.86 (1 row) Sampling information: gaussdb=# select * from gs_stat_freespace(16802, 16807, 'r', 0, false); oid | info -------+----------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------- 16807 | live_tuples: 14002, dead_tuples: 0, avg_tuple_len: 4, tuple_header_size: 11, rowptr_size: 56008, page_header_size: 56, nblocks: 27, approxima te_table_size: 267550 (Bytes), approximate expansion rate: 0.83 (1 row) -- Query information about all partitions in a partitioned table. Create a table: drop table if exists t; create table t(a int) with (storage_type=ustore) partition by range(a) (partition p1 values less than(3000), partition p2 values less than(maxvalue)); insert into t values(generate_series(1, 10000)); Update statistics: vacuum analyze t; Query the OID: select oid from pg_class where relname='t'; gaussdb=# select oid from pg_class where relname='t'; oid ------- 16802 (1 row) select oid, relname, parttype from pg_partition; gaussdb=# select oid, relname, parttype from pg_partition; oid | relname | parttype -------+---------+---------- 16805 | t | r 16806 | p1 | p 16807 | p2 | p (3 rows) Estimate bloat rate: gaussdb=# select * from gs_stat_freespace(16802, 0, 'r', 0, false); oid | info -------+----------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------- 16806 | live_tuples: 5998, dead_tuples: 0, avg_tuple_len: 4, tuple_header_size: 11, rowptr_size: 23992, page_header_size: 56, nblocks: 12, approximat e_table_size: 114634 (Bytes), approximate expansion rate: 0.86 16807 | live_tuples: 14002, dead_tuples: 0, avg_tuple_len: 4, tuple_header_size: 11, rowptr_size: 56008, page_header_size: 56, nblocks: 27, approxima te_table_size: 267550 (Bytes), approximate expansion rate: 0.83 (2 rows) Sampling information: gaussdb=# select * from gs_stat_freespace(16802, 0, 'r', 60, false); oid | info -------+----------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------- 16806 | Average page stat info: page_header_size: 56, tuple has storage: 249.92, normal tuple: 249.92, dead tuple: 0.00, unused tuple: 0.00, redirecte d tuple: 0.00, td_count: 4.00, potential_freespace: 1034.33, actual_freespace: 1034.33. Freespace stat info: nblocks: 12, approximate_table_size: 84636 (Bytes), approximate expansion rate: 1.16. 16807 | Average page stat info: page_header_size: 56, tuple has storage: 259.30, normal tuple: 259.30, dead tuple: 0.00, unused tuple: 0.00, redirecte d tuple: 0.00, td_count: 4.00, potential_freespace: 771.70, actual_freespace: 771.70. Freespace stat info: nblocks: 27, approximate_table_size: 197532 (Bytes), approximate expansion rate: 1.12. (2 rows) -- Query ordinary index information. Create a table: drop table if exists t; create table t(a int, b int, c varchar(1000)) with (storage_type=ustore); create index on t(a); insert into t values(generate_series(1, 100000), generate_series(1, 100000), 'kjgkjature'); Update statistics: vacuum analyze t; Estimate bloat rate: gaussdb=# select * from gs_stat_freespace((select oid from pg_class where relname='t_a_idx'), 0, 'i', 0, false); oid | info -------+----------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------- 16776 | live_tuples: 100000, dead_tuples: 0, origin_avg_tuple_len 4, max_align_avg_tuple_len: 8, tuple_header_size: 16, rowptr_size: 401548, page_head er_size: 24, nblocks: 387, approximate_table_size: 2838700 (Bytes), approximate expansion rate: 1.12 (1 row) Sampling information: gaussdb=# select * from gs_stat_freespace((select oid from pg_class where relname='t_a_idx'), 0, 'i', 60, false); oid | info -------+----------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------- 16776 | Average page stat info: page_header_size: 24, tuple has storage: 260.39, normal tuple: 260.39, dead tuple: 0.00, unused tuple: 0.02, redirecte d tuple: 0.00, td_count: 0.00, potential_freespace: 0.00, actual_freespace: 845.05. Freespace stat info: nblocks: 387, approximate_table_size: 3044529 (Bytes), approximate expansion rate: 1.04. (1 row) -- Query information about a single partition in a local partitioned index. Create a table: drop table if exists t; create table t(a int) with (storage_type=ustore) partition by range(a) (partition p1 values less than(3000), partition p2 values less than(maxvalue));; create index on t(a) local; insert into t values(generate_series(1, 10000)); Update statistics: vacuum analyze t; Query the OID: gaussdb=# select oid from pg_class where relname='t_a_idx'; oid ------- 16814 (1 row) gaussdb=# select oid, relname, parttype from pg_partition; oid | relname | parttype -------+----------+---------- 16811 | t | r 16812 | p1 | p 16813 | p2 | p 16815 | p1_a_idx | x 16816 | p2_a_idx | x (5 rows) Estimate bloat rate: gaussdb=# select * from gs_stat_freespace(16814, 16815, 'i', 0, false); oid | info -------+----------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------- 16815 | live_tuples: 10000, dead_tuples: 0, origin_avg_tuple_len 4, max_align_avg_tuple_len: 8, tuple_header_size: 16, rowptr_size: 40056, page_header _size: 24, nblocks: 14, approximate_table_size: 281400 (Bytes), approximate expansion rate: 0.41 (1 row) Sampling information: gaussdb=# select * from gs_stat_freespace(16814, 16815, 'i', 80, false); oid | info -------+----------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------- 16815 | Average page stat info: page_header_size: 24, tuple has storage: 215.93, normal tuple: 215.93, dead tuple: 0.00, unused tuple: 0.50, redirecte d tuple: 0.00, td_count: 0.00, potential_freespace: 0.00, actual_freespace: 2087.43. Freespace stat info: nblocks: 14, approximate_table_size: 91476 (B ytes), approximate expansion rate: 1.25. (1 row) -- Query information about all partitions in a local partitioned index. Create a table: drop table if exists t; create table t(a int) with (storage_type=ustore) partition by range(a) (partition p1 values less than(3000), partition p2 values less than(maxvalue));; create index on t(a) local; insert into t values(generate_series(1, 10000)); Update statistics: vacuum analyze t; Query the OID: gaussdb=# select oid from pg_class where relname='t_a_idx'; oid ------- 16814 (1 row) Estimate bloat rate: gaussdb=# select * from gs_stat_freespace(16814, 0, 'i', 0, false); oid | info -------+----------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------- 16815 | live_tuples: 10000, dead_tuples: 0, origin_avg_tuple_len 4, max_align_avg_tuple_len: 8, tuple_header_size: 16, rowptr_size: 40056, page_header _size: 24, nblocks: 14, approximate_table_size: 281400 (Bytes), approximate expansion rate: 0.41 16816 | live_tuples: 10000, dead_tuples: 0, origin_avg_tuple_len 4, max_align_avg_tuple_len: 8, tuple_header_size: 16, rowptr_size: 40116, page_header _size: 24, nblocks: 29, approximate_table_size: 282900 (Bytes), approximate expansion rate: 0.84 (2 rows) Sampling information: gaussdb=# select * from gs_stat_freespace(16814, 0, 'i', 80, false); oid | info -------+----------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------ 16815 | Average page stat info: page_header_size: 24, tuple has storage: 215.93, normal tuple: 215.93, dead tuple: 0.00, unused tuple: 0.50, redirecte d tuple: 0.00, td_count: 0.00, potential_freespace: 0.00, actual_freespace: 2087.43. Freespace stat info: nblocks: 14, approximate_table_size: 91476 (B ytes), approximate expansion rate: 1.25. 16816 | Average page stat info: page_header_size: 24, tuple has storage: 243.28, normal tuple: 243.28, dead tuple: 0.00, unused tuple: 0.24, redirecte d tuple: 0.00, td_count: 0.00, potential_freespace: 0.00, actual_freespace: 1323.03. Freespace stat info: nblocks: 29, approximate_table_size: 213237 ( Bytes), approximate expansion rate: 1.11. (2 rows) Abnormal input: gaussdb=# select * from gs_stat_freespace(16814, 0, 'k', 0, false); ERROR: wrong relation_type input, type should be either 'r' or 'i' gaussdb=# select * from gs_stat_freespace(16814, 0, 'i', 600, false); ERROR: wrong sample_ratio input, sample_ratio should be an integer between 0 and 100 gaussdb=# select * from gs_stat_freespace(16814, 0, 'r', 60, false); ERROR: "t_a_idx" is not a ordinary relation or toast relation.
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
