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