--
-- 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 TRANSACTION ISOLATION LEVEL SERIALIZABLE;
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 TRANSACTION ISOLATION LEVEL SERIALIZABLE;
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 TRANSACTION ISOLATION LEVEL SERIALIZABLE;
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 TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO pxtest1 VALUES ('fff');
-- This should fail, because the gid foo3 is already in use
PREPARE TRANSACTION 'foo3';
ERROR:  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)

-- Test serialization failure (SSI)
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE pxtest1 SET foobar = 'eee' WHERE foobar = 'ddd';
SELECT * FROM pxtest1;
 foobar 
--------
 aaa
 eee
(2 rows)

PREPARE TRANSACTION 'foo4';
SELECT gid FROM pg_prepared_xacts;
 gid  
------
 foo4
(1 row)

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM pxtest1;
 foobar 
--------
 aaa
 ddd
(2 rows)

INSERT INTO pxtest1 VALUES ('fff');
-- This should fail, because the two transactions have a write-skew anomaly
PREPARE TRANSACTION 'foo5';
ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Cancelled on commit attempt with conflict in from prepared pivot.
HINT:  The transaction might succeed if retried.
SELECT gid FROM pg_prepared_xacts;
 gid  
------
 foo4
(1 row)

ROLLBACK PREPARED 'foo4';
SELECT gid FROM pg_prepared_xacts;
 gid 
-----
(0 rows)

-- Clean up
DROP TABLE pxtest1;
-- Test subtransactions
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  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 TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  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
LINE 1: SELECT * FROM pxtest2;
                      ^
-- 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 2000;
SELECT * FROM pxtest3;
ERROR:  canceling statement due to statement timeout
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 2000;
SELECT * FROM pxtest3;
ERROR:  canceling statement due to statement timeout
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
LINE 1: SELECT * FROM pxtest3;
                      ^
-- There should be no prepared transactions
SELECT gid FROM pg_prepared_xacts;
 gid 
-----
(0 rows)

-- Clean up
DROP TABLE pxtest2;
DROP TABLE pxtest3;  -- will still be there if prepared xacts are disabled
ERROR:  table "pxtest3" does not exist
DROP TABLE pxtest4;