Re: Autonomous Transaction (WIP)

Hello

+1 for feature
-1 for Oracle syntax - it is hardly inconsistent with Postgres

Autonomous transactions should be used everywhere - not only in plpgsql

Regards

Pavel

2014-04-07 6:06 GMT+02:00 Rajeev rastogi <rajeev(dot)rastogi(at)huawei(dot)com>:

> 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 *
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
>