Help Center/ GaussDB/ Developer Guide(Distributed_3.x)/ SQL Reference/ Functions and Operators/ System Administration Functions/ Undo System Functions

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

View PDF

Undo System Functions

The Undo system function can be executed only on DNs.

gs_undo_meta(type, zoneId, location)

Description: Specifies metadata of a module in the undo system.

Parameter description:

type (metadata type)
- 0: indicates metadata corresponding to an undo zone (record).
- 1: indicates metadata corresponding to an undo zone(transaction slot).
- 2: indicates metadata corresponding to an undo space (record).
- 3: indicates metadata corresponding to an undo space(transaction slot).
zoneId (undo zone ID)
- –1: indicates metadata of all undo zones.
- 0 to 1024 x 1024 – 1: indicates the meta information of the corresponding zone ID.
location (read location)
- 0: indicates that data is read from the current memory.
- 1: indicates that data is read from a physical file.

Return type: record

**Table 1** gs_undo_meta parameters
Category	Parameter	Type	Description
Output parameter	zoneId	oid	ID of an undo zone.
Output parameter	persistType	oid	Persistence level.
Output parameter	insert	text	Position of the next undo record to be inserted.
Output parameter	discard	text	Position of the undo record that is recycled in common mode.
Output parameter	end	text	Position of the undo record that is forcibly recycled. Values smaller than the value of this parameter indicate that the record has been recycled.
Output parameter	used	text	Undo space that has been used.
Output parameter	lsn	text	Modifies the LSN of an undo zone.
Output parameter	pid	oid	ID of a process bound to an undo zone.

gs_undo_translot(location, zoneId)

Description: Specifies transaction slot information of the undo system.

Parameter description:

location (read location)
- 0: indicates that data is read from the current memory.
- 1: indicates that data is read from a physical file.
zoneId (undo zone ID)
- –1: indicates metadata of all undo zones.
- 0 to 1024 x 1024 – 1: indicates the meta information of the corresponding zone ID.

Return type: record

**Table 2** gs_undo_translot parameters
Category	Parameter	Type	Description
Output parameter	groupId	oid	Undo zone ID that is used.
Output parameter	xactId	text	Transaction ID.
Output parameter	startUndoPtr	text	Position where an undo record is inserted at the start of a transaction corresponding to a transaction slot.
Output parameter	endUndoPtr	text	Position where an undo record is inserted at the end of a transaction corresponding to a transaction slot.
Output parameter	lsn	text	Transaction slot pointer.
Output parameter	slot_states	oid	Transaction status. 0: committed. 1: being executed. 2: being rolled back. 3: rolled back.

gs_stat_undo([bool init])

Description: Collects undo statistics.

Return type: record

**Table 3** gs_stat_undo parameters
Category	Parameter	Type	Description
Input parameter	init	bool	(Optional) Specifies whether to clear statistics and restart statistics collection.
Output parameter	curr_used_zone_count	uint32	Number of used undo zones.
Output parameter	top_used_zones	text	Information about the first three undo zones with the maximum usage. The output format is as follows: zoneId1: used size of zone 1. zoneId2: used size of zone 2. zoneId3: used size of zone 3.
Output parameter	curr_used_undo_size	uint32	Total size of the undo tablespace that is being used. The unit is MB.
Output parameter	undo_threshold	uint32	Calculation result of the value of the GUC parameter undo_space_limit_size x 80%. The unit is MB.
Output parameter	global_recycle_xid	uint64	XID of the transaction recycled to the undo space. The undo records generated by the transaction whose XID is smaller than the value of XID are recycled.
Output parameter	oldest_xmin	uint64	Oldest active transaction.
Output parameter	total_undo_chain_len	int64	Total length of all accessed undo chains.
Output parameter	max_undo_chain_len	int64	Maximum length of the accessed undo chain.
Output parameter	create_undo_file_count	uint32	Number of created undo files.
Output parameter	discard_undo_file_count	uint32	Number of deleted undo files.
Output parameter	info	text	If the input parameter is false, suggestions on the undo_space_limit_size, undo_limit_size_per_transaction, and undo_retention_time parameters are provided. If the input parameter is true, init (clearing statistics) is required, and only 'The statistics have been initialized.' is displayed.

