Transaction IDs and Snapshots

Internal transaction IDs (XIDs) are 64 bits. txid_snapshot, the data type used by these functions, stores information about transaction ID visibility at a particular moment. Table 12 describes its components.

Table 12 Snapshot components

Name	Description
xmin	Earliest transaction ID (txid) that is still active. All earlier transactions will either be committed and visible, or rolled back.
xmax	First as-yet-unassigned txid. All txids greater than or equal to this are not yet started as of the time of the snapshot, so they are invisible.
xip_list	Active txids at the time of the snapshot. The list includes only those active txids between xmin and xmax; there might be active txids higher than xmax. A txid that is xmin <= txid < xmax and not in this list was already completed at the time of the snapshot, and is either visible or dead according to its commit status. The list does not include txids of subtransactions.

txid_snapshot's textual representation is xmin:xmax:xip_list.

For example, 10:20:10,14,15 means xmin=10, xmax=20, xip_list=10, 14, 15.

The following functions provide server transaction information in an exportable form. The main use of these functions is to determine which transactions were committed between two snapshots.

• txid_current()

  Description: Obtains the current transaction ID.

  Return type: bigint

• gs_txid_oldestxmin()

  Description: Obtains the minimum transaction ID (specified by oldesxmin).

  Return type: bigint

• txid_current_snapshot()

  Description: Obtains the current snapshot.

  Return type: txid_snapshot

• txid_snapshot_xip(txid_snapshot)

  Description: Obtains in-progress transaction IDs in a snapshot.

  Return type: SETOF bigint

• txid_snapshot_xmax(txid_snapshot)

  Description: Obtains xmax of snapshots.

  Return type: bigint

• txid_snapshot_xmin(txid_snapshot)

  Description: Obtains xmin of snapshots.

  Return type: bigint

• txid_visible_in_snapshot(bigint, txid_snapshot)

  Description: Specifies whether the transaction ID is visible in a snapshot (do not use subtransaction IDs).

  Return type: Boolean

• get_local_prepared_xact()

  Description: Obtains the two-phase residual transaction information of the current node, including the transaction ID, GID of the two-phase transaction, prepared time, owner OID, database OID, and node name of the current node.

  Return type: xid, text, timestamptz, oid, oid, text

• get_remote_prepared_xacts()

  Description: Obtains the two-phase residual transaction information of all remote nodes, including the transaction ID, GID of the two-phase transaction, prepared time, owner name, database name, and node name.

  Return type: xid, text, timestamptz, name, name, text

• global_clean_prepared_xacts(text, text)

  Description: Concurrently cleans two-phase residual transactions. Only the gs_clean tool can call this function for the cleaning. In other situations, false is returned.

  Return type: Boolean

• gs_get_next_xid_csn()

  Description: Returns the values of next_xid and next_csn on all nodes globally.

  The return values are as follows:

  Table 13 gs_get_next_xid_csn parameters

  Column	Description
  nodename	Node name.
  next_xid	Next transaction ID of the current node.
  next_csn	Next CSN of the current node.

• slice(hstore, text[])

  Description: Extracts the subset of the hstore type.

  Return type: hstore

  Example:

  MogDB=# select slice('a=>1,b=>2,c=>3'::hstore, ARRAY['b','c','x']);
         slice
  --------------------
   "b"=>"2", "c"=>"3"
  (1 row)

• slice_array(hstore, text[])

  Description: Extracts the set of hstore values.

  Return type: value array

  Example:

  MogDB=# select slice_array('a=>1,b=>2,c=>3'::hstore, ARRAY['b','c','x']);
   slice_array
  -------------
   {2,3,NULL}
  (1 row)

• skeys(hstore)

  Description: Returns a set of all keys of the hstore type.

  Return type: a set of keys

  Example:

  MogDB=# select skeys('a=>1,b=>2');
   skeys
  -------
   a
   b
  (2 rows)

• 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

