CREATE TABLE pxtest1 (foobar VARCHAR(10));
INSERT INTO pxtest1 VALUES ('aaa');
-- Test PREPARE TRANSACTION
-BEGIN;
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE pxtest1 SET foobar = 'bbb' WHERE foobar = 'aaa';
SELECT * FROM pxtest1;
foobar
(0 rows)
-- Test COMMIT PREPARED
-BEGIN;
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO pxtest1 VALUES ('ddd');
SELECT * FROM pxtest1;
foobar
(2 rows)
-- Test duplicate gids
-BEGIN;
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE pxtest1 SET foobar = 'eee' WHERE foobar = 'ddd';
SELECT * FROM pxtest1;
foobar
foo3
(1 row)
-BEGIN;
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO pxtest1 VALUES ('fff');
+-- This should fail, because the gid foo3 is already in use
+PREPARE TRANSACTION 'foo3';
+ERROR: transaction identifier "foo3" is already in use
SELECT * FROM pxtest1;
foobar
--------
aaa
ddd
- fff
-(3 rows)
+(2 rows)
--- This should fail, because the gid foo3 is already in use
-PREPARE TRANSACTION 'foo3';
-ERROR: transaction identifier "foo3" is already in use
+ROLLBACK PREPARED 'foo3';
SELECT * FROM pxtest1;
foobar
--------
ddd
(2 rows)
-ROLLBACK PREPARED 'foo3';
+-- Test serialization failure (SSI)
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+UPDATE pxtest1 SET foobar = 'eee' WHERE foobar = 'ddd';
+SELECT * FROM pxtest1;
+ foobar
+--------
+ aaa
+ eee
+(2 rows)
+
+PREPARE TRANSACTION 'foo4';
+SELECT gid FROM pg_prepared_xacts;
+ gid
+------
+ foo4
+(1 row)
+
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM pxtest1;
foobar
--------
ddd
(2 rows)
+INSERT INTO pxtest1 VALUES ('fff');
+-- This should fail, because the two transactions have a write-skew anomaly
+PREPARE TRANSACTION 'foo5';
+ERROR: could not serialize access due to read/write dependencies among transactions
+DETAIL: Canceled on commit attempt with conflict in from prepared pivot.
+HINT: The transaction might succeed if retried.
+SELECT gid FROM pg_prepared_xacts;
+ gid
+------
+ foo4
+(1 row)
+
+ROLLBACK PREPARED 'foo4';
+SELECT gid FROM pg_prepared_xacts;
+ gid
+-----
+(0 rows)
+
-- Clean up
DROP TABLE pxtest1;
-- Test subtransactions
-BEGIN;
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
CREATE TABLE pxtest2 (a int);
INSERT INTO pxtest2 VALUES (1);
SAVEPOINT a;
PREPARE TRANSACTION 'regress-one';
CREATE TABLE pxtest3(fff int);
-- Test shared invalidation
-BEGIN;
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
DROP TABLE pxtest3;
CREATE TABLE pxtest4 (a int);
INSERT INTO pxtest4 VALUES (1);
-- Table doesn't exist, the creation hasn't been committed yet
SELECT * FROM pxtest2;
ERROR: relation "pxtest2" does not exist
+LINE 1: SELECT * FROM pxtest2;
+ ^
-- There should be two prepared transactions
SELECT gid FROM pg_prepared_xacts;
gid
(2 rows)
-- pxtest3 should be locked because of the pending DROP
-set statement_timeout to 1000;
+set statement_timeout to 2000;
SELECT * FROM pxtest3;
ERROR: canceling statement due to statement timeout
reset statement_timeout;
(2 rows)
-- pxtest3 should still be locked because of the pending DROP
-set statement_timeout to 1000;
+set statement_timeout to 2000;
SELECT * FROM pxtest3;
ERROR: canceling statement due to statement timeout
reset statement_timeout;
COMMIT PREPARED 'regress-two';
SELECT * FROM pxtest3;
ERROR: relation "pxtest3" does not exist
+LINE 1: SELECT * FROM pxtest3;
+ ^
-- There should be no prepared transactions
SELECT gid FROM pg_prepared_xacts;
gid
-- Clean up
DROP TABLE pxtest2;
+DROP TABLE pxtest3; -- will still be there if prepared xacts are disabled
+ERROR: table "pxtest3" does not exist
DROP TABLE pxtest4;