Example 1: Clear undo statistics.

gaussdb=# select * from gs_stat_undo(true);
curr_used_zone_count |   top_used_zones    | curr_used_undo_size | undo_threshold | global_recycle_xid | oldest_xmin | total_undo_chain_len | max_undo_chain_len | create_undo_file_coun
t | discard_undo_file_count |                 info
----------------------+---------------------+---------------------+----------------+--------------------+-------------+----------------------+--------------------+----------------------
--+-------------------------+--------------------------------------
                    3 | 0 : 0, 0 : 0, 0 : 0 |                   1 |         209715 |              15741 |       15741 |                    0 |                  0 |
2 |                       0 | The statistics have been initialized.
(1 row)

Example 2: Output undo statistics.

gaussdb=# select * from gs_stat_undo(false);
curr_used_zone_count |   top_used_zones    | curr_used_undo_size | undo_threshold | global_recycle_xid | oldest_xmin | total_undo_chain_len | max_undo_chain_len | create_undo_file_coun
t | discard_undo_file_count |
             info

----------------------+---------------------+---------------------+----------------+--------------------+-------------+----------------------+--------------------+----------------------
--+-------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-
                    3 | 0 : 0, 0 : 0, 0 : 0 |                   1 |         209715 |              16253 |       16253 |                    0 |                  0 |
2 |                       0 | Based on the statistic info from last initialization, undo_space_limit_size is recommended to set >= 120953 blocks, undo_limit_size_per_transaction is
recommended to set <= 327150 blocks, undo_retention_time is recommended to set <= 259200 seconds. Since last initialization, max undo space used by a single transaction is 32715 blocks.
(1 row)

gs_undo_record(undoptr)
Description: Parses undo records.

Parameter description: undoptr (undo record pointer)

Return type: record

gs_undo_dump_parsepage_mv(relpath text, blkno bigint, reltype text, rmem boolean)

Description: Parses the page header information of the disk page in the Ustore table, header information of each tuple, flag bit information, and all historical undo version information that can be queried.

Return type: text

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

Currently, this API supports only Ustore tables.

**Table 4** gs_undo_dump_parsepage_mv parameters
Category	Parameter	Type	Description
Input parameter	relpath	text	Relative path of the Ustore table data file, in the format of Tablespace name/Database OID/relfilenode, for example, base/16603/16384. You can run the pg_relation_filepath('tablename') command to query the relative path.
Input parameter	blkno	bigint	–1: All block pages are parsed. 0–MaxBlocNumber: A specified block page is parsed.
Input parameter	reltype	text	Table type. Currently, only the Ustore table is supported. The value is uheap.
Input parameter	rmem	Boolean	false true Currently, the value can only be false, indicating that the corresponding page is parsed from the disk file.
Output parameter	output	text	Absolute path of the parsing result file.

gs_undo_meta_dump_zone(zone_id int, read_memory boolean)

Description: Parses undo zone metadata in an undo module.

Return type: record

**Table 5** gs_undo_meta_dump_zone parameters
Category	Parameter	Type	Description
Input parameter	zone_id	int	Undo zone ID. –1: All undo zones are queried. 0–1048575: The undo zone metadata is queried based on the zone ID.
Input parameter	read_memory	Boolean	true: Data is read from the current memory. false: Data is read from the physical file.
Output parameter	zone_id	int	Undo zone ID.
Output parameter	persist_type	int	Persistence level. 0: ordinary table 1: unlogged table 2: temporary table
Output parameter	insert	text	Position of the next undo record to be inserted.
Output parameter	discard	text	Position of the undo record that is recycled in common mode.
Output parameter	forcediscard	text	Position of the undo record that is forcibly recycled. Values smaller than the value of this parameter indicate that the record has been recycled.
Output parameter	lsn	text	Modifies the LSN of a zone.

