sqlpostgresqltransactionsisolation-level

Why transaction snapshots doesn't work on postgresql?

Here is my algorithm:

begin first transaction with isolation level repeatable read

insert one row inside table

select row count from table - result: 1

select pg_export_snapshot();

begin second transaction with isolation level repeatable read

set transaction snapshot 'export result';

select row count from table - result: 0

What am I doing wrong?

enter image description here

Solution

  • Quote from the manual

    But note that any database changes made by any one of these transactions remain invisible to the other transactions, as is usual for changes made by uncommitted transactions. So the transactions are synchronized with respect to pre-existing data, but act normally for changes they make themselves.

    So even when changing to an exported transaction snapshot, you still can't get a dirty read (=read uncommitted data)