Prepared Transactions in PostgreSQL 9.2

Prepared Transactions:

PREPARE TRANSACTION prepares the current transaction for two-phase commit. After this command, the transaction is no longer associated with the current session; instead, its state is fully stored on disk, and there is a very high probability that it can be committed successfully, even if a database crash occurs before the commit is requested.

Once prepared, a transaction can later be committed or rolled back with COMMIT PREPARED or ROLLBACK PREPARED, respectively. Those commands can be issued from any session, not only the one that executed the original transaction.

Prepared transactions are session independent,we can create/prepare a prepared transaction on one session and the we can commit/rollback the prepared transaction in different session.

NOTE1: The commit/rollback user should be owner  of the transaction or superuser of he database,otherwise we will get errores.


NOTE2: If we have prepared transaction on the database it will prevent the database maintenance operations like "Reindex/Vacuum Full" .

postgres=# \dt+
                    List of relations
 Schema | Name | Type  |  Owner   |  Size   | Description 
--------+------+-------+----------+---------+-------------
 public | test | table | postgres | 0 bytes | 
(1 row)

postgres=# BEGIN ;
BEGIN
postgres=# COPY test from '/tmp/test_copy.sql';
COPY 7
postgres=# SELECT * from test;
 id 
----
  1
  1
  1
  2
  2
 10
 10
(7 rows)

postgres=# PREPARE transaction 'trx1';
PREPARE TRANSACTION
postgres=# select * from test;
 id 
----
(0 rows)

postgres=# SELECT * from pg_prepared_xacts ;
 transaction | gid  |           prepared            |  owner   | database 
-------------+------+-------------------------------+----------+----------
        1716 | trx1 | 2013-05-07 04:04:56.620985-07 | postgres | postgres
(1 row)

postgres=# COMMIT PREPARED 'trx1';
COMMIT PREPARED
postgres=# SELECT * from test;
 id 
----
  1
  1
  1
  2
  2
 10
 10
(7 rows)

postgres=# SELECT * from pg_prepared_xacts ;
 transaction | gid | prepared | owner | database 
-------------+-----+----------+-------+----------
(0 rows)

postgres=# PREPARE transaction 'trx2';
WARNING:  there is no transaction in progress
WARNING:  there is no transaction in progress
ROLLBACK
postgres=# BEGIN ;
BEGIN
postgres=# DELETE FROM test  where id=10;
DELETE 2
postgres=# SELECT * from test;
 id 
----
  1
  1
  1
  2
  2
(5 rows)

postgres=# 
postgres=# PREPARE transaction 'trx2';
PREPARE TRANSACTION
postgres=# SELECT * from test;
 id 
----
  1
  1
  1
  2
  2
 10
 10
(7 rows)

postgres=# 
postgres=# ROLLBACK PREPARED 'trx2';
ROLLBACK PREPARED
postgres=# SELECT * from test;
 id 
----
  1
  1
  1
  2
  2
 10
 10
(7 rows)

postgres=# 

Errors: we will get if we use prepared transaction in different users.

The below transactions created by  chiru2 and postgres users.i have tried to commit/rollbacked in different CHIRU users and received below errors.

Session 1:



postgres=> select current_user;
 current_user 
--------------
 chiru2
(1 row)

postgres=> select * from pg_prepared_xacts ;
 transaction | gid  |           prepared            |  owner   | database 
-------------+------+-------------------------------+----------+----------
        1723 | trx3 | 2013-05-07 04:19:45.99662-07  | postgres | postgres
        1730 | trx4 | 2013-05-07 04:30:54.648643-07 | chiru2   | postgres
(2 rows)

Session 2:

postgres=> select current_user;
 current_user 
--------------
 chiru
(1 row)

postgres=> select * from pg_prepared_xacts ;
 transaction | gid  |           prepared            |  owner   | database 
-------------+------+-------------------------------+----------+----------
        1723 | trx3 | 2013-05-07 04:19:45.99662-07  | postgres | postgres
        1730 | trx4 | 2013-05-07 04:30:54.648643-07 | chiru2   | postgres
(2 rows)

postgres=> 
postgres=> 
postgres=> COMMIT  prepared 'trx3';
ERROR:  permission denied to finish prepared transaction
HINT:  Must be superuser or the user that prepared the transaction.
postgres=> COMMIT  prepared 'trx4';
ERROR:  permission denied to finish prepared transaction
HINT:  Must be superuser or the user that prepared the transaction.
postgres=>