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-- create a simple table that we'll use in the tests 9CREATE TABLE pxtest1 (foobar VARCHAR(10)); 10INSERT INTO pxtest1 VALUES ('aaa'); 11-- Test PREPARE TRANSACTION 12BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; 13UPDATE pxtest1 SET foobar = 'bbb' WHERE foobar = 'aaa'; 14SELECT * FROM pxtest1; 15 foobar 16-------- 17 bbb 18(1 row) 19 20PREPARE TRANSACTION 'foo1'; 21ERROR: prepared transactions are disabled 22HINT: Set max_prepared_transactions to a nonzero value. 23SELECT * FROM pxtest1; 24 foobar 25-------- 26 aaa 27(1 row) 28 29-- Test pg_prepared_xacts system view 30SELECT gid FROM pg_prepared_xacts; 31 gid 32----- 33(0 rows) 34 35-- Test ROLLBACK PREPARED 36ROLLBACK PREPARED 'foo1'; 37ERROR: prepared transaction with identifier "foo1" does not exist 38SELECT * FROM pxtest1; 39 foobar 40-------- 41 aaa 42(1 row) 43 44SELECT gid FROM pg_prepared_xacts; 45 gid 46----- 47(0 rows) 48 49-- Test COMMIT PREPARED 50BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; 51INSERT INTO pxtest1 VALUES ('ddd'); 52SELECT * FROM pxtest1; 53 foobar 54-------- 55 aaa 56 ddd 57(2 rows) 58 59PREPARE TRANSACTION 'foo2'; 60ERROR: prepared transactions are disabled 61HINT: Set max_prepared_transactions to a nonzero value. 62SELECT * FROM pxtest1; 63 foobar 64-------- 65 aaa 66(1 row) 67 68COMMIT PREPARED 'foo2'; 69ERROR: prepared transaction with identifier "foo2" does not exist 70SELECT * FROM pxtest1; 71 foobar 72-------- 73 aaa 74(1 row) 75 76-- Test duplicate gids 77BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; 78UPDATE pxtest1 SET foobar = 'eee' WHERE foobar = 'ddd'; 79SELECT * FROM pxtest1; 80 foobar 81-------- 82 aaa 83(1 row) 84 85PREPARE TRANSACTION 'foo3'; 86ERROR: prepared transactions are disabled 87HINT: Set max_prepared_transactions to a nonzero value. 88SELECT gid FROM pg_prepared_xacts; 89 gid 90----- 91(0 rows) 92 93BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; 94INSERT INTO pxtest1 VALUES ('fff'); 95-- This should fail, because the gid foo3 is already in use 96PREPARE TRANSACTION 'foo3'; 97ERROR: prepared transactions are disabled 98HINT: Set max_prepared_transactions to a nonzero value. 99SELECT * FROM pxtest1; 100 foobar 101-------- 102 aaa 103(1 row) 104 105ROLLBACK PREPARED 'foo3'; 106ERROR: prepared transaction with identifier "foo3" does not exist 107SELECT * FROM pxtest1; 108 foobar 109-------- 110 aaa 111(1 row) 112 113-- Test serialization failure (SSI) 114BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; 115UPDATE pxtest1 SET foobar = 'eee' WHERE foobar = 'ddd'; 116SELECT * FROM pxtest1; 117 foobar 118-------- 119 aaa 120(1 row) 121 122PREPARE TRANSACTION 'foo4'; 123ERROR: prepared transactions are disabled 124HINT: Set max_prepared_transactions to a nonzero value. 125SELECT gid FROM pg_prepared_xacts; 126 gid 127----- 128(0 rows) 129 130BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; 131SELECT * FROM pxtest1; 132 foobar 133-------- 134 aaa 135(1 row) 136 137-- This should fail, because the two transactions have a write-skew anomaly 138INSERT INTO pxtest1 VALUES ('fff'); 139PREPARE TRANSACTION 'foo5'; 140ERROR: prepared transactions are disabled 141HINT: Set max_prepared_transactions to a nonzero value. 142SELECT gid FROM pg_prepared_xacts; 143 gid 144----- 145(0 rows) 146 147ROLLBACK PREPARED 'foo4'; 148ERROR: prepared transaction with identifier "foo4" does not exist 149SELECT gid FROM pg_prepared_xacts; 150 gid 151----- 152(0 rows) 153 154-- Clean up 155DROP TABLE pxtest1; 156-- Test detection of session-level and xact-level locks on same object 157BEGIN; 158SELECT pg_advisory_lock(1); 159 pg_advisory_lock 160------------------ 161 162(1 row) 163 164SELECT pg_advisory_xact_lock_shared(1); 165 pg_advisory_xact_lock_shared 166------------------------------ 167 168(1 row) 169 170PREPARE TRANSACTION 'foo6'; -- fails 171ERROR: prepared transactions are disabled 172HINT: Set max_prepared_transactions to a nonzero value. 173-- Test subtransactions 174BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; 175 CREATE TABLE pxtest2 (a int); 176 INSERT INTO pxtest2 VALUES (1); 177 SAVEPOINT a; 178 INSERT INTO pxtest2 VALUES (2); 179 ROLLBACK TO a; 180 SAVEPOINT b; 181 INSERT INTO pxtest2 VALUES (3); 182PREPARE TRANSACTION 'regress-one'; 183ERROR: prepared transactions are disabled 184HINT: Set max_prepared_transactions to a nonzero value. 185CREATE TABLE pxtest3(fff int); 186-- Test shared invalidation 187BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; 188 DROP TABLE pxtest3; 189 CREATE TABLE pxtest4 (a int); 190 INSERT INTO pxtest4 VALUES (1); 191 INSERT INTO pxtest4 VALUES (2); 192 DECLARE foo CURSOR FOR SELECT * FROM pxtest4; 193 -- Fetch 1 tuple, keeping the cursor open 194 FETCH 1 FROM foo; 195 a 196--- 197 1 198(1 row) 199 200PREPARE TRANSACTION 'regress-two'; 201ERROR: prepared transactions are disabled 202HINT: Set max_prepared_transactions to a nonzero value. 203-- No such cursor 204FETCH 1 FROM foo; 205ERROR: cursor "foo" does not exist 206-- Table doesn't exist, the creation hasn't been committed yet 207SELECT * FROM pxtest2; 208ERROR: relation "pxtest2" does not exist 209LINE 1: SELECT * FROM pxtest2; 210 ^ 211-- There should be two prepared transactions 212SELECT gid FROM pg_prepared_xacts; 213 gid 214----- 215(0 rows) 216 217-- pxtest3 should be locked because of the pending DROP 218begin; 219lock table pxtest3 in access share mode nowait; 220rollback; 221-- Disconnect, we will continue testing in a different backend 222\c - 223-- There should still be two prepared transactions 224SELECT gid FROM pg_prepared_xacts; 225 gid 226----- 227(0 rows) 228 229-- pxtest3 should still be locked because of the pending DROP 230begin; 231lock table pxtest3 in access share mode nowait; 232rollback; 233-- Commit table creation 234COMMIT PREPARED 'regress-one'; 235ERROR: prepared transaction with identifier "regress-one" does not exist 236\d pxtest2 237SELECT * FROM pxtest2; 238ERROR: relation "pxtest2" does not exist 239LINE 1: SELECT * FROM pxtest2; 240 ^ 241-- There should be one prepared transaction 242SELECT gid FROM pg_prepared_xacts; 243 gid 244----- 245(0 rows) 246 247-- Commit table drop 248COMMIT PREPARED 'regress-two'; 249ERROR: prepared transaction with identifier "regress-two" does not exist 250SELECT * FROM pxtest3; 251 fff 252----- 253(0 rows) 254 255-- There should be no prepared transactions 256SELECT gid FROM pg_prepared_xacts; 257 gid 258----- 259(0 rows) 260 261-- Clean up 262DROP TABLE pxtest2; 263ERROR: table "pxtest2" does not exist 264DROP TABLE pxtest3; -- will still be there if prepared xacts are disabled 265DROP TABLE pxtest4; 266ERROR: table "pxtest4" does not exist 267