Re: 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

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
>
>

On 04/07/2014 12:06 PM, Rajeev rastogi wrote:

> Syntax to create autonomous transaction can be as:
>
> */PRAGMA AUTONOMOUS TRANSACTION;/*

Wouldn't you want to use SET TRANSACTION for this?

Or a suffix on BEGIN, like BEGIN AUTONOMOUS TRANSACTION ?

What's the logic behind introducing "PRAGMA" ?

If you wanted to use that syntax for Oracle compatibility you'd need to use:

PRAGMA AUTONOMOUS_TRANSACTION;

(note underscore). But really, this would no be a pragma at all,
PostgreSQL doesn't really have the concept. Calling it that would just
be misleading.

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

If you don't want it to dirty read data from the parent tx, or inherit
parent locks, then it cannot be the same at all.

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

I'm not sure what you mean here.

Overall, this looks like a HUGE job to make work well. I know some
others have been doing work along the same lines, so hopefully you'll be
able to collaborate and share ideas.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 07/04/14 15:50, Craig Ringer wrote:
> On 04/07/2014 12:06 PM, Rajeev rastogi wrote:
>
>
>> Syntax to create autonomous transaction can be as:
>>
>> */PRAGMA AUTONOMOUS TRANSACTION;/*
>
> Wouldn't you want to use SET TRANSACTION for this?
>
> Or a suffix on BEGIN, like BEGIN AUTONOMOUS TRANSACTION ?
>
> What's the logic behind introducing "PRAGMA" ?
>
>
> If you wanted to use that syntax for Oracle compatibility you'd need to use:
>
> PRAGMA AUTONOMOUS_TRANSACTION;
>
> (note underscore).

FWIW the implementation in the patch uses "PRAGMA
AUTONOMOUS_TRANSACTION", the space is presumably a typo.

Regards

Ian Barwick

--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 07 April 2014 12:20, Craig Ringer
>
> > Syntax to create autonomous transaction can be as:
> >
> > */PRAGMA AUTONOMOUS TRANSACTION;/*
>
> Wouldn't you want to use SET TRANSACTION for this?
>
> Or a suffix on BEGIN, like BEGIN AUTONOMOUS TRANSACTION ?
>
> What's the logic behind introducing "PRAGMA" ?
>
>
> If you wanted to use that syntax for Oracle compatibility you'd need to
> use:
>
> PRAGMA AUTONOMOUS_TRANSACTION;
>
> (note underscore). But really, this would no be a pragma at all,
> PostgreSQL doesn't really have the concept. Calling it that would just
> be misleading.

Actually it is same as oracle (i.e. PRAGMA AUTONOMOUS_TRANSACTION), it was just typo mistake in previous mail.
But if this is also not accepted then we can discuss and come out with a syntax based on everyone agreement.

>
> > *_Starting of Autonomous Transaction:_*
> >
> > Starting of autonomous transaction will be exactly same as starting
> > sub-transaction.
>
> If you don't want it to dirty read data from the parent tx, or inherit
> parent locks, then it cannot be the same at all.

While starting sub-transaction, it is just initializing the resources required and
links the same to the parent transaction, which we require for autonomous transaction also.
I am not able to notice any issue as you mentioned above with this.
Please let me know if I am missing something or misunderstood your concern.

> > 2. Freeing of all resource and popping of previous transaction
> > happens in the same way as sub-transaction.
>
> I'm not sure what you mean here.

It means, during commit of autonomous transaction, freeing of all resource are done in the same way as done for sub-transaction.
Also current autonomous transaction gets popped out and points to the parent transaction in the similar way as done for sub-transaction.

> Overall, this looks like a HUGE job to make work well. I know some
> others have been doing work along the same lines, so hopefully you'll
> be able to collaborate and share ideas.

Yes it is huge works, so I have proposed in the beginning of 9.5 so that we can have multiple round of discussion and hence address
all concerns.
Also I have proposed to finish this feature in multiple rounds i.e. first patch, we can try to support autonomous transaction from
standalone SQL-command only, which will set-up infrastructure for future work in this area.

Using the WIP patch sent, I have done basic testing and it works fine.

Any comments?

Thanks and Regards,
Kumar Rajeev Rastogi

On Mon, Apr 7, 2014 at 12:06 AM, Rajeev rastogi
<rajeev(dot)rastogi(at)huawei(dot)com> wrote:
>
> 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.

I'm not sure how this would work out internally -- it would depend on
how you plan to allocate the new transaction in the internal data
structures -- but the natural way to prevent/detect deadlocks would be
to have the parent transaction immediately take a lock on the
autonomous transaction as soon as it's started. That would cause any
lock in the autonomous transaction which caused it to wait on the
parent transaction to be detected as a deadlock. It would also cause
any monitoring tool to correctly show the parent transaction as
waiting on the autonomous transaction to finish.

If the autonomous transaction is actually a separate procarray entry
(which I suspect it would have to be, much like prepared transactions
and the dblink connections which are commonly used to kludge
autonomous transactions) then this should be fairly painless. If you
implement some kind of saving and restoring procarray data then it
probably wouldn't work out.

--
greg

Greg Stark wrote:

> If the autonomous transaction is actually a separate procarray entry
> (which I suspect it would have to be, much like prepared transactions
> and the dblink connections which are commonly used to kludge
> autonomous transactions) then this should be fairly painless. If you
> implement some kind of saving and restoring procarray data then it
> probably wouldn't work out.

I don't have time to digest this proposal ATM, but in previous occasion
when we have discussed autonomous transactions (ATs), we have always
considered natural that they have their own procarray entries; there are
too many strange issues otherwise.

Since the number of procarray entries is fixed at startup time, one
natural consequence of this is that the number of ATs in flight at any
moment is also fixed. Normally we consider allocating a single AT per
session to be sufficient. So you can't have one AT start another AT,
for instance -- that seems a reasonable restriction.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Tue, Apr 8, 2014 at 2:43 PM, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> wrote:
> Greg Stark wrote:
>> If the autonomous transaction is actually a separate procarray entry
>> (which I suspect it would have to be, much like prepared transactions
>> and the dblink connections which are commonly used to kludge
>> autonomous transactions) then this should be fairly painless. If you
>> implement some kind of saving and restoring procarray data then it
>> probably wouldn't work out.
>
> I don't have time to digest this proposal ATM, but in previous occasion
> when we have discussed autonomous transactions (ATs), we have always
> considered natural that they have their own procarray entries; there are
> too many strange issues otherwise.
>
> Since the number of procarray entries is fixed at startup time, one
> natural consequence of this is that the number of ATs in flight at any
> moment is also fixed. Normally we consider allocating a single AT per
> session to be sufficient. So you can't have one AT start another AT,
> for instance -- that seems a reasonable restriction.

It depends. A lot of Oracle users are used to having autonomous
transactions be very cheap, so you can just mark random procedures as
running in an autonomous transaction and forget about it. If the call
stack is several levels deep, then you could easily have one such
procedure call another such procedure. Of course, you may feel that's
bad practice or that we shouldn't emulate what $COMPETITOR does, and I
agree we don't have to necessarily do it that way just because they do
it that way, but I'm not sure it's accurate to say that nobody will
care.

I'm also pretty unconvinced that multiple PGPROCs is the right way to
go. First, PGPROCs have a bunch of state in them that is assumed to
exist once per backend. We might find pretty substantial code churn
there if we try to go change that. Second, why do other backends
really need to know about our ATs? As far as I can see, if other
backends see the AT as a subtransaction of our top-level transaction
up until it actually commits, that ought to be just fine. Maybe the
backend needs to internally frob visibility rules, but that's not a
matter for shared memory.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 2014-04-08 15:39:18 -0400, Robert Haas wrote:
> I'm also pretty unconvinced that multiple PGPROCs is the right way to
> go. First, PGPROCs have a bunch of state in them that is assumed to
> exist once per backend. We might find pretty substantial code churn
> there if we try to go change that. Second, why do other backends
> really need to know about our ATs? As far as I can see, if other
> backends see the AT as a subtransaction of our top-level transaction
> up until it actually commits, that ought to be just fine. Maybe the
> backend needs to internally frob visibility rules, but that's not a
> matter for shared memory.

Agreed. That's also how I imagined things to work.

I think except the visibility semantics, there's really not that much to
do if we were to reuse the subtransaction framework. There's some
complications with Hot Standby, but I think those can be solved.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> I'm also pretty unconvinced that multiple PGPROCs is the right way to
> go. First, PGPROCs have a bunch of state in them that is assumed to
> exist once per backend. We might find pretty substantial code churn
> there if we try to go change that. Second, why do other backends
> really need to know about our ATs? As far as I can see, if other
> backends see the AT as a subtransaction of our top-level transaction
> up until it actually commits, that ought to be just fine.

If we can make it work like that, sure. I'm a bit worried about how you'd
decouple a subtransaction and commit it atomically ... or if that's not
atomic, will it create any problems? The point being that you need to
change both pg_subtrans and pg_clog to make that state transition.

regards, tom lane

On 2014-04-08 16:13:21 -0400, Tom Lane wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > I'm also pretty unconvinced that multiple PGPROCs is the right way to
> > go. First, PGPROCs have a bunch of state in them that is assumed to
> > exist once per backend. We might find pretty substantial code churn
> > there if we try to go change that. Second, why do other backends
> > really need to know about our ATs? As far as I can see, if other
> > backends see the AT as a subtransaction of our top-level transaction
> > up until it actually commits, that ought to be just fine.
>
> If we can make it work like that, sure. I'm a bit worried about how you'd
> decouple a subtransaction and commit it atomically ... or if that's not
> atomic, will it create any problems? The point being that you need to
> change both pg_subtrans and pg_clog to make that state transition.

I think it can be made work sensibly - while those states are changed it
will still appear to be running via the procarray. There's some fun
around suboverflowed entries, but I think that can be handled by
reserving an entry for autonomous transactions.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 08 April 2014 23:29, Greg Stark Wrote:

> If the autonomous transaction is actually a separate procarray entry
> (which I suspect it would have to be, much like prepared transactions
> and the dblink connections which are commonly used to kludge autonomous
> transactions) then this should be fairly painless. If you implement
> some kind of saving and restoring procarray data then it probably
> wouldn't work out.

No, I am not creating a separate procarray entry to maintain autonomous transaction.

Similar to MyPgXact (of type PGXACT), which hold main transaction for a particular session,
I have created another member MyPgAutonomousXact (of type structure PGAutonomousXACT),
which holds autonomous transactions for a particular session.

Unlike MyPgXact, MyPgAutonomousXact will be an array to hold multiple autonomous transactions.
There are no limit, other than resource limits, on how many levels of autonomous transaction
can be started. As of now we have used maximum as 3, which can be changed easily if required or
it can be made configurable also.

MyProc for a particular session just have an entry to track the level of autonomous transaction,
which will be used to reference current autonomous transaction from MyPgAutonomousXact.
e.g. if one autonomous transaction is created and it is currently working under this transaction,
then level inside MyProc will be as 1. Once this transaction is over and popped out, level will
be reduced to zero.

Again like main transaction MyPgXact, MyPgAutonomousXact can also track list of all sub-transaction
and overflowed transaction started within this autonomous transaction.

> > Deadlock Detection:
> I'm not sure how this would work out internally
In order to resolve deadlock, two member variable will be created in the structure PROLOCK:
Bitmask for lock types currently held by autonomous transaction.
LOCKMASK holdMaskByAutoTx[MAX_AUTO_TX_LEVEL]
Bitmask for lock types currently held by main transaction.
LOCKMASK holdMaskByNormalTx

Now when we grant the lock to particular transaction, depending on type of transaction, bit
Mask will be set for either holdMaskByAutoTx or holdMaskByNormalTx.
Similar when lock is ungranted, corresponding bitmask will be reset.

Using the above two information, deadlock will be detected.

Any comment/feedback/doubt are welcome.

Thanks and Regards,
Kumar Rajeev Rastogi

On 09 April 2014 01:09, Rover Haas Wrote:

> I'm also pretty unconvinced that multiple PGPROCs is the right way to
> go. First, PGPROCs have a bunch of state in them that is assumed to
> exist once per backend. We might find pretty substantial code churn
> there if we try to go change that.

Yes you right. That is why I am not creating a separate procarray entry to
maintain autonomous transaction. Please find details in previous reply sent
today sometime back.

> Second, why do other backends
> really need to know about our ATs? As far as I can see, if other
> backends see the AT as a subtransaction of our top-level transaction up
> until it actually commits, that ought to be just fine. Maybe the
> backend needs to internally frob visibility rules, but that's not a
> matter for shared memory.

In order to get snapshot from other session, it will be required by other
session to access autonomous transaction and their sub-transactions.

During snapshot creation, autonomous transaction is considered as main
transaction and list of all running autonomous transaction and their sub-transaction
gets stored in snapshot data.

e.g. Suppose below processes are running with given transactions:

Proc-1: 100
Proc-2: 101, 102 (Auto Tx1), 103 (Auto Tx2), 104 (Sub-tx of Auto Tx2)
Proc-3: 105, 106 (Auto Tx2), 107 (Auto Tx2)

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

Thanks and Regards,
Kumar Rajeev Rastogi

On 09 April 2014 01:43, Tom Lane Wrote:

> > I'm also pretty unconvinced that multiple PGPROCs is the right way to
> > go. First, PGPROCs have a bunch of state in them that is assumed to
> > exist once per backend. We might find pretty substantial code churn
> > there if we try to go change that. Second, why do other backends
> > really need to know about our ATs? As far as I can see, if other
> > backends see the AT as a subtransaction of our top-level transaction
> > up until it actually commits, that ought to be just fine.
>
> If we can make it work like that, sure. I'm a bit worried about how
> you'd decouple a subtransaction and commit it atomically ... or if
> that's not atomic, will it create any problems?

Though autonomous transaction uses mixed approach of sub-transaction as well as main
transaction, transaction state of autonomous transaction is handled independently.
So depending on the transaction state of autonomous transaction (for commit TBLOCK_AUTOCOMMIT),
this transaction will be committed. While committing:
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).
This will take care automatically of updating pg_clog also for autonomous transaction.
2. Also it marks the autonomous transaction finish by setting appropriate fields of MyPgAutonomousXact in similar manner as done for main transaction.
3. Freeing of all resource and popping out of parent transaction happens in the same way as sub-transaction.

> The point being that
> you need to change both pg_subtrans and pg_clog to make that state
> transition.

Yes I am changing both. But no specific changes were required. During commit and assignment of autonomous transaction, it is automatically taken care.

Any comment/feedback/doubt are welcome?

Thanks and Regards,
Kumar Rajeev Rastogi

On Wed, Apr 9, 2014 at 11:03 AM, Rajeev rastogi
<rajeev(dot)rastogi(at)huawei(dot)com>wrote:

>
> Though autonomous transaction uses mixed approach of sub-transaction as
> well as main
> transaction, transaction state of autonomous transaction is handled
> independently.
>
>
Whenever I was asked to have a look at implementing this feature, I always
wondered about the great amount of global state that a backend maintains
which is normally tied to a single top transaction. Since AT will have same
characteristics as a top level transaction, I wonder how do you plan to
separate those global state variables ? Sure, we can group them in a
structure and put them on a stack when an AT starts and pop them off when
the original top transaction becomes active again, finding all such global
state variables is going to be tricky.

Thanks,
Pavan

--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee

On 04/09/2014 08:44 AM, Pavan Deolasee wrote:
> On Wed, Apr 9, 2014 at 11:03 AM, Rajeev rastogi
> <rajeev(dot)rastogi(at)huawei(dot)com <mailto:rajeev(dot)rastogi(at)huawei(dot)com>> wrote:
>
>
> Though autonomous transaction uses mixed approach of
> sub-transaction as well as main
> transaction, transaction state of autonomous transaction is
> handled independently.
>
>
> Whenever I was asked to have a look at implementing this feature, I
> always wondered about the great amount of global state that a backend
> maintains which is normally tied to a single top transaction. Since AT
> will have same characteristics as a top level transaction, I wonder
> how do you plan to separate those global state variables ? Sure, we
> can group them in a structure and put them on a stack when an AT
> starts and pop them off when the original top transaction becomes
> active again, finding all such global state variables is going to be
> tricky.
I would hope most of this to be solved by having one (read only) virtual
transaction and
then juggling the ATs in a way similar to current subtransaction machinery.

The main differences would be that:

A) the top level transaction stays virtual

and

B) ATs are committed independantly

This would be greatly simplified if we can accept the restriction that
there is only single
snapshot per backend (not per transaction). To me this seems a
completely sensible restriction.

Re syntax, I think we need a way to name the transactions so we can have
a way
to switch between multiple parallel active autonomous transactions.

-----
BEGIN TRANSACTION myfirsttransaction;

do something in myfirsttransaction;

BEGIN TRANSACTION anothertransaction;

do something in anothertransaction;

SET TRANSACTION myfirsttransaction;

more work in myfirsttransaction;

ROLLBACK anothertransaction;

COMMIT; -- or COMMIT myfirsttransaction;
----

Cheers
Hannu

>
> Thanks,
> Pavan
>
> --
> Pavan Deolasee
> http://www.linkedin.com/in/pavandeolasee

On 04/09/2014 02:44 PM, Pavan Deolasee wrote:
> On Wed, Apr 9, 2014 at 11:03 AM, Rajeev rastogi
> <rajeev(dot)rastogi(at)huawei(dot)com <mailto:rajeev(dot)rastogi(at)huawei(dot)com>> wrote:
>
>
> Though autonomous transaction uses mixed approach of sub-transaction
> as well as main
> transaction, transaction state of autonomous transaction is handled
> independently.
>
>
> Whenever I was asked to have a look at implementing this feature, I
> always wondered about the great amount of global state that a backend
> maintains which is normally tied to a single top transaction. Since AT
> will have same characteristics as a top level transaction, I wonder how
> do you plan to separate those global state variables ? Sure, we can
> group them in a structure and put them on a stack when an AT starts and
> pop them off when the original top transaction becomes active again,
> finding all such global state variables is going to be tricky.

... not to mention the fact that extensions may rely on having their own
global state.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 04/09/2014 09:55 AM, Hannu Krosing wrote:
> This would be greatly simplified if we can accept the restriction that
> there is only single
> snapshot per backend (not per transaction). To me this seems a
> completely sensible restriction.

Huh? In Read committed mode, every query within a transaction gets a
different snapshot.

- Heikki

On Wed, Apr 9, 2014 at 12:24 AM, Rajeev rastogi
<rajeev(dot)rastogi(at)huawei(dot)com> wrote:
>> > Deadlock Detection:
>> I'm not sure how this would work out internally
> In order to resolve deadlock, two member variable will be created in the structure PROLOCK:
> Bitmask for lock types currently held by autonomous transaction.
> LOCKMASK holdMaskByAutoTx[MAX_AUTO_TX_LEVEL]
> Bitmask for lock types currently held by main transaction.
> LOCKMASK holdMaskByNormalTx
>
> Now when we grant the lock to particular transaction, depending on type of transaction, bit
> Mask will be set for either holdMaskByAutoTx or holdMaskByNormalTx.
> Similar when lock is ungranted, corresponding bitmask will be reset.

That sounds pretty ugly, not to mention the fact that it will cause a
substantial increase in the amount of memory required to store
PROCLOCKs. It will probably slow things down, too.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Wed, Apr 9, 2014 at 12:24 AM, Rajeev rastogi
> <rajeev(dot)rastogi(at)huawei(dot)com> wrote:
>> Now when we grant the lock to particular transaction, depending on type of transaction, bit
>> Mask will be set for either holdMaskByAutoTx or holdMaskByNormalTx.
>> Similar when lock is ungranted, corresponding bitmask will be reset.

> That sounds pretty ugly, not to mention the fact that it will cause a
> substantial increase in the amount of memory required to store
> PROCLOCKs. It will probably slow things down, too.

More to the point, why isn't it a flat-out bad idea? I can see no
justification for distinguishing normal and autonomous transactions
at this level.

regards, tom lane

On 09 April 2014 21:25, Robert Haas Wrote:

> >> > Deadlock Detection:
> >> I'm not sure how this would work out internally
> > In order to resolve deadlock, two member variable will be created in
> the structure PROLOCK:
> > Bitmask for lock types currently held by autonomous
> transaction.
> > LOCKMASK holdMaskByAutoTx[MAX_AUTO_TX_LEVEL]
> > Bitmask for lock types currently held by main transaction.
> > LOCKMASK holdMaskByNormalTx
> >
> > Now when we grant the lock to particular transaction, depending on
> > type of transaction, bit Mask will be set for either holdMaskByAutoTx
> or holdMaskByNormalTx.
> > Similar when lock is ungranted, corresponding bitmask will be reset.
>
> That sounds pretty ugly, not to mention the fact that it will cause a
> substantial increase in the amount of memory required to store
> PROCLOCKs. It will probably slow things down, too.

Actually I followed above design to keep it align with the existing design. As I understand, currently also
all lock conflict is checked based on the corresponding lock bit mask.

This is good catch that shared memory required will increase but isn't it justified from user perspective
since we are allowing more transactions per session and hence memory required to store various kind of resources
will increase.

Since we are just additionally setting the bitmask for each lock (in-case there is autonomous transaction, then there will
be one more additional bit mask setting and deadlock check), I don't think it should slow down the overall operation.

Also We can keep number of autonomous transaction configurable(default-0), to keep it less impacting incase it is not configured.

An autonomous transaction can also conflict with main transaction, so in order to check conflict between them,
I am distinguishing at this level.

Please correct me If I am wrong anywhere and also please provide your thought on this and on overall design.

Thanks and Regards,
Kumar Rajeev Rastogi

On 7 April 2014 05:06, Rajeev rastogi <rajeev(dot)rastogi(at)huawei(dot)com> wrote:

> *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*
>

I already proposed exactly this design two years ago and it was rejected at
the PgCon hackers meeting.

I have a better design worked out now and will likely be working on it for
9.5

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services