I love the PostgreSQL concurrency model. Have I mentioned that lately? Today, I want to tell you about snapshot exports.
You're probably used to the READ COMMITTED isolation level. (That's the default.) This mode means you can see any committed data, whether it was commmitted before the transaction started or while the transaction is in progress. It means that consecutive statements acting on the same table might see different records.
You can, of course, choose a higher isolation level if that's appropriate. REPEATABLE READ offers stronger guarantees: specifically, you can only see data committed before the start of your transaction. Any changes caused by other sessions will not be visible to you until your transaction is complete.
Past that is SERIALIZABLE. The mechanics of the associated predicate locking scheme are rather fascinating, but not terribly relevant here. By the way, if any of this is news to you, check out the transaction isolation documentation.
The point here is that each transaction has a different perspective of the database. Each transaction can see a snapshot of the data that was committed by previous transactions, along with all the yet-to-be-committed data created within the transaction itself.
The meaning of "previous transactions" depends on when you started your statement (in READ COMMITTED) or when you started your transaction (in REPEATABLE READ), but in either case, you don't get to choose. This can be a problem if you need to synchronize state, since there's no way to guarantee that different statements or transactions will start at exactly the same time.
As of 9.2, there's another option: you can tell PostgreSQL which snapshot you want to see.
In one session:
db=> begin;
BEGIN
db=> select pg_export_snapshot();
pg_export_snapshot
--------------------
000ED905-1
(1 row)
Now, in another session:
db=> begin isolation level repeatable read;
BEGIN
db=> set transaction snapshot '000ED905-1';
SET
This second transaction will see exactly the same data as the original transaction saw when pg_export_snapshot() returned. Instead of having PostgreSQL decide which "previous transactions" you want to see, you get to specify.
Also, you can export multiple snapshots from the same READ COMMITTED transaction and see (after the fact) what changed one moment to the next. Also, you can access those snapshots in any order. From any other connection. As many times as you want. Simultaneously.
Magic, right?
(Either that or it's just MVCC in action.)
Be aware that snapshots are still tied to the life cycle of their associated transaction, and giving them IDs doesn't change anything. Once the exporting transaction commits or rolls back, new transactions trying to access an exported snapshot will see:
db=> set transaction snapshot '000ED905-1';
ERROR: invalid snapshot identifier: "000ED905-1"
Transactions that have already SET TRANSACTION SNAPSHOT will continue to function properly; the resources necessary to use the snapshot are retained until it's no longer referenced by an active transaction, but the identifier goes away when the exporting transaction completes. (And yes, these child transactions can export their own snapshots, creating new IDs equivalent to the earlier IDs. Go nuts.)
pg_dump uses this feature if you specify --jobs=N. In this mode, it establishes a connection, exports the snapshot ID, then establishes N more connections to actually retrieve data. This gets you a 100% consistent dump of an active database, just like a single REPEATABLE READ transaction, even though it was retrieved using multiple connections.
Definitely magic.