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
Attachments:
autonomous_tx_v0.patchapplication/octet-stream; name=autonomous_tx_v0.patchDownload+1882-280
Hello
+1 for feature
-1 for Oracle syntax - it is hardly inconsistent with PostgresAutonomous transactions should be used everywhere - not only in plpgsql
Regards
Pavel
2014-04-07 6:06 GMT+02:00 Rajeev rastogi <rajeev.rastogi@huawei.com>:
Show quoted text
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 belowSay 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
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 07 April 2014 12:12, Pavel Stehule wrote:
+1 for feature
Thanks
-1 for Oracle syntax - it is hardly inconsistent with Postgres
We can discuss and come out with the syntax based on everyone agreement.
Autonomous transactions should be used everywhere - not only in plpgsql
Yes you are right. I am not planning to support only using plpgsql. Initially we can support this
Using the standalone SQL-commands and then later we can enhance based on this infrastructure
to be used using plpgsql, triggers.
Thanks and Regards,
Kumar Rajeev Rastogi
Import Notes
Resolved by subject fallback
2014-04-07 11:59 GMT+02:00 Rajeev rastogi <rajeev.rastogi@huawei.com>:
On 07 April 2014 12:12, Pavel Stehule wrote:
+1 for feature
Thanks
-1 for Oracle syntax - it is hardly inconsistent with Postgres
We can discuss and come out with the syntax based on everyone agreement.
Autonomous transactions should be used everywhere - not only in plpgsql
Yes you are right. I am not planning to support only using plpgsql.
Initially we can support thisUsing the standalone SQL-commands and then later we can enhance based on
this infrastructureto be used using plpgsql, triggers.
ok
long time I though about this feature.
I am thinking so this should be fully isolated transaction - it should not
be subtransaction, because then you can break database consistency - RI
I am happy so someone does this job
Regards
Pavel
Show quoted text
*Thanks and Regards,*
*Kumar Rajeev Rastogi *
On Mon, Apr 7, 2014 at 3:41 PM, Pavel Stehule <pavel.stehule@gmail.com>wrote:
2014-04-07 11:59 GMT+02:00 Rajeev rastogi <rajeev.rastogi@huawei.com>:
On 07 April 2014 12:12, Pavel Stehule wrote:
+1 for feature
Thanks
-1 for Oracle syntax - it is hardly inconsistent with Postgres
We can discuss and come out with the syntax based on everyone agreement.
Autonomous transactions should be used everywhere - not only in plpgsql
Yes you are right. I am not planning to support only using plpgsql.
Initially we can support thisUsing the standalone SQL-commands and then later we can enhance based on
this infrastructureto be used using plpgsql, triggers.
ok
long time I though about this feature.
I am thinking so this should be fully isolated transaction - it should not
be subtransaction, because then you can break database consistency - RI
I am missing something here, but how does making it a subtransaction break
consistency? Isnt that what should actually be happening so that the
autonomous transaction's changes are actually visible till the parent
transaction commits?
What am I missing here?
Regards,
Atri
On 2014-04-07 15:46:42 +0530, Atri Sharma wrote:
On Mon, Apr 7, 2014 at 3:41 PM, Pavel Stehule <pavel.stehule@gmail.com>wrote:
I am missing something here, but how does making it a subtransaction break
consistency? Isnt that what should actually be happening so that the
autonomous transaction's changes are actually visible till the parent
transaction commits?What am I missing here?
START TRANSACTION;
INSERT INTO referenced_to_table ... id = 1;
START AUTONOMOUS SUBTRANSACTION;
INSERT INTO referencing_table id = 1 ...;
COMMIT AUTONOMOUS SUBTRANSACTION;
ROLLBACK;
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
2014-04-07 12:16 GMT+02:00 Atri Sharma <atri.jiit@gmail.com>:
On Mon, Apr 7, 2014 at 3:41 PM, Pavel Stehule <pavel.stehule@gmail.com>wrote:
2014-04-07 11:59 GMT+02:00 Rajeev rastogi <rajeev.rastogi@huawei.com>:
On 07 April 2014 12:12, Pavel Stehule wrote:
+1 for feature
Thanks
-1 for Oracle syntax - it is hardly inconsistent with Postgres
We can discuss and come out with the syntax based on everyone agreement.
Autonomous transactions should be used everywhere - not only in plpgsql
Yes you are right. I am not planning to support only using plpgsql.
Initially we can support thisUsing the standalone SQL-commands and then later we can enhance based on
this infrastructureto be used using plpgsql, triggers.
ok
long time I though about this feature.
I am thinking so this should be fully isolated transaction - it should
not be subtransaction, because then you can break database consistency - RII am missing something here, but how does making it a subtransaction break
consistency? Isnt that what should actually be happening so that the
autonomous transaction's changes are actually visible till the parent
transaction commits?
commit of autonomous transaction doesn't depends on outer transaction. So
anything what you can do, should be independent on outer transaction.
Pavel
Show quoted text
What am I missing here?
Regards,
Atri
On Mon, Apr 7, 2014 at 12:06 AM, Rajeev rastogi
<rajeev.rastogi@huawei.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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, Apr 8, 2014 at 2:43 PM, Alvaro Herrera <alvherre@2ndquadrant.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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Robert Haas <robertmhaas@gmail.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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2014-04-08 16:13:21 -0400, Tom Lane wrote:
Robert Haas <robertmhaas@gmail.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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
Attachments:
autonomous_tx_v1.patchapplication/octet-stream; name=autonomous_tx_v1.patchDownload+1936-288
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Apr 9, 2014 at 11:03 AM, Rajeev rastogi
<rajeev.rastogi@huawei.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