gs_undo_meta_dump_spaces(zone_id int, read_memory boolean)

Description: Parses metadata of an undo record space and a transaction slot space in an undo module.

Return type: record

**Table 6** gs_undo_meta_dump_spaces parameters
Category	Parameter	Type	Description
Input parameter	zone_id	int	Undo zone ID. –1: All undo zones are queried. 0–1048575: The undo zone metadata is queried based on the zone ID.
Input parameter	read_memory	Boolean	true: Data is read from the current memory. false: Data is read from the physical file.
Output parameter	zone_id	int	Undo zone ID.
Output parameter	undorecord_space_tail	text	End position of the undo record space.
Output parameter	undorecord_space_head	text	Start position of the undo record space.
Output parameter	undorecord_space_lsn	text	Modifies the LSN of an undo record space.
Output parameter	undoslot_space_tail	text	End position of a transaction slot space.
Output parameter	undoslot_space_head	text	Start position of a transaction slot space.
Output parameter	undoreslot_space_lsn	text	Modifies the LSN of an undo slot space.

gs_undo_meta_dump_slot(zone_id int, read_memory boolean)

Description: Parses transaction slot metadata in an undo module.

Return type: record

**Table 7** gs_undo_meta_dump_slot parameters
Category	Parameter	Type	Description
Input parameter	zone_id	int	Undo zone ID. –1: All undo zones are queried. 0–1048575: The undo zone metadata is queried based on the zone ID.
Input parameter	read_memory	Boolean	true: Data is read from the current memory. false: Data is read from the physical file.
Output parameter	zone_id	int	Undo zone ID.
Output parameter	allocate	text	Allocation position of the undo transaction slot.
Output parameter	recycle	text	Recycling position of the undo transaction slot.
Output parameter	frozen_xid	text	Frozen XID, which is used to determine the visibility.
Output parameter	global_frozen_xid	text	Minimum frozen XID in the system. Transactions whose XID is smaller than the value of this parameter are visible.
Output parameter	recycle_xid	text	Recycled XID. Transactions whose XID is smaller than the value of this parameter are recycled.
Output parameter	global_recycle_xid	text	Minimum recycled XID in the system. Transactions whose XID is smaller than the value of this parameter are recycled.

gs_undo_translot_dump_slot(zone_id int, read_memory boolean)

Description: Parses a transaction slot in an undo zone.

Return type: record

**Table 8** gs_undo_translot_dump_slot parameters
Category	Parameter	Type	Description
Input parameter	zone_id	oid	Undo zone ID. –1: All undo zones are queried. 0–1048575: The undo zone metadata is queried based on the zone ID.
Input parameter	read_memory	Boolean	true: Data is read from the current memory. false: Data is read from the physical file.
Output parameter	zone_id	text	Undo zone ID.
Output parameter	slot_xid	text	Transaction ID.
Output parameter	start_undoptr	text	Position where an undo record is inserted at the start of a transaction corresponding to a transaction slot.
Output parameter	end_undoptr	text	Position where an undo record is inserted at the end of a transaction corresponding to a transaction slot.
Output parameter	slot_ptr	text	Position of a transaction slot.
Output parameter	slot_states	oid	Transaction state 0: committed 1: being executed 2: being rolled back 3: rolled back

gs_undo_translot_dump_xid(slot_xid xid, read_memory boolean)

Description: Parses a transaction slot in an undo zone based on the XID.

Return type: record

**Table 9** gs_undo_translot_dump_xid parameters
Category	Parameter	Type	Description
Input parameter	slot_xid	xid	Transaction ID to be queried.
Input parameter	read_memory	Boolean	true: Data is read from the current memory. false: Data is read from the physical file.
Output parameter	zone_id	text	Undo zone ID.
Output parameter	slot_xid	text	Transaction ID.
Output parameter	start_undoptr	text	Position where an undo record is inserted at the start of a transaction corresponding to a transaction slot.
Output parameter	end_undoptr	text	Position where an undo record is inserted at the end of a transaction corresponding to a transaction slot.
Output parameter	slot_ptr	text	Position of a transaction slot.
Output parameter	slot_states	oid	Transaction state 0: committed 1: being executed 2: being rolled back 3: rolled back

