Two-phase commit. Original patch by Heikki Linnakangas, with additional

hacking by Alvaro Herrera and Tom Lane.

2 files changed, 215 insertions, 1 deletions

diff --git a/src/test/regress/expected/prepared_xacts.out b/src/test/regress/expected/prepared_xacts.out
new file mode 100644
index 0000000000..d6a165e94e
--- /dev/null
+++ b/src/test/regress/expected/prepared_xacts.out
@@ -0,0 +1,213 @@
+--
+-- PREPARED TRANSACTIONS (two-phase commit)
+--
+-- We can't readily test persistence of prepared xacts within the
+-- regression script framework, unfortunately.  Note that a crash
+-- isn't really needed ... stopping and starting the postmaster would
+-- be enough, but we can't even do that here.
+-- create a simple table that we'll use in the tests
+CREATE TABLE pxtest1 (foobar VARCHAR(10));
+INSERT INTO pxtest1 VALUES ('aaa');
+-- Test PREPARE TRANSACTION
+BEGIN;
+UPDATE pxtest1 SET foobar = 'bbb' WHERE foobar = 'aaa';
+SELECT * FROM pxtest1;
+ foobar 
+--------
+ bbb
+(1 row)
+
+PREPARE TRANSACTION 'foo1';
+SELECT * FROM pxtest1;
+ foobar 
+--------
+ aaa
+(1 row)
+
+-- Test pg_prepared_xacts system view
+SELECT gid FROM pg_prepared_xacts;
+ gid  
+------
+ foo1
+(1 row)
+
+-- Test ROLLBACK PREPARED
+ROLLBACK PREPARED 'foo1';
+SELECT * FROM pxtest1;
+ foobar 
+--------
+ aaa
+(1 row)
+
+SELECT gid FROM pg_prepared_xacts;
+ gid 
+-----
+(0 rows)
+
+-- Test COMMIT PREPARED
+BEGIN;
+INSERT INTO pxtest1 VALUES ('ddd');
+SELECT * FROM pxtest1;
+ foobar 
+--------
+ aaa
+ ddd
+(2 rows)
+
+PREPARE TRANSACTION 'foo2';
+SELECT * FROM pxtest1;
+ foobar 
+--------
+ aaa
+(1 row)
+
+COMMIT PREPARED 'foo2';
+SELECT * FROM pxtest1;
+ foobar 
+--------
+ aaa
+ ddd
+(2 rows)
+
+-- Test duplicate gids
+BEGIN;
+UPDATE pxtest1 SET foobar = 'eee' WHERE foobar = 'ddd';
+SELECT * FROM pxtest1;
+ foobar 
+--------
+ aaa
+ eee
+(2 rows)
+
+PREPARE TRANSACTION 'foo3';
+SELECT gid FROM pg_prepared_xacts;
+ gid  
+------
+ foo3
+(1 row)
+
+BEGIN;
+INSERT INTO pxtest1 VALUES ('fff');
+SELECT * FROM pxtest1;
+ foobar 
+--------
+ aaa
+ ddd
+ fff
+(3 rows)
+
+-- This should fail, because the gid foo3 is already in use
+PREPARE TRANSACTION 'foo3';
+ERROR:  global transaction identifier "foo3" is already in use
+SELECT * FROM pxtest1;
+ foobar 
+--------
+ aaa
+ ddd
+(2 rows)
+
+ROLLBACK PREPARED 'foo3';
+SELECT * FROM pxtest1;
+ foobar 
+--------
+ aaa
+ ddd
+(2 rows)
+
+-- Clean up
+DROP TABLE pxtest1;
+-- Test subtransactions
+BEGIN;
+  CREATE TABLE pxtest2 (a int);
+  INSERT INTO pxtest2 VALUES (1);
+  SAVEPOINT a;
+    INSERT INTO pxtest2 VALUES (2);
+  ROLLBACK TO a;
+  SAVEPOINT b;
+  INSERT INTO pxtest2 VALUES (3);
+PREPARE TRANSACTION 'regress-one';
+CREATE TABLE pxtest3(fff int);
+-- Test shared invalidation
+BEGIN;
+  DROP TABLE pxtest3;
+  CREATE TABLE pxtest4 (a int);
+  INSERT INTO pxtest4 VALUES (1);
+  INSERT INTO pxtest4 VALUES (2);
+  DECLARE foo CURSOR FOR SELECT * FROM pxtest4;
+  -- Fetch 1 tuple, keeping the cursor open
+  FETCH 1 FROM foo;
+ a 
+---
+ 1
+(1 row)
+
+PREPARE TRANSACTION 'regress-two';
+-- No such cursor
+FETCH 1 FROM foo;
+ERROR:  cursor "foo" does not exist
+-- Table doesn't exist, the creation hasn't been committed yet
+SELECT * FROM pxtest2;
+ERROR:  relation "pxtest2" does not exist
+-- There should be two prepared transactions
+SELECT gid FROM pg_prepared_xacts;
+     gid     
+-------------
+ regress-one
+ regress-two
+(2 rows)
+
+-- pxtest3 should be locked because of the pending DROP
+set statement_timeout to 1000;
+SELECT * FROM pxtest3;
+ERROR:  canceling query due to user request
+reset statement_timeout;
+-- Disconnect, we will continue testing in a different backend
+\c -
+-- There should still be two prepared transactions
+SELECT gid FROM pg_prepared_xacts;
+     gid     
+-------------
+ regress-one
+ regress-two
+(2 rows)
+
+-- pxtest3 should still be locked because of the pending DROP
+set statement_timeout to 1000;
+SELECT * FROM pxtest3;
+ERROR:  canceling query due to user request
+reset statement_timeout;
+-- Commit table creation
+COMMIT PREPARED 'regress-one';
+\d pxtest2
+    Table "public.pxtest2"
+ Column |  Type   | Modifiers 
+--------+---------+-----------
+ a      | integer | 
+
+SELECT * FROM pxtest2;
+ a 
+---
+ 1
+ 3
+(2 rows)
+
+-- There should be one prepared transaction
+SELECT gid FROM pg_prepared_xacts;
+     gid     
+-------------
+ regress-two
+(1 row)
+
+-- Commit table drop
+COMMIT PREPARED 'regress-two';
+SELECT * FROM pxtest3;
+ERROR:  relation "pxtest3" does not exist
+-- There should be no prepared transactions
+SELECT gid FROM pg_prepared_xacts;
+ gid 
+-----
+(0 rows)
+
+-- Clean up
+DROP TABLE pxtest2;
+DROP TABLE pxtest4;
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 5eccf2d44a..acd2d25b35 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1279,6 +1279,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem
  iexit                    | SELECT ih.name, ih.thepath, interpt_pp(ih.thepath, r.thepath) AS exit FROM ihighway ih, ramp r WHERE (ih.thepath ## r.thepath);
  pg_indexes               | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, t.spcname AS "tablespace", pg_get_indexdef(i.oid) AS indexdef FROM ((((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = i.reltablespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind = 'i'::"char"));
  pg_locks                 | SELECT l.locktype, l."database", l.relation, l.page, l.tuple, l."transaction", l.classid, l.objid, l.objsubid, l.pid, l."mode", l.granted FROM pg_lock_status() l(locktype text, "database" oid, relation oid, page integer, tuple smallint, "transaction" xid, classid oid, objid oid, objsubid smallint, pid integer, "mode" text, granted boolean);
+ pg_prepared_xacts        | SELECT p."transaction", p.gid, u.usename AS "owner", d.datname AS "database" FROM ((pg_prepared_xact() p("transaction" xid, gid text, ownerid integer, dbid oid) LEFT JOIN pg_database d ON ((p.dbid = d.oid))) LEFT JOIN pg_shadow u ON ((p.ownerid = u.usesysid)));
  pg_rules                 | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) AS definition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (r.rulename <> '_RETURN'::name);
  pg_settings              | SELECT a.name, a.setting, a.category, a.short_desc, a.extra_desc, a.context, a.vartype, a.source, a.min_val, a.max_val FROM pg_show_all_settings() a(name text, setting text, category text, short_desc text, extra_desc text, context text, vartype text, source text, min_val text, max_val text);
  pg_stat_activity         | SELECT d.oid AS datid, d.datname, pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_userid(s.backendid) AS usesysid, u.usename, pg_stat_get_backend_activity(s.backendid) AS current_query, pg_stat_get_backend_activity_start(s.backendid) AS query_start, pg_stat_get_backend_start(s.backendid) AS backend_start, pg_stat_get_backend_client_addr(s.backendid) AS client_addr, pg_stat_get_backend_client_port(s.backendid) AS client_port FROM pg_database d, (SELECT pg_stat_get_backend_idset() AS backendid) s, pg_shadow u WHERE ((pg_stat_get_backend_dbid(s.backendid) = d.oid) AND (pg_stat_get_backend_userid(s.backendid) = u.usesysid));
@@ -1316,7 +1317,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem
  shoelace_obsolete        | SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE (shoe.slcolor = shoelace.sl_color))));
  street                   | SELECT r.name, r.thepath, c.cname FROM ONLY road r, real_city c WHERE (c.outline ## r.thepath);
  toyemp                   | SELECT emp.name, emp.age, emp."location", (12 * emp.salary) AS annualsal FROM emp;
-(40 rows)
+(41 rows)
 
 SELECT tablename, rulename, definition FROM pg_rules 
 	ORDER BY tablename, rulename;