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.rast...@huawei.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > >