1-- 2-- PREPARED TRANSACTIONS (two-phase commit) 3-- 4-- We can't readily test persistence of prepared xacts within the 5-- regression script framework, unfortunately. Note that a crash 6-- isn't really needed ... stopping and starting the postmaster would 7-- be enough, but we can't even do that here. 8 9 10-- create a simple table that we'll use in the tests 11CREATE TABLE pxtest1 (foobar VARCHAR(10)); 12 13INSERT INTO pxtest1 VALUES ('aaa'); 14 15 16-- Test PREPARE TRANSACTION 17BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; 18UPDATE pxtest1 SET foobar = 'bbb' WHERE foobar = 'aaa'; 19SELECT * FROM pxtest1; 20PREPARE TRANSACTION 'foo1'; 21 22SELECT * FROM pxtest1; 23 24-- Test pg_prepared_xacts system view 25SELECT gid FROM pg_prepared_xacts; 26 27-- Test ROLLBACK PREPARED 28ROLLBACK PREPARED 'foo1'; 29 30SELECT * FROM pxtest1; 31 32SELECT gid FROM pg_prepared_xacts; 33 34 35-- Test COMMIT PREPARED 36BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; 37INSERT INTO pxtest1 VALUES ('ddd'); 38SELECT * FROM pxtest1; 39PREPARE TRANSACTION 'foo2'; 40 41SELECT * FROM pxtest1; 42 43COMMIT PREPARED 'foo2'; 44 45SELECT * FROM pxtest1; 46 47-- Test duplicate gids 48BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; 49UPDATE pxtest1 SET foobar = 'eee' WHERE foobar = 'ddd'; 50SELECT * FROM pxtest1; 51PREPARE TRANSACTION 'foo3'; 52 53SELECT gid FROM pg_prepared_xacts; 54 55BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; 56INSERT INTO pxtest1 VALUES ('fff'); 57 58-- This should fail, because the gid foo3 is already in use 59PREPARE TRANSACTION 'foo3'; 60 61SELECT * FROM pxtest1; 62 63ROLLBACK PREPARED 'foo3'; 64 65SELECT * FROM pxtest1; 66 67-- Test serialization failure (SSI) 68BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; 69UPDATE pxtest1 SET foobar = 'eee' WHERE foobar = 'ddd'; 70SELECT * FROM pxtest1; 71PREPARE TRANSACTION 'foo4'; 72 73SELECT gid FROM pg_prepared_xacts; 74 75BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; 76SELECT * FROM pxtest1; 77 78-- This should fail, because the two transactions have a write-skew anomaly 79INSERT INTO pxtest1 VALUES ('fff'); 80PREPARE TRANSACTION 'foo5'; 81 82SELECT gid FROM pg_prepared_xacts; 83 84ROLLBACK PREPARED 'foo4'; 85 86SELECT gid FROM pg_prepared_xacts; 87 88-- Clean up 89DROP TABLE pxtest1; 90 91-- Test detection of session-level and xact-level locks on same object 92BEGIN; 93SELECT pg_advisory_lock(1); 94SELECT pg_advisory_xact_lock_shared(1); 95PREPARE TRANSACTION 'foo6'; -- fails 96 97-- Test subtransactions 98BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; 99 CREATE TABLE pxtest2 (a int); 100 INSERT INTO pxtest2 VALUES (1); 101 SAVEPOINT a; 102 INSERT INTO pxtest2 VALUES (2); 103 ROLLBACK TO a; 104 SAVEPOINT b; 105 INSERT INTO pxtest2 VALUES (3); 106PREPARE TRANSACTION 'regress-one'; 107 108CREATE TABLE pxtest3(fff int); 109 110-- Test shared invalidation 111BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; 112 DROP TABLE pxtest3; 113 CREATE TABLE pxtest4 (a int); 114 INSERT INTO pxtest4 VALUES (1); 115 INSERT INTO pxtest4 VALUES (2); 116 DECLARE foo CURSOR FOR SELECT * FROM pxtest4; 117 -- Fetch 1 tuple, keeping the cursor open 118 FETCH 1 FROM foo; 119PREPARE TRANSACTION 'regress-two'; 120 121-- No such cursor 122FETCH 1 FROM foo; 123 124-- Table doesn't exist, the creation hasn't been committed yet 125SELECT * FROM pxtest2; 126 127-- There should be two prepared transactions 128SELECT gid FROM pg_prepared_xacts; 129 130-- pxtest3 should be locked because of the pending DROP 131begin; 132lock table pxtest3 in access share mode nowait; 133rollback; 134 135-- Disconnect, we will continue testing in a different backend 136\c - 137 138-- There should still be two prepared transactions 139SELECT gid FROM pg_prepared_xacts; 140 141-- pxtest3 should still be locked because of the pending DROP 142begin; 143lock table pxtest3 in access share mode nowait; 144rollback; 145 146-- Commit table creation 147COMMIT PREPARED 'regress-one'; 148\d pxtest2 149SELECT * FROM pxtest2; 150 151-- There should be one prepared transaction 152SELECT gid FROM pg_prepared_xacts; 153 154-- Commit table drop 155COMMIT PREPARED 'regress-two'; 156SELECT * FROM pxtest3; 157 158-- There should be no prepared transactions 159SELECT gid FROM pg_prepared_xacts; 160 161-- Clean up 162DROP TABLE pxtest2; 163DROP TABLE pxtest3; -- will still be there if prepared xacts are disabled 164DROP TABLE pxtest4; 165