Autonomous Transaction (WIP)

I would like to propose "Autonomous Transaction" feature for 9.5. Details for the same are mentioned below:

What is Autonomous Transaction?
An autonomous transaction has its own COMMIT and ROLLBACK scope to ensure that its outcome does not affect the caller's uncommitted changes. Additionally, the COMMITs and ROLLBACK in the calling transaction should not affect the changes that were finalized on the completion of autonomous transaction itself. Below are properties of autonomous transaction:

1. The autonomous transaction does not see uncommitted changes made by the main transaction and does not share locks or resources with main transaction.

2. Changes in autonomous transactions are visible to other transactions upon commit of the autonomous transactions. Thus, users can access the updated information without having to wait for the main transaction to commit.

3. Autonomous transactions can start other autonomous transaction. There are no limit, other than resource limits, on how many levels of autonomous transaction can be started.

Use-case:
There are many use-case for this feature. One of the use-case is illustrated below
Say a procedure is defined, which does some operation on the database and incase of any failure in operation on main table, it maintains the failure information in a separate relation. But because of current transaction behavior, once main table operation fails, it will rollback whole transaction and hence error logged in error relation will be also lost, which might have been required for future analysis.
In order to solve this issue, we can use autonomous transaction as shown below:
CREATE OR REPLACE function operation(err_msg IN VARCHAR) returns void AS $$
BEGIN
INSERT INTO at_test(id, description) VALUES (998, 'Description for 998');
INSERT INTO at_test(id, description) VALUES (999, NULL);
EXCEPTION
WHEN OTHER THEN
PRAGMA AUTONOMOUS TRANSACTION;
INSERT INTO error_logs(id, timestamp, err_msg) VALUES(nextval('errno'), timenow(), err_msg);
COMMIT;
RAISE not_null_violation;
END;
$$ LANGUAGE plpgsql;
So once we execute above procedure, second INSERT will fails and then within exception handling it will start autonomous transaction and log the error information in a separate table and then gets committed. So though operation to table at_test will fail and rollback, error information will persist in the error_logs table. After execution of procedure, record in two tables will be as below:
Postgres=# select * from error_logs;
id | log_time | err_msg
----+---------------------+---------
5 | 2014-01-17 19:57:11 | error
postgres=# select * from at_test;
id | decsription
----+-------------
(0 rows)

Syntax:
Syntax to create autonomous transaction can be as:
PRAGMA AUTONOMOUS TRANSACTION;
This can be used with independent SQL commands, from procedure, triggers.

Implementation:
Implementation of autonomous transaction is based on the existing sub-transaction and main transaction. Most of the implementations are re-used for autonomous transaction also. Below are the brief details about the same:

Autonomous Transaction Storage:
As for main transaction, structure PGXACT is used to store main transactions, which are created in shared memory of size:
(Number of process)*sizeof(struct PGXACT)
Similarly a new structure will be defined to store autonomous transaction:
Struct PGAutonomousXACT
{
TransactionId xid;
TransactionId xmin;
/* Store the level below main transaction as stored for sub-transaction*/
int nestingLevel;
struct XidCache subxids;
bool overflowed;
bool delaychkpt;
uint nxids;
} PGAutonomousXACT;
All structure members of PGAutonomousXACT are same as used in PGXACT except nestingLevel as marked in bold color to store the level of transaction.
Similar to main transaction, the memory allocated to store autonomous transaction will be:
(Number of process) * sizeof (struct PGAutonomousXACT)*MAX_AUTO_TX_LEVEL
Where MAX_AUTO_TX_LEVEL is maximum number of nested autonomous transaction level.
Unlike main transaction, autonomous transaction cannot be accessed directly. It can be accessed using offset as:
(Process number)*MAX_AUTO_TX_LEVEL + (current auto tx level)
Where 'current auto tx level' is autonomous transaction level in current process (which will be maintained in MyProc structure).

Definition of Autonomous Transaction:
Autonomous transaction will be defined in similar way as sub-transaction except few additional info (like level of autonomous transaction in MyProc) about autonomous transaction will be initialized.

Starting of Autonomous Transaction:
Starting of autonomous transaction will be exactly same as starting sub-transaction.

Committing of Autonomous Transaction:
Commit uses mix approach of main and sub-transaction to perform commit:

1. Commit of record and logging the corresponding WAL happens in the same way as main transaction (except the way autonomous transaction and their sub-transaction accessed).

2. Freeing of all resource and popping of previous transaction happens in the same way as sub-transaction.

Data Visibility for Autonomous Transaction:
Autonomous transaction will be treated as independent and similar to main transaction while taking the snapshot. For each process, all running autonomous transaction (except the current one) and their sub-transaction (if any) will be added to transaction list of snapshot.
Suppose below processes are running with given transactions:
Proc-1

Proc-2

Proc-3

100

101

105

102 (Auto Tx1)

106 (Auto Tx1)

103 (Auto Tx1)

107 (Auto Tx2)

104 (Auto Tx2 sub-tx)

Suppose latest completed transaction is 108.
Then Snapshot data for autonomous transaction 107 will be as below:
Xmin: 100
Xmax: 109
Snapshot->xip[]: 100, 101, 102, 103, 105, 106
Snapshot->subxip[]: 104

System Cache:
As per current design, subsequent search for a same tuple from same session results in getting tuple from system cache itself. Since autonomous transaction is not supposed to see the changes done by main transaction, so it should not search in the system cache which was updated by main transaction otherwise it will end-up in seeing changes done by main transaction. So in order to avoid this, we can take one of the approaches:

1. It should always search from the system table and should not add tuple to system cache. This will keep the design simple but performance will be impacted if same tuple is searched multiple times.

2. We can maintain one system cache for each transaction for each system tables i.e. for each system table per process, number of cache will be:
MAX_AUTO_TX_LEVEL + 1 (For Main transaction)
So then autonomous transaction will have to search and insert the tuple in the corresponding cache of the transaction. This will use more resources to manage more number of caches but performance will not be impacted.
First approach is used in current patch.

Deadlock Detection:
It is possible that the main or upper autonomous transaction has taken a lock on some resource, which might be required by lower autonomous transaction. If it happens so then deadlock will occur. So in order to solve this issue, each main and autonomous transaction will hold list of all locks acquired in PROLOCK based on which deadlock will be resolved.

Plan to push it into 9.5:

1. Initially we can plan to support only along with standalone SQL-commands. This will create infrastructure for future work.

2. Then in further CommitFest/Release, we can plan to support this inside the Procedure (this will require to create infrastructure to do autonomous transaction operation inside procedure) and triggers also.

Any Comments/Suggestions/Feedbacks are welcome.

Thanks and Regards,
Kumar Rajeev Rastogi