pg_export_snapshot()
pg_export_snapshot() is a system function which saves the transaction's current snapshot and returns an identifier as a text value.
pg_export_snapshot() was added in PostgreSQL 9.2.
Usage
pg_export_snapshot () → text
An exported snapshot can be used by another session to read the database in exactly the same state as the originating session. This is useful for e.g. pg_dump, which can use exported snapshots to implement parallel dumping.
Multiple snapshots can be exported during a transaction, however note that only READ COMMITTED transactions actually use more than one snaphot during their lifetime.
Importing into another transaction
A snapshot which has been exported within a transaction can be imported into another transaction with SET TRANSACTION SNAPSHOT '...'.
Change history
- PostgreSQL 9.2
- added (commit bb446b68)
Examples
Basic usage example for pg_export_snapshot():
postgres=# SELECT pg_export_snapshot(); pg_export_snapshot --------------------- 00000003-00000011-1 (1 row)
Note that in the above example, as pg_export_snapshot() was executed outside of a transaction, the exported snapshot is already invalid.
Exporting a snapshot from within a transaction:
postgres=# BEGIN
TRANSACTION ISOLATION LEVEL
REPEATABLE READ;
postgres=*# SELECT pg_export_snapshot();
pg_export_snapshot
---------------------
00000003-000001BF-1
(1 row)
The snapshot can now be imported into another transaction:
postgres=# BEGIN
TRANSACTION ISOLATION LEVEL
REPEATABLE READ;
BEGIN
postgres=*# SET TRANSACTION SNAPSHOT '00000003-000001BF-1';
SET
It is not possible to export a snapshot from a subtransaction:
postgres=# BEGIN; BEGIN postgres=*# SAVEPOINT s1; SAVEPOINT postgres=*# SELECT pg_export_snapshot(); ERROR: cannot export a snapshot from a subtransaction
It is not possible to execue PREPARE TRANSACTION if a transaction's snapshot has been exported:
postgres=*# SELECT pg_export_snapshot(); pg_export_snapshot --------------------- 00000003-00000013-1 (1 row) postgres=*# PREPARE TRANSACTION 'foo'; ERROR: cannot PREPARE a transaction that has exported snapshots
References
- PostgreSQL documentation: Snapshot Synchronization Functions
Useful links
- Working With Snapshots in PostgreSQL - September 2022 blog article by Robert Bernier / Percona
