pg_export_snapshot()

A function for saving and exporting the current 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

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

Categories

System function, Transactions

See also

pg_current_snapshot(), pg_snapshot