System Information Functions

Redrock Postgres provides several functions that extract session and system information.

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

Table 1. Transaction ID and Snapshot Information Functions

Function	Description
pg_current_xact_id () → xid	Returns the current transaction’s ID. It will assign a new one if the current transaction does not have one already (because it has not performed any database updates).
pg_current_xact_id_if_assigned () → xid	Returns the current transaction’s ID, or `NULL` if no ID is assigned yet. (It’s best to use this variant if the transaction might otherwise be read-only, to avoid unnecessary consumption of an XID.)
pg_xact_status ( xid ) → text	Reports the commit status of a recent transaction. The result is one of `in progress`, `committed`, or `aborted`, provided that the transaction is recent enough that the system retains the commit status of that transaction. If it is old enough that no references to the transaction survive in the system and the commit status information has been discarded, the result is `NULL`. Applications might use this function, for example, to determine whether their transaction committed or aborted after the application and database server become disconnected while a `COMMIT` is in progress. Note that prepared transactions are reported as `prepared`; applications could check `pg_prepared_xacts` if they need to determine whether a transaction ID belongs to a prepared transaction.
pg_current_logicaltime () → logicaltime	Returns the latest `logicaltime` in database system.
pg_current_snapshot () → logicaltime	Returns the `logicaltime` of current snapshot. All earlier transactions with commit `logicaltime` less than current snapshot `logicaltime` will either be committed and visible, or rolled back and dead.

A transaction ID represents a transaction and the data type xid stores information about the undo segment number, transaction slot number, and sequence number. A logical timestamp is an integer value generated in a monotonically increasing order.

The functions shown in Table 2 provide information about when past transactions were committed. They provide useful data only for transactions that were committed.

Table 2. Committed Transaction Information Functions

Function	Description
pg_xact_commit_timestamp ( xid ) → timestamp with time zone	Returns the commit `timestamp` of a transaction.
pg_xact_commit_logicaltime ( xid ) → logicaltime	Returns the commit `logicaltime` of a transaction.

The functions shown in Table 3 print information initialized during initdb, such as the catalog version. They also show information about write-ahead logging and checkpoint processing. This information is instance-wide, not specific to any one database. These functions provide most of the same information, from the same source, as the pg_controldata application.

Table 3. Control Data Functions

Function	Description
pg_control_checkpoint () → record	Returns information about current checkpoint state, as shown in Table 4.
pg_control_system () → record	Returns information about current control file state, as shown in Table 5.
pg_control_init () → record	Returns information about instance initialization state, as shown in Table 6.
pg_control_recovery () → record	Returns information about recovery state, as shown in Table 7.

pg_control_checkpoint returns a record, shown in Table 4

Table 4. pg_control_checkpoint Output Columns

Column Name	Data Type	Description
`checkpoint_lsn`	`pg_lsn`	Latest checkpoint location.
`redo_lsn`	`pg_lsn`	Latest checkpoint’s REDO location.
`redo_wal_file`	`text`	Latest checkpoint’s REDO WAL file.
`timeline_id`	`integer`	Latest checkpoint’s TimeLineID.
`prev_timeline_id`	`integer`	Latest checkpoint’s PrevTimeLineID.
`full_page_writes`	`boolean`	Latest checkpoint’s full_page_writes.
`next_time`	`logicaltime`	Next logical timestamp in latest checkpoint.
`next_oid`	`oid`	Next object number in latest checkpoint.
`checkpoint_time`	`timestamp with time zone`	Time of latest checkpoint.

pg_control_system returns a record, shown in Table 5

Table 5. pg_control_system Output Columns

Column Name	Data Type	Description
`pg_control_version`	`integer`	pg_control version number.
`catalog_version_no`	`integer`	Catalog version number.
`system_identifier`	`bigint`	Database system identifier.
`pg_control_last_modified`	`timestamp with time zone`	pg_control last modified timestamp.

pg_control_init returns a record, shown in Table 6

Table 6. pg_control_init Output Columns

Column Name	Data Type	Description
`max_data_alignment`	`integer`	Maximum data alignment.
`database_block_size`	`integer`	Database block size.
`blocks_per_segment`	`integer`	Blocks per segment of large relation.
`wal_block_size`	`integer`	WAL block size.
`bytes_per_wal_segment`	`integer`	Bytes per WAL segment.
`max_identifier_length`	`integer`	Maximum length of identifiers.
`max_index_columns`	`integer`	Maximum columns in an index.
`max_toast_chunk_size`	`integer`	Maximum size of a TOAST chunk.
`large_object_chunk_size`	`integer`	Size of a large-object chunk.
`float4_pass_by_value`	`boolean`	Float4 argument passing by value or reference.
`float8_pass_by_value`	`boolean`	Float8 argument passing by value or reference.
`data_page_checksum_version`	`integer`	Data page checksum version.

pg_control_recovery returns a record, shown in Table 7

Table 7. pg_control_recovery Output Columns

Column Name	Data Type	Description
`min_recovery_end_lsn`	`pg_lsn`	Minimum recovery ending location.
`min_recovery_end_timeline`	`integer`	Minimum recovery ending location’s timeline.
`backup_start_lsn`	`pg_lsn`	Backup start location.
`backup_end_lsn`	`pg_lsn`	Backup end location.
`end_of_backup_record_required`	`boolean`	End-of-backup record required?