Redrock Postgres Documentation
Home Toggle Dark/Light/Auto mode Toggle Dark/Light/Auto mode Toggle Dark/Light/Auto mode Back to homepage

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?