summaryrefslogtreecommitdiffstats
path: root/src/test/regress/expected/prepared_xacts_1.out
diff options
context:
space:
mode:
Diffstat (limited to 'src/test/regress/expected/prepared_xacts_1.out')
-rw-r--r--src/test/regress/expected/prepared_xacts_1.out266
1 files changed, 266 insertions, 0 deletions
diff --git a/src/test/regress/expected/prepared_xacts_1.out b/src/test/regress/expected/prepared_xacts_1.out
new file mode 100644
index 0000000..2cd50ad
--- /dev/null
+++ b/src/test/regress/expected/prepared_xacts_1.out
@@ -0,0 +1,266 @@
+--
+-- 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';
+ERROR: prepared transactions are disabled
+HINT: Set max_prepared_transactions to a nonzero value.
+SELECT * FROM pxtest1;
+ foobar
+--------
+ aaa
+(1 row)
+
+-- Test pg_prepared_xacts system view
+SELECT gid FROM pg_prepared_xacts;
+ gid
+-----
+(0 rows)
+
+-- Test ROLLBACK PREPARED
+ROLLBACK PREPARED 'foo1';
+ERROR: prepared transaction with identifier "foo1" does not exist
+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';
+ERROR: prepared transactions are disabled
+HINT: Set max_prepared_transactions to a nonzero value.
+SELECT * FROM pxtest1;
+ foobar
+--------
+ aaa
+(1 row)
+
+COMMIT PREPARED 'foo2';
+ERROR: prepared transaction with identifier "foo2" does not exist
+SELECT * FROM pxtest1;
+ foobar
+--------
+ aaa
+(1 row)
+
+-- Test duplicate gids
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+UPDATE pxtest1 SET foobar = 'eee' WHERE foobar = 'ddd';
+SELECT * FROM pxtest1;
+ foobar
+--------
+ aaa
+(1 row)
+
+PREPARE TRANSACTION 'foo3';
+ERROR: prepared transactions are disabled
+HINT: Set max_prepared_transactions to a nonzero value.
+SELECT gid FROM pg_prepared_xacts;
+ gid
+-----
+(0 rows)
+
+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: prepared transactions are disabled
+HINT: Set max_prepared_transactions to a nonzero value.
+SELECT * FROM pxtest1;
+ foobar
+--------
+ aaa
+(1 row)
+
+ROLLBACK PREPARED 'foo3';
+ERROR: prepared transaction with identifier "foo3" does not exist
+SELECT * FROM pxtest1;
+ foobar
+--------
+ aaa
+(1 row)
+
+-- Test serialization failure (SSI)
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+UPDATE pxtest1 SET foobar = 'eee' WHERE foobar = 'ddd';
+SELECT * FROM pxtest1;
+ foobar
+--------
+ aaa
+(1 row)
+
+PREPARE TRANSACTION 'foo4';
+ERROR: prepared transactions are disabled
+HINT: Set max_prepared_transactions to a nonzero value.
+SELECT gid FROM pg_prepared_xacts;
+ gid
+-----
+(0 rows)
+
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+SELECT * FROM pxtest1;
+ foobar
+--------
+ aaa
+(1 row)
+
+-- This should fail, because the two transactions have a write-skew anomaly
+INSERT INTO pxtest1 VALUES ('fff');
+PREPARE TRANSACTION 'foo5';
+ERROR: prepared transactions are disabled
+HINT: Set max_prepared_transactions to a nonzero value.
+SELECT gid FROM pg_prepared_xacts;
+ gid
+-----
+(0 rows)
+
+ROLLBACK PREPARED 'foo4';
+ERROR: prepared transaction with identifier "foo4" does not exist
+SELECT gid FROM pg_prepared_xacts;
+ gid
+-----
+(0 rows)
+
+-- Clean up
+DROP TABLE pxtest1;
+-- Test detection of session-level and xact-level locks on same object
+BEGIN;
+SELECT pg_advisory_lock(1);
+ pg_advisory_lock
+------------------
+
+(1 row)
+
+SELECT pg_advisory_xact_lock_shared(1);
+ pg_advisory_xact_lock_shared
+------------------------------
+
+(1 row)
+
+PREPARE TRANSACTION 'foo6'; -- fails
+ERROR: prepared transactions are disabled
+HINT: Set max_prepared_transactions to a nonzero value.
+-- 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';
+ERROR: prepared transactions are disabled
+HINT: Set max_prepared_transactions to a nonzero value.
+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';
+ERROR: prepared transactions are disabled
+HINT: Set max_prepared_transactions to a nonzero value.
+-- 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
+-----
+(0 rows)
+
+-- pxtest3 should be locked because of the pending DROP
+begin;
+lock table pxtest3 in access share mode nowait;
+rollback;
+-- Disconnect, we will continue testing in a different backend
+\c -
+-- There should still be two prepared transactions
+SELECT gid FROM pg_prepared_xacts;
+ gid
+-----
+(0 rows)
+
+-- pxtest3 should still be locked because of the pending DROP
+begin;
+lock table pxtest3 in access share mode nowait;
+rollback;
+-- Commit table creation
+COMMIT PREPARED 'regress-one';
+ERROR: prepared transaction with identifier "regress-one" does not exist
+\d pxtest2
+SELECT * FROM pxtest2;
+ERROR: relation "pxtest2" does not exist
+LINE 1: SELECT * FROM pxtest2;
+ ^
+-- There should be one prepared transaction
+SELECT gid FROM pg_prepared_xacts;
+ gid
+-----
+(0 rows)
+
+-- Commit table drop
+COMMIT PREPARED 'regress-two';
+ERROR: prepared transaction with identifier "regress-two" does not exist
+SELECT * FROM pxtest3;
+ fff
+-----
+(0 rows)
+
+-- There should be no prepared transactions
+SELECT gid FROM pg_prepared_xacts;
+ gid
+-----
+(0 rows)
+
+-- Clean up
+DROP TABLE pxtest2;
+ERROR: table "pxtest2" does not exist
+DROP TABLE pxtest3; -- will still be there if prepared xacts are disabled
+DROP TABLE pxtest4;
+ERROR: table "pxtest4" does not exist