gs_undo_dump_record(undoptr bigint)

Description: Parses undo records.

Return type: record

**Table 10** gs_undo_dump_record parameters
Category	Parameter	Type	Description
Input parameter	undoptr	bigint	Start position of the undo record to be parsed.
Output parameter	undoptr	bigint	Start position of the undo record to be parsed.
Output parameter	xactid	text	Transaction ID.
Output parameter	cid	text	Command ID.
Output parameter	reloid	text	Relation OID.
Output parameter	relfilenode	text	Relfinode of the file.
Output parameter	utype	text	Undo record type.
Output parameter	blkprev	text	Position of the previous undo record in the same block.
Output parameter	blockno	text	Block number.
Output parameter	uoffset	text	Undo record offset.
Output parameter	prevurp	text	Position of the previous undo record.
Output parameter	payloadlen	text	Length of the undo record data.
Output parameter	oldxactid	text	Previous transaction ID.
Output parameter	partitionoid	text	Partition OID.
Output parameter	tablespace	text	Tablespace.
Output parameter	alreadyread_bytes	text	Length of the read undo record.
Output parameter	prev_undorec_len	text	Length of the previous undo record.
Output parameter	td_id	text	ID of the transaction directory.
Output parameter	reserved	text	Reserved flag of the old version tuple stored in an undo record.
Output parameter	flag	text	Status flag of the old version tuple stored in an undo record.
Output parameter	flag2	text	Number of old version tuple columns stored in an undo record.
Output parameter	t_hoff	text	Length of the undo record data header.

Example (If the undo record is not recycled, the input parameter undoptr can be queried after being converted to a decimal value using the output parameter end_undoptr of the gs_undo_translot_dump_slot function.):

gaussdb=# SELECT * FROM gs_undo_dump_record('0000000000000042');
 undoptr |   xactid   | cid | reloid | relfilenode | utype |  blkprev   | blockno | uoffset | prevurp | payloadlen | oldxactid | partitionoid | tablespace | alreadyread_byt
es | prev_undorec_len | td_id | reserved | flag | flag2 | t_hoff
---------+------------+-----+--------+-------------+-------+------------+---------+---------+---------+------------+-----------+--------------+------------+----------------
---+------------------+-------+----------+------+-------+--------
      42 | 1073807360 | 0   | 0      | 108986369   | 0     | 1024786474 | 0       | 0       | 0       | 0          | 0         | 0            | 0          | 36
   | 16390            | -1    | -1       | -1   | -1    | -1
(1 row)

gs_undo_dump_xid(undo_xid xid)

Description: Parses undo records based on the XID.

Return type: record

**Table 11** gs_undo_dump_xid parameters
Category	Parameter	Type	Description
Input parameter	undo_xid	xid	Transaction ID.
Output parameter	undoptr	xid	Start position of the undo record to be parsed.
Output parameter	xactid	text	Transaction ID.
Output parameter	cid	text	Command ID.
Output parameter	reloid	text	Relation OID.
Output parameter	relfilenode	text	Relfinode of the file.
Output parameter	utype	text	Undo record type.
Output parameter	blkprev	text	Position of the previous undo record in the same block.
Output parameter	blockno	text	Block number.
Output parameter	uoffset	text	Undo record offset.
Output parameter	prevurp	text	Position of the previous undo record.
Output parameter	payloadlen	text	Length of the undo record data.
Output parameter	oldxactid	text	Previous transaction ID.
Output parameter	partitionoid	text	Partition OID.
Output parameter	tablespace	text	Tablespace.
Output parameter	alreadyread_bytes	text	Length of the read undo record.
Output parameter	prev_undorec_len	text	Length of the previous undo record.
Output parameter	td_id	text	ID of the transaction directory.
Output parameter	reserved	text	Reserved flag of the old version tuple stored in an undo record.
Output parameter	flag	text	Status flag of the old version tuple stored in an undo record.
Output parameter	flag2	text	Number of old version tuple columns stored in an undo record.
Output parameter	t_hoff	text	Length of the undo record data header.

