old_snapshot

Overview

Version

Install

Note: This is a built-in contrib extension of PostgreSQL

CREATE EXTENSION old_snapshot;

Usage

old_snapshot: utilities in support of old_snapshot_threshold

The old_snapshot extension provides inspection functions for the server state related to the old_snapshot_threshold configuration parameter.

Note: this chapter is removed from PostgreSQL current docs in newer releases; use versioned docs when needed.

Function

-- View the timestamp-to-XID mapping table
SELECT * FROM pg_old_snapshot_time_mapping();

Function Signature

pg_old_snapshot_time_mapping(
    array_offset OUT int4,
    end_timestamp OUT timestamptz,
    newest_xmin OUT xid
) RETURNS SETOF record

Output Columns

Context

PostgreSQL’s old_snapshot_threshold parameter controls how long a snapshot can remain valid. The server maintains an internal mapping of timestamps to transaction IDs to implement this feature. This extension exposes that mapping for inspection and debugging.

-- Check the old_snapshot_threshold setting
SHOW old_snapshot_threshold;

-- Inspect the current mapping entries
SELECT array_offset, end_timestamp, newest_xmin
FROM pg_old_snapshot_time_mapping()
ORDER BY array_offset;