• pv_builtin_functions

  Description: Displays information about all built-in system functions.

  Parameter: nan

  Return type: proname name, pronamespace oid, proowner oid, prolang oid, procost real, prorows real, provariadic oid, protransform regproc, proisagg boolean, proiswindow boolean, prosecdef boolean, proleakproof boolean, proisstrict boolean, proretset boolean, provolatile "char", pronargs smallint, pronargdefaults smallint, prorettype oid, proargtypes oidvector, proallargtypes integer[], proargmodes "char"[], proargnames text[], proargdefaults pg_node_tree, prosrc text, probin text, proconfig text[], proacl aclitem[], prodefaultargpos int2vector, fencedmode boolean, proshippable boolean, propackage boolean, oid oid

• pv_thread_memory_detail

  Description: Returns the memory information of each thread.

  Parameter: nan

  Return type: threadid text, tid bigint, thrdtype text, contextname text, level smallint, parent text, totalsize bigint, freesize bigint, usedsize bigint

• 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: Specifies whether a table is compressed.

  Parameter: text

  Return type: Boolean

• pg_stat_file_recursive

  Description: Lists all files in a path.

  Parameter: location text

• pg_shared_memory_detail

  Description: Returns usage information about all generated shared memory contexts. For details about each column, see GS_SHARED_MEMORY_DETAIL.

  Parameter: nan

  Return type: contextname text, level smallint, parent text, totalsize bigint, freesize bigint, usedsize bigint

• get_gtm_lite_status

  Description: Returns the backup XID and CSN on the GTM for fault locating. This system function is not supported in GTM-FREE mode.

• gs_stat_get_wlm_plan_operator_info

  Description: Obtains operator plan information from the internal hash table.

  Parameter: oid

  Return type: datname text, queryid int8, plan_node_id int4, startup_time int8, total_time int8, actual_rows int8, max_peak_memory int4, query_dop int4, parent_node_id int4, left_child_id int4, right_child_id int4, operation text, orientation text, strategy text, options text, condition text, projection text

• pg_stat_get_partition_tuples_hot_updated

  Description: Returns statistics on the number of hot-updated tuples in a partition with a specified partition ID.

  Parameter: oid

  Return type: bigint

• gs_session_memory_detail_tp

  Description: Returns the memory usage of the session. For details, see gs_session_memory_detail.

  Parameter: nan

  Return type: sessid text, sesstype text, contextname text, level smallint, parent text, totalsize bigint, freesize bigint, usedsize bigint

• gs_thread_memory_detail

  Description: Returns the memory information of each thread.

  Parameter: nan

  Return type: threadid text, tid bigint, thrdtype text, contextname text, level smallint, parent text, totalsize bigint, freesize bigint, usedsize bigint

• pg_stat_get_wlm_realtime_operator_info

  Description: Obtains the operator information of the real-time execution plan from the internal hash table.

  Parameter: nan

  Return type: queryid bigint, pid bigint, plan_node_id integer, plan_node_name text, start_time timestamp with time zone, duration bigint, status text, query_dop integer, estimated_rows bigint, tuple_processed bigint, min_peak_memory integer, max_peak_memory integer, average_peak_memory integer, memory_skew_percent integer, min_spill_size integer, max_spill_size integer, average_spill_size integer, spill_skew_percent integer, min_cpu_time bigint, max_cpu_time bigint, total_cpu_time bigint, cpu_skew_percent integer, warning text

• pg_stat_get_wlm_realtime_ec_operator_info

  Description: Obtains the operator information of the EC execution plan from the internal hash table.

  Parameter: nan

  Return type: queryid bigint, plan_node_id integer, plan_node_name text, start_time timestamp with time zone, ec_operator integer, ec_status text, ec_execute_datanode text, ec_dsn text, ec_username text, ec_query text, ec_libodbc_type text, ec_fetch_count bigint

• pg_stat_get_wlm_operator_info

  Description: Obtains the operator information of the execution plan from the internal hash table.

  Parameter: nan

  Return type: queryid bigint, pid bigint, plan_node_id integer, plan_node_name text, start_time timestamp with time zone, duration bigint, query_dop integer, estimated_rows bigint, tuple_processed bigint, min_peak_memory integer, max_peak_memory integer, average_peak_memory integer, memory_skew_percent integer, min_spill_size integer, max_spill_size integer, average_spill_size integer, spill_skew_percent integer, min_cpu_time bigint, max_cpu_time bigint, total_cpu_time bigint, cpu_skew_percent integer, warning text