gs_verify_undo_record(type, start_idx, end_idx, location)

Description: Verifies undo records. Currently, only the disk verification mode is supported. Offline verification can be performed only when services are not running. Before the verification, you need to manually perform checkpoint flushing.

Return type: record

**Table 12** gs_verify_undo_record parameters
Category	Parameter	Type	Description
Input parameter	type	text	Verification type. 'urp': verifies all undo records in a specified URP range. 'zone': verifies all undo records of all zones in a specified zone range.
Input parameter	start_idx	int64	Start position. When type is set to 'urp', this parameter indicates the start position of undo records. When type is set to 'zone', this parameter indicates the start undo zone ID.
Input parameter	end_idx	int64	End position. When type is set to 'urp', this parameter indicates the end position of undo records. When type is set to 'zone', this parameter indicates the end undo zone ID.
Input parameter	location	bool	0: memory verification 1: disk verification Currently, this parameter can only be set to 1.
Output parameter	zone_id	int64	Undo zone ID.
Output parameter	detail	text	Verification error information.

Example 1: Verify the undo record whose URP is 24.

gaussdb=# select * from gs_verify_undo_record('urp', 24, 24, 1);
zone_id | detail
--------+--------
(0 rows)

Example 2: If the entered value of urp is incorrect, that is, the value is not the start position of the undo record, the system may return a message indicating that the verification result is empty or the verification fails.

gaussdb=# select * from gs_verify_undo_record('urp', 35184372173095, 35184372173095, 1);
 zone_id |                            detail
---------+---------------------------------------------------------------
       2 | verification failed: xid is invalid, urp:35184372173095.
(1 row)

Example 3: Verify all undo records from zone 0 to zone 2 on the disk.

gaussdb=# select * from gs_verify_undo_record('zone', 0, 2, 1);
zone_id | detail
--------+--------
(0 rows)

If an error is reported when this view is called, contact Huawei technical support.

gs_verify_undo_slot(type, start_idx, end_idx, location)

Description: Verifies undo transaction slots. Currently, only the disk verification mode is supported. Offline verification can be performed only when services are not running. Before the verification, you need to manually perform checkpoint flushing.

Return type: record

**Table 13** gs_verify_undo_slot parameters
Category	Parameter	Type	Description
Input parameter	type	text	Verification type. 'zone': verifies all transaction slots of all zones in a specified zone range.
Input parameter	start_idx	int64	Start undo zone ID.
Input parameter	end_idx	int64	End undo zone ID.
Input parameter	location	bool	0: memory verification 1: disk verification Currently, this parameter can only be set to 1.
Output parameter	zone_id	int64	Undo zone ID.
Output parameter	detail	text	Verification error information.

Example 1: Verify all transaction slot records from zone 0 to zone 2 on the disk.

gaussdb=# select * from gs_verify_undo_slot('zone', 0, 2, 1);
zone_id | detail
--------+--------
(0 rows)

If an error is reported when this view is called, contact Huawei technical support.

gs_verify_undo_meta(type, start_idx, end_idx, location)

Description: Verifies undo metadata. Currently, only the disk verification mode is supported. Offline verification can be performed only when services are not running. Before the verification, you need to manually perform checkpoint flushing.

Return type: record

**Table 14** gs_verify_undo_meta parameters
Category	Parameter	Type	Description
Input parameter	type	text	Verification type. The value of type can only be set to 'all'. 'all': verifies all meta information of all zones in a specified zone range.
Input parameter	start_idx	int64	Start undo zone ID.
Input parameter	end_idx	int64	End undo zone ID.
Input parameter	location	bool	0: memory verification 1: disk verification Currently, this parameter can only be set to 1.
Output parameter	zone_id	int64	Undo zone ID.
Output parameter	detail	text	Verification error information.

Example 1: Verify all meta information records from zone 0 to zone 2 on the disk.

