# This is from: http://wiki.postgresql.org/wiki/SSI#Intersecting_Data
use DBI;
use Data::Dumper;

$one = DBI->connect('dbi:Pg:dbname=template1;host=localhost', '', '', {AutoCommit => 1, RaiseError => 1});
$two = DBI->connect('dbi:Pg:dbname=template1;host=localhost', '', '', {AutoCommit => 1, RaiseError => 1});
$three = DBI->connect('dbi:Pg:dbname=template1;host=localhost', '', '', {AutoCommit => 1, RaiseError => 1});

# Setup...
$one->do("DROP TABLE IF EXISTS mytab");
$one->do("
CREATE TABLE mytab
(
  class int NOT NULL,
  value int NOT NULL
);
INSERT INTO mytab VALUES
(1, 10), (1, 20), (2, 100), (2, 200);
");

$one->do("BEGIN TRANSACTION");
($snap) = $one->selectrow_array('SELECT pg_export_snapshot()');


$two->do("BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE");
$two->do("SELECT SUM(value) FROM mytab WHERE class = 2");
$two->do("INSERT INTO mytab VALUES (1, 300);");

$three->do("BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE");

# Uncomment to make it succeed
$three->do("SET TRANSACTION SNAPSHOT '$snap'");

my $rv;
$rv = $two->do("COMMIT");
print "Nr. 2 commit: $rv\n";

$three->do("SELECT SUM(value) FROM mytab WHERE class = 1;");
$three->do("INSERT INTO mytab VALUES (2, 30);");

$rv = $three->do("COMMIT");
print "Nr. 3 commit: $rv\n";