• pg_stat_get_wlm_node_resource_info

  Description: Obtains the resource information of the current node.

  Parameter: nan

  Return type: min_mem_util integer, max_mem_util integer, min_cpu_util integer, max_cpu_util integer, min_io_util integer, max_io_util integer, used_mem_rate integer

• pg_stat_get_session_wlmstat

  Description: Returns the load information of the current session.

  Parameter: pid integer

  Return type: datid oid, threadid bigint, sessionid bigint, threadpid integer, usesysid oid, appname text, query text, priority bigint, block_time bigint, elapsed_time bigint, total_cpu_time bigint, skew_percent integer, statement_mem integer, active_points integer, dop_value integer, current_cgroup text, current_status text, enqueue_state text, attribute text, is_plana boolean, node_group text, srespool name

• pg_stat_get_wlm_ec_operator_info

  Description: Obtains the operator information of the EC execution plan from the internal hash table.

  Parameter: nan

  Return type: queryid bigint, plan_node_id integer, plan_node_name text, start_time timestamp with time zone, duration bigint, tuple_processed bigint, min_peak_memory integer, max_peak_memory integer, average_peak_memory integer, ec_operator integer, ec_status text, ec_execute_datanode text, ec_dsn text, ec_username text, ec_query text, ec_libodbc_type text, ec_fetch_count bigint

• pg_stat_get_wlm_instance_info

  Description: Returns the load information of the current instance.

  Parameter: nan

  Return type: instancename text, timestamp, timestamp with time zone, used_cpu integer, free_memory integer, used_memory integer, io_await double precision, io_util double precision, disk_read double precision, disk_write double precision, process_read bigint, process_write bigint, logical_read bigint, logical_write bigint, read_counts bigint, write_counts bigint

• pg_stat_get_wlm_instance_info_with_cleanup

  Description: Returns the load information of the current instance and saves the information to the system catalog.

  Parameter: nan

  Return type: instancename text, timestamp, timestamp with time zone, used_cpu integer, free_memory integer, used_memory integer, io_await double precision, io_util double precision, disk_read double precision, disk_write double precision, process_read bigint, process_write bigint, logical_read bigint, logical_write bigint, read_counts bigint, write_counts bigint

• pg_stat_get_wlm_realtime_session_info

  Description: Returns the load information of the real-time session.

  Parameter: nan

  Return type: nodename text, threadid bigint, block_time bigint, duration bigint, estimate_total_time bigint, estimate_left_time bigint, schemaname text, query_band text, spill_info text, control_group text, estimate_memory integer, min_peak_memory integer, max_peak_memory integer, average_peak_memory integer, memory_skew_percent integer, min_spill_size integer, max_spill_size integer, average_spill_size integer, spill_skew_percent integer, min_dn_time bigint, max_dn_time bigint, average_dn_time bigint, dntime_skew_percent integer, min_cpu_time bigint, max_cpu_time bigint, total_cpu_time bigint, cpu_skew_percent integer, min_peak_iops integer, max_peak_iops integer, average_peak_iops integer, iops_skew_percent integer, warning text, query text, query_plan text, cpu_top1_node_name text, cpu_top2_node_name text, cpu_top3_node_name text, cpu_top4_node_name text, cpu_top5_node_name text, mem_top1_node_name text, mem_top2_node_name text, mem_top3_node_name text, mem_top4_node_name text, mem_top5_node_name text, cpu_top1_value bigint, cpu_top2_value bigint, cpu_top3_value bigint, cpu_top4_value bigint, cpu_top5_value bigint, mem_top1_value bigint, mem_top2_value bigint, mem_top3_value bigint, mem_top4_value bigint, mem_top5_value bigint, top_mem_dn text, top_cpu_dn text

• pg_stat_get_wlm_session_iostat_info

  Description: Returns the session load I/O information.

  Parameter: nan

  Return type: threadid bigint, maxcurr_iops integer, mincurr_iops integer, maxpeak_iops integer, minpeak_iops integer, iops_limits integer, io_priority integer, curr_io_limits integer

• pg_stat_get_wlm_statistics

  Description: Returns session load statistics.

  Parameter: nan

  Return type: statement text, block_time bigint, elapsed_time bigint, total_cpu_time bigint, qualification_time bigint, skew_percent integer, control_group text, status text, action text