gaussdb=# select * from gs_verify_undo_meta('all', 0, 2, 1);
zone_id | detail
--------+--------
(0 rows)

If an error is reported when this view is called, contact Huawei technical support.

gs_async_rollback_worker_status()

Description: Monitors the status of active asynchronous rollback threads.

Return type: record

**Table 15** gs_async_rollback_worker_status parameters
Category	Parameter	Type	Description
Output parameter	datid	oid	Database ID.
Output parameter	pid	int64	Process ID.
Output parameter	sessionid	int64	Session ID.
Output parameter	usesysid	oid	ID of the user who initiates the thread.
Output parameter	state	int32	Thread status. 0: undefined 1: idle 2: running
Output parameter	rollback_start_time	timestamptz	Timestamp when a thread is started.
Output parameter	idx	uint32	Index of an asynchronous rollback thread in the array.
Output parameter	xid	uint64	XID of the transaction that is being rolled back.
Output parameter	progress	text	Rollback progress of the transaction (number of undo records that have been rolled back/total number of undo records, in percentage).

gs_async_rollback_xact_status()

Description: Monitors the hash table of asynchronous rollback tasks.

Return type: record

**Table 16** gs_async_rollback_xact_status parameters
Category	Parameter	Type	Description
Output parameter	xid	xid	XID of the transaction that requires asynchronous rollback.
Output parameter	start_undoptr	uint64	Pointer to the start undo record of the transaction.
Output parameter	end_undoptr	uint64	Pointer to the end undo record of the transaction.
Output parameter	dbid	uint32	ID of the database where the transaction is located.
Output parameter	slot_ptr	uint64	Pointer to the transaction slot corresponding to the transaction.
Output parameter	launched	bool	Checks whether there is an active asynchronous rollback thread.

gs_undo_recycler_status()

Description: Monitors the status of asynchronous recycling threads.

Return type: record

**Table 17** gs_undo_recycler_status parameters
Category	Parameter	Type	Description
Output parameter	datid	oid	Database ID.
Output parameter	pid	int64	Process ID.
Output parameter	sessionid	int64	Session ID.
Output parameter	usesysid	oid	ID of the user who initiates the thread.
Output parameter	state	int32	Thread status. 0: undefined 1: idle 2: running
Output parameter	backend_start	timestamptz	Timestamp when a thread is started.
Output parameter	total_recycle_time	uint64	Total recycling time.
Output parameter	max_recycle_time	uint64	Maximum recycling time.
Output parameter	total_recycle_size	uint64	Total recycled space.
Output parameter	total_recycle_count	uint64	Total number of recycling times.
Output parameter	recycle_sleep_count	uint64	Number of sleep times.
Output parameter	recycle_sleep_time	uint64	Total sleep time.
Output parameter	max_recycle_sleep_time	uint64	Maximum sleep time.
Output parameter	last_recycle_timestamp	uint64	Timestamp of the last successful recycling.
Output parameter	last_update_global_recycle_xid_timestamp	uint64	Timestamp of the last global recycling transaction.

gs_undo_launcher_status()

Description: Monitors the status of asynchronous rollback launcher threads.

Return type: record

**Table 18** gs_undo_launcher_status parameters
Category	Parameter	Type	Description
Output parameter	datid	oid	Database ID.
Output parameter	pid	int64	Process ID.
Output parameter	sessionid	int64	Session ID.
Output parameter	usesysid	oid	ID of the user who initiates the thread.
Output parameter	state	int32	Thread status. 0: undefined 1: idle 2: running
Output parameter	backend_start	timestamptz	Timestamp when a thread is started.
Output parameter	total_async_rollback_task_count	uint64	Total number of asynchronous rollback tasks initiated after the database on the local node is started.
Output parameter	average_async_rollback_time	uint64	Average duration of an asynchronous rollback task.
Output parameter	max_async_rollback_time	uint64	Maximum duration of an asynchronous rollback task.
Output parameter	min_async_rollback_time	uint64	Minimum duration of an asynchronous rollback task.