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