old_snapshot

utilities in support of old_snapshot_threshold

old_snapshot : utilities in support of old_snapshot_threshold

Overview

ID	Extension	Package	Version	Category	License	Language
5960	old_snapshot	old_snapshot	`1.0`	ADMIN	PostgreSQL	C

Attribute	Has Binary	Has Library	Need Load	Has DDL	Relocatable	Trusted
--s-d--	No	Yes	No	Yes	no	no

Relationships
See Also	pg_dirtyread pg_visibility temporal_tables pgstattuple

Packages

PG18	PG17	PG16	PG15	PG14
N/A	N/A	1.0	1.0	1.0

This is a built-in contrib extension ship with the PostgreSQL kernel

Install

Create this extension with:

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

Column	Type	Description
`array_offset`	int4	Index position in the mapping array
`end_timestamp`	timestamptz	End of the corresponding one-minute interval
`newest_xmin`	xid	Newest xmin of any snapshot taken during that minute

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;