2 -- PREPARED TRANSACTIONS (two-phase commit)
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
9 CREATE TABLE pxtest1 (foobar VARCHAR(10));
10 INSERT INTO pxtest1 VALUES ('aaa');
11 -- Test PREPARE TRANSACTION
12 BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
13 UPDATE pxtest1 SET foobar = 'bbb' WHERE foobar = 'aaa';
14 SELECT * FROM pxtest1;
20 PREPARE TRANSACTION 'foo1';
21 SELECT * FROM pxtest1;
27 -- Test pg_prepared_xacts system view
28 SELECT gid FROM pg_prepared_xacts;
34 -- Test ROLLBACK PREPARED
35 ROLLBACK PREPARED 'foo1';
36 SELECT * FROM pxtest1;
42 SELECT gid FROM pg_prepared_xacts;
47 -- Test COMMIT PREPARED
48 BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
49 INSERT INTO pxtest1 VALUES ('ddd');
50 SELECT * FROM pxtest1;
57 PREPARE TRANSACTION 'foo2';
58 SELECT * FROM pxtest1;
64 COMMIT PREPARED 'foo2';
65 SELECT * FROM pxtest1;
72 -- Test duplicate gids
73 BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
74 UPDATE pxtest1 SET foobar = 'eee' WHERE foobar = 'ddd';
75 SELECT * FROM pxtest1;
82 PREPARE TRANSACTION 'foo3';
83 SELECT gid FROM pg_prepared_xacts;
89 BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
90 INSERT INTO pxtest1 VALUES ('fff');
91 -- This should fail, because the gid foo3 is already in use
92 PREPARE TRANSACTION 'foo3';
93 ERROR: transaction identifier "foo3" is already in use
94 SELECT * FROM pxtest1;
101 ROLLBACK PREPARED 'foo3';
102 SELECT * FROM pxtest1;
109 -- Test serialization failure (SSI)
110 BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
111 UPDATE pxtest1 SET foobar = 'eee' WHERE foobar = 'ddd';
112 SELECT * FROM pxtest1;
119 PREPARE TRANSACTION 'foo4';
120 SELECT gid FROM pg_prepared_xacts;
126 BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
127 SELECT * FROM pxtest1;
134 INSERT INTO pxtest1 VALUES ('fff');
135 -- This should fail, because the two transactions have a write-skew anomaly
136 PREPARE TRANSACTION 'foo5';
137 ERROR: could not serialize access due to read/write dependencies among transactions
138 DETAIL: Cancelled on commit attempt with conflict in from prepared pivot.
139 HINT: The transaction might succeed if retried.
140 SELECT gid FROM pg_prepared_xacts;
146 ROLLBACK PREPARED 'foo4';
147 SELECT gid FROM pg_prepared_xacts;
154 -- Test subtransactions
155 BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
156 CREATE TABLE pxtest2 (a int);
157 INSERT INTO pxtest2 VALUES (1);
159 INSERT INTO pxtest2 VALUES (2);
162 INSERT INTO pxtest2 VALUES (3);
163 PREPARE TRANSACTION 'regress-one';
164 CREATE TABLE pxtest3(fff int);
165 -- Test shared invalidation
166 BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
168 CREATE TABLE pxtest4 (a int);
169 INSERT INTO pxtest4 VALUES (1);
170 INSERT INTO pxtest4 VALUES (2);
171 DECLARE foo CURSOR FOR SELECT * FROM pxtest4;
172 -- Fetch 1 tuple, keeping the cursor open
179 PREPARE TRANSACTION 'regress-two';
182 ERROR: cursor "foo" does not exist
183 -- Table doesn't exist, the creation hasn't been committed yet
184 SELECT * FROM pxtest2;
185 ERROR: relation "pxtest2" does not exist
186 LINE 1: SELECT * FROM pxtest2;
188 -- There should be two prepared transactions
189 SELECT gid FROM pg_prepared_xacts;
196 -- pxtest3 should be locked because of the pending DROP
197 set statement_timeout to 2000;
198 SELECT * FROM pxtest3;
199 ERROR: canceling statement due to statement timeout
200 reset statement_timeout;
201 -- Disconnect, we will continue testing in a different backend
203 -- There should still be two prepared transactions
204 SELECT gid FROM pg_prepared_xacts;
211 -- pxtest3 should still be locked because of the pending DROP
212 set statement_timeout to 2000;
213 SELECT * FROM pxtest3;
214 ERROR: canceling statement due to statement timeout
215 reset statement_timeout;
216 -- Commit table creation
217 COMMIT PREPARED 'regress-one';
219 Table "public.pxtest2"
220 Column | Type | Modifiers
221 --------+---------+-----------
224 SELECT * FROM pxtest2;
231 -- There should be one prepared transaction
232 SELECT gid FROM pg_prepared_xacts;
239 COMMIT PREPARED 'regress-two';
240 SELECT * FROM pxtest3;
241 ERROR: relation "pxtest3" does not exist
242 LINE 1: SELECT * FROM pxtest3;
244 -- There should be no prepared transactions
245 SELECT gid FROM pg_prepared_xacts;
252 DROP TABLE pxtest3; -- will still be there if prepared xacts are disabled
253 ERROR: table "pxtest3" does not exist