Autonomous Transaction is back

On Thu, Jul 23, 2015 at 1:31 AM, Rajeev rastogi
<rajeev.rastogi@huawei.com> wrote:

2. It should be allowed to deadlock with master transaction. We
need to work-out a solution to avoid deadlock.

This sentence seems to contradict itself. I thought the consensus was
that the transaction should NOT conflict with the master transaction.

3. It can support multiple level of nesting based on the
configuration (may be max as 70).

Why have any fixed maximum?

2. The above commands can be issued either inside the procedure to make few
statements of procedure inside autonomous transaction or even in stand-alone
query execution.

I think inside a procedure the autonomous transaction will need to be
lexically scoped. You won't be able to do this, for example:

BEGIN AUTONOMOUS TRANSACTION;
FOR x IN SELECT ... LOOP
COMMIT;
BEGIN AUTONOMOUS TRANSACTION;
END LOOP;

Rather you'd have to do something like this:

FOR x IN SELECT .. LOOP
BEGIN WITH AUTONOMOUS TRANSACTION
do stuff
END;
END LOOP;

In that way it would work like an EXCEPTION block, which can be
attached to a begin block to establish a subtransaction. There's no
way in PL/pgsql to persistently enter a subtransaction; but you can
have one that applies to a particular lexical scope (the BEGIN block
to which EXCEPTION is attached).

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

Requesting for everyone's opinion regarding this based on which we can
proceed to enhance/tune/re-write our design.

So, one of the things which came up during the discussion was advancing
XMIN, which is not important to the audit logging use case, but is very
important for the batch job use case. What have you concluded regarding
this item?

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Jul 23, 2015 at 2:33 PM, Josh Berkus <josh@agliodbs.com> wrote:

Requesting for everyone's opinion regarding this based on which we can
proceed to enhance/tune/re-write our design.

So, one of the things which came up during the discussion was advancing
XMIN, which is not important to the audit logging use case, but is very
important for the batch job use case. What have you concluded regarding
this item?

Could you explain more specifically what you are talking about here?

--
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 07/23/2015 11:39 AM, Robert Haas wrote:

On Thu, Jul 23, 2015 at 2:33 PM, Josh Berkus <josh@agliodbs.com> wrote:

Requesting for everyone's opinion regarding this based on which we can
proceed to enhance/tune/re-write our design.

So, one of the things which came up during the discussion was advancing
XMIN, which is not important to the audit logging use case, but is very
important for the batch job use case. What have you concluded regarding
this item?

Could you explain more specifically what you are talking about here?

Yeah, my notes are kinda incoherent, no?

There's two core use-cases for Autonomous Transactions (hereafter ATX):

* audit logging
* batch jobs

Audit Logging: triggers or other statements which should leave a record
even when a transaction aborts. While audit logging is the main example
of this use case, any kind of logging of failures is applicable.

Batch Jobs: large data-manipulation tasks which need to be broken up
into segments, with each segment committing separately. Example:
updating 1 million records in batches of 1000.

Unlike the Audit Logging case, Batch Jobs generally want XMIN to advance
so that updated/imported/deleted rows can be vacuumed or HOT updated.
Thus the need to allow XMIN to advance.

One of the things we kind of concluded from our discussion was that the
two core use-cases are probably different features:

Audit Logging:
* requires 2-way data interaction with outer transaction
* no parallelism
* XMIN does not need to advance
* master transaction should still commit/fail
* needs to support nesting

Batch Jobs:
* 1-way data interaction sufficient (master-->child)
* parallelism desired
* XMIN should advance
* master process could be transactionless
* does not need to support nesting

Of these two, the Audit Logging case is the more important one to
implement because there is no real workaround for it. Batch Jobs can,
and are, handled by external scripting, and having ATX for them is more
of a convenience than anything else.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Jul 23, 2015 at 2:49 PM, Josh Berkus <josh@agliodbs.com> wrote:

On 07/23/2015 11:39 AM, Robert Haas wrote:

On Thu, Jul 23, 2015 at 2:33 PM, Josh Berkus <josh@agliodbs.com> wrote:

Requesting for everyone's opinion regarding this based on which we can
proceed to enhance/tune/re-write our design.

So, one of the things which came up during the discussion was advancing
XMIN, which is not important to the audit logging use case, but is very
important for the batch job use case. What have you concluded regarding
this item?

Could you explain more specifically what you are talking about here?

Yeah, my notes are kinda incoherent, no?

There's two core use-cases for Autonomous Transactions (hereafter ATX):

* audit logging
* batch jobs

Audit Logging: triggers or other statements which should leave a record
even when a transaction aborts. While audit logging is the main example
of this use case, any kind of logging of failures is applicable.

Batch Jobs: large data-manipulation tasks which need to be broken up
into segments, with each segment committing separately. Example:
updating 1 million records in batches of 1000.

Unlike the Audit Logging case, Batch Jobs generally want XMIN to advance
so that updated/imported/deleted rows can be vacuumed or HOT updated.
Thus the need to allow XMIN to advance.

One of the things we kind of concluded from our discussion was that the
two core use-cases are probably different features:

Audit Logging:
* requires 2-way data interaction with outer transaction
* no parallelism
* XMIN does not need to advance
* master transaction should still commit/fail
* needs to support nesting

Batch Jobs:
* 1-way data interaction sufficient (master-->child)
* parallelism desired
* XMIN should advance
* master process could be transactionless
* does not need to support nesting

Of these two, the Audit Logging case is the more important one to
implement because there is no real workaround for it. Batch Jobs can,
and are, handled by external scripting, and having ATX for them is more
of a convenience than anything else.

You're still not really explaining what you mean by "xmin should
advance". If the parent transaction holds a snapshot, or for as long
as it does, xmin can't be advanced safely. If it doesn't, you'll be
fine. I suppose the situation you're worried about is where we
execute a stored procedure that repeatedly spawns autonomous
transactions. Since the parent transaction will always have a
snapshot, you won't advance xmin until the entire stored procedure
finishes.

That's a problem, but I think it is rather unfair to say that it has
anything to do with autonomous transactions. "Run a procedure without
needing to hold a snapshot" is a completely separate feature request
from "allow autonomous transactions", and it's probably neither easy
nor uncontroversial.

--
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 07/23/2015 12:40 PM, Robert Haas wrote:

That's a problem, but I think it is rather unfair to say that it has
anything to do with autonomous transactions. "Run a procedure without
needing to hold a snapshot" is a completely separate feature request
from "allow autonomous transactions", and it's probably neither easy
nor uncontroversial.

That's more-or-less a summary of what I just posted.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Jul 23, 2015 at 11:01 AM, Rajeev rastogi <rajeev.rastogi@huawei.com>
wrote:

After few failed attempt to propose Autonomous transaction earlier. I
along with Simon Riggs would like to propose again but completely different
in approach.

We also had discussion about this feature in last PGCon2015 Unconference
Day, those who missed this discussion, please refer

https://wiki.postgresql.org/wiki/AutonomousTransactionsUnconference2015

Before jumping into the design and code proposal for this feature, me
along with Simon Riggs wanted to propose its behavior and usage to keep
everyone in the same boat.

So we have summarized the behavior and usage of the Autonomous Transaction
based on the discussion with community members in last PGCon2015
Unconference Day:

*Behavior of Autonomous Transaction*:

1. The autonomous transaction treated as a completely different
transaction from the master transaction.

2. It should be allowed to deadlock with master transaction. We
need to work-out a solution to avoid deadlock.

For an autonomous transaction not to conflict with the master, either it
shouldn't take conflicting resources or when it does so, they should be
shared by the master transaction. The first needs to be implemented in an
application, the later will need careful design of how to share such
resources (hierarchial resource sharing?) and how to release the resources
when the ATX ends. Given that the transactions are autonomous as per point
1, they should be allowed to conflict and the deadlock detection would take
care of such deadlocks.

3. It can support multiple level of nesting based on the
configuration (may be max as 70).

This might just be an implementation artifact in other RDBMSes, and may not
necessarily apply to the implementation done here. So, we should not list
it as "necessary" behaviour of ATX. Just as an example (no way suggesting
that the design should be this way), if we implement autonomous
transactions using background worker processes or additional backends like
prepared transactions, we will have a limit on the number of simultaneously
open ATX. This may translate to nesting limit if we implement 4, but not if
we implement parallel autonomous transactions.

4. Outer (i.e. main or upper autonomous) transaction to be
suspended while the inner autonomous transaction is running.

5. Outer transaction should not see data of inner till inner is
committed (serializable upper transaction should not see even after inner
transaction commit).

To me only 1 and 5 seem to be the real behaviour we should define, may be
4, but that restricts parallelism. Although parallel autonomous
transactions will have its own problems to solve like sharing code. But the
design shouldn't restrict it from being a future possibility, I guess.

*How to Use Autonomous Transaction*:

1. We can issue explicit command to start an Autonomous transaction as
below:

BEGIN AUTONOMOUS TRANSACTION (Don’t worry about keywords
at this point.)

Do you work.

COMMIT/ROLLBACK (Will commit/rollback the autonomous
transaction and will return to main transaction or upper autonomous
transaction).

2. The above commands can be issued either inside the procedure to make
few statements of procedure inside autonomous transaction or even in
stand-alone query execution.

3. We can make whole procedure itself as autonomous, which will be similar
to start autonomous transaction in the beginning of the procedure and
commit/rollback at the end of the procedure.

There was another discussion in Unconference Day to decide whether to
implement COMMIT/ROLLBACK inside the procedure or autonomous transaction.
So our opinion about this is that

COMMIT/ROLLBACK inside procedure will be somewhat
different from Autonomous Transaction as incase of first, once we commit
inside the procedure,

it commits everything done before call of procedure. This is the behavior
of Oracle.

So in this case user required to be very careful to not do any operation
before call of procedure, which is not yet intended to be committed inside
procedure.

So we can prefer to implement Autonomous Transaction, which will not only
be compatible with Oracle but also gives really strong required features.

I have not put the use-cases here as already we agree about its strong
use-cases.

Requesting for everyone's opinion regarding this based on which we can
proceed to enhance/tune/re-write our design.

*Thanks and Regards,*

*Kumar Rajeev Rastogi *

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

On Thu, Jul 23, 2015 at 1:49 PM, Josh Berkus <josh@agliodbs.com> wrote:

Batch Jobs: large data-manipulation tasks which need to be broken up
into segments, with each segment committing separately. Example:
updating 1 million records in batches of 1000.

Autonomous transactions are not a good fit for this case; stored
procedures are a better way to go for any scenario where you don't
want be be in a snapshot (for example, suppose you want to change
isolation level on the fly).

merlin

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 23 July 2015 21:04, Robert Haas Wrote:
On Thu, Jul 23, 2015 at 1:31 AM, Rajeev rastogi <rajeev.rastogi@huawei.com> wrote:

2. It should be allowed to deadlock with master transaction. We
need to work-out a solution to avoid deadlock.

This sentence seems to contradict itself. I thought the consensus was that the transaction should NOT conflict with the master transaction.

Since we are saying transaction is autonomous to parent transaction, we cannot guarantee that it does not take any conflicting lock unless otherwise designed so by the application.
But yes, we should have mechanism to deal with the possible deadlock.

3. It can support multiple level of nesting based on the
configuration (may be max as 70).

Why have any fixed maximum?

Since we are planning to have nested autonomous transaction, so it is required to have limit on this so that resources can be controlled.

2. The above commands can be issued either inside the procedure to
make few statements of procedure inside autonomous transaction or even
in stand-alone query execution.

I think inside a procedure the autonomous transaction will need to be lexically scoped. You won't be able to do this, for example:

BEGIN AUTONOMOUS TRANSACTION;
FOR x IN SELECT ... LOOP
COMMIT;
BEGIN AUTONOMOUS TRANSACTION;
END LOOP;

I am not sure, how we will be able to control this. IMHO user should be able to control this, especially since it does not have any meaning from user perspective.
Please let me know if I am missing something here.

Rather you'd have to do something like this:

FOR x IN SELECT .. LOOP
BEGIN WITH AUTONOMOUS TRANSACTION
do stuff
END;
END LOOP;

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 Fri, Jul 24, 2015 at 9:39 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Thu, Jul 23, 2015 at 1:49 PM, Josh Berkus <josh@agliodbs.com> wrote:

Batch Jobs: large data-manipulation tasks which need to be broken up
into segments, with each segment committing separately. Example:
updating 1 million records in batches of 1000.

Autonomous transactions are not a good fit for this case; stored
procedures are a better way to go for any scenario where you don't
want be be in a snapshot (for example, suppose you want to change
isolation level on the fly).

Hm, you mean we need real "stored procedures" in PostgreSQL and not just
"functions"?

If not, I think it would be sufficient to add Autonomous Transaction
support to the type of functions we already have in pg to allow writing a
batch job function which would commit after X numbers of modified rows,
instead of having to write a script in an external language such as Perl to
call the function in a while-loop and commit in between each function call.

However, we should also add a way for the caller to protect against an
Autonomous Transaction in a function called by the caller. Imagine if
you're the author of function X() and within X() make use of some other
function Y() which has been written by some other author, and within your
function X(), it's very important either all of your work or none at all
gets committed, then you need to make sure none of the changes you made
before calling Y() gets committed, and thus we need a way to prevent Y()
from starting and committing an Autonomous Transaction, otherwise we would
increase the risk and complexity of working with functions and plpgsql in
PostgreSQL as you would then need to be sure none of the functions you are
using within a function will start and commit an ATX.

On 07/27/2015 02:41 PM, Joel Jacobson wrote:

However, we should also add a way for the caller to protect against an
Autonomous Transaction in a function called by the caller. Imagine if
you're the author of function X() and within X() make use of some other
function Y() which has been written by some other author, and within
your function X(), it's very important either all of your work or none
at all gets committed, then you need to make sure none of the changes
you made before calling Y() gets committed, and thus we need a way to
prevent Y() from starting and committing an Autonomous Transaction,
otherwise we would increase the risk and complexity of working with
functions and plpgsql in PostgreSQL as you would then need to be sure
none of the functions you are using within a function will start and
commit an ATX.

Ah, you're missing how commits in ATX are expected to work. Let me
illustrate:

X (
Data write A1
call Y(
Start ATX
Data write B1
Commit ATX
)
Data write A2
Exception
)

In this workflow, B1 would be committed and persistent. Neither A1 nor
A2 would be committed, or visible to other users. Depending on what
implementation we end up with, A1 might not even be visible to Y().

So that solves your use case without any need to "block" ATXs in called
functions. However, it leads to some interesting cases involving
self-deadlocks; see the original post on this thread.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--
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/27/2015 02:47 AM, Rajeev rastogi wrote:

Why have any fixed maximum?

Since we are planning to have nested autonomous transaction, so it is required to have limit on this so that resources can be controlled.

Is there a particular reason why this limit wouldn't just be
max_stack_depth?

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Jul 27, 2015 at 4:41 PM, Joel Jacobson <joel@trustly.com> wrote:

On Fri, Jul 24, 2015 at 9:39 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Thu, Jul 23, 2015 at 1:49 PM, Josh Berkus <josh@agliodbs.com> wrote:

Batch Jobs: large data-manipulation tasks which need to be broken up
into segments, with each segment committing separately. Example:
updating 1 million records in batches of 1000.

Autonomous transactions are not a good fit for this case; stored
procedures are a better way to go for any scenario where you don't
want be be in a snapshot (for example, suppose you want to change
isolation level on the fly).

Hm, you mean we need real "stored procedures" in PostgreSQL and not just
"functions"?

Yes, exactly.

Autonomous transactions aren't really set up for cases where the
function runs for a very long time or indefinitely. This is the
'advancing xmin' problem as Josh puts it but I think the problem is
much bigger than that. Anyways, this is mostly irrelevant to
autonomous transactions as long as the design isn't extended to try
and cover that case.

Is the Autonomous Transaction feature only going to be exposed through pl/pgsql?

merlin

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Jul 27, 2015 at 11:49 PM, Josh Berkus <josh@agliodbs.com> wrote:

Ah, you're missing how commits in ATX are expected to work. Let me
illustrate:

X (
Data write A1
call Y(
Start ATX
Data write B1
Commit ATX
)
Data write A2
Exception
)

In this workflow, B1 would be committed and persistent. Neither A1 nor
A2 would be committed, or visible to other users. Depending on what
implementation we end up with, A1 might not even be visible to Y().

So that solves your use case without any need to "block" ATXs in called
functions. However, it leads to some interesting cases involving
self-deadlocks; see the original post on this thread.

I don't follow. In your example above, if I'm X(), how do I ensure Y()
won't have committed anyting at all when I later at "Exception" decide to
rollback everything from "Data write A1" to "Data write A2" including any
writes made by Y() (in the example "Data write B1")?

I understand the "Exception" will take care of rollbacking my (X's) writes,
but that's not sufficient if you want to make sure you rollback
*everything*, including any writes made by functions you call.

Right now, when writing a function, if you raise an exception, you can be
sure all writes you have made will be rollbacked, but your caller function
might caught the exception and decide to carry on and commit work made
before your function was called, but at least you can be confident your
writes won't be committed as long as you don't caught the exception you
raised in your own function. If I understand it correctly, that would
change with the addition of Autonomous Transaction, unless given a way to
prevent a function you call from starting and commiting a Autonomous
Transaction. Wrong? If so, then please show how to prevent Y() from
commiting the "Data write B1" in your example, I don't get it.

On 07/27/2015 03:12 PM, Joel Jacobson wrote:

On Mon, Jul 27, 2015 at 11:49 PM, Josh Berkus <josh@agliodbs.com
<mailto:josh@agliodbs.com>> wrote:

Ah, you're missing how commits in ATX are expected to work. Let me
illustrate:

X (
Data write A1
call Y(
Start ATX
Data write B1
Commit ATX
)
Data write A2
Exception
)

In this workflow, B1 would be committed and persistent. Neither A1 nor
A2 would be committed, or visible to other users. Depending on what
implementation we end up with, A1 might not even be visible to Y().

So that solves your use case without any need to "block" ATXs in called
functions. However, it leads to some interesting cases involving
self-deadlocks; see the original post on this thread.

I don't follow. In your example above, if I'm X(), how do I ensure Y()
won't have committed anyting at all when I later at "Exception" decide
to rollback everything from "Data write A1" to "Data write A2" including
any writes made by Y() (in the example "Data write B1")?

Ah, ok. The goal of the project is that the writer of X() *cannot*
prevent Y() from writing its data (B1) and committing it.

One of the primary use cases for ATX is audit triggers. If a function
writer could override ATX and prevent the audit triggers from
committing, then that use case would be violated.

Can you explain what use case you have where simply telling the staff
"if you use ATX without clearing it, you'll be fired" is not sufficient?
Possibly there's something we failed to account for in the unconference
discussion.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 7/27/15 5:12 PM, Joel Jacobson wrote:

Right now, when writing a function, if you raise an exception, you can
be sure all writes you have made will be rollbacked, but your caller
function might caught the exception and decide to carry on and commit
work made before your function was called, but at least you can be
confident your writes won't be committed as long as you don't caught the
exception you raised in your own function. If I understand it correctly,
that would change with the addition of Autonomous Transaction, unless
given a way to prevent a function you call from starting and commiting
a Autonomous Transaction. Wrong? If so, then please show how to prevent
Y() from commiting the "Data write B1" in your example, I don't get it.

What's being described here doesn't make sense in either use case ([1]the "batch process" use case: batches that still hold their own transaction open don't gain anything. &
[2]: the "audit logging" case. If you didn't care about auditing surviving regardless of a rollback then you wouldn't go to the extra work of an autonomous transaction to begin with. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Data in Trouble? Get it in Treble! http://BlueTreble.com
doing. It would be nice to have the ability to disallow and/or disable
autonomous transactions, but I don't see a practical way of doing that
other than introducing a new GUC. I'm not sure if it's worth that effort.

[1]: the "batch process" use case: batches that still hold their own transaction open don't gain anything.
transaction open don't gain anything.

[2]: the "audit logging" case. If you didn't care about auditing surviving regardless of a rollback then you wouldn't go to the extra work of an autonomous transaction to begin with. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Data in Trouble? Get it in Treble! http://BlueTreble.com
surviving regardless of a rollback then you wouldn't go to the extra
work of an autonomous transaction to begin with.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Data in Trouble? Get it in Treble! http://BlueTreble.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 7/27/15 5:56 PM, Josh Berkus wrote:

Can you explain what use case you have where simply telling the staff
"if you use ATX without clearing it, you'll be fired" is not sufficient?
Possibly there's something we failed to account for in the unconference
discussion.

That there's no way to enforce that, short of hand-auditing code?
There's already enough things that are difficult/impossible to enforce,
I'd rather not add another one.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Data in Trouble? Get it in Treble! http://BlueTreble.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 7/27/15 6:40 PM, Jim Nasby wrote:

On 7/27/15 5:12 PM, Joel Jacobson wrote:

Right now, when writing a function, if you raise an exception, you can
be sure all writes you have made will be rollbacked, but your caller
function might caught the exception and decide to carry on and commit
work made before your function was called, but at least you can be
confident your writes won't be committed as long as you don't caught the
exception you raised in your own function. If I understand it correctly,
that would change with the addition of Autonomous Transaction, unless
given a way to prevent a function you call from starting and commiting
a Autonomous Transaction. Wrong? If so, then please show how to prevent
Y() from commiting the "Data write B1" in your example, I don't get it.

What's being described here doesn't make sense in either use case ([1] &
[2]), but I do understand the concern about what 3rd party software is
doing. It would be nice to have the ability to disallow and/or disable
autonomous transactions, but I don't see a practical way of doing that
other than introducing a new GUC. I'm not sure if it's worth that effort.

It just occurred to me that another option would be to have an event
trigger for beginning an autonomous transaction.

[1] the "batch process" use case: batches that still hold their own
transaction open don't gain anything.

[2] the "audit logging" case. If you didn't care about auditing
surviving regardless of a rollback then you wouldn't go to the extra
work of an autonomous transaction to begin with.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Data in Trouble? Get it in Treble! http://BlueTreble.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2015-07-27 23:59 GMT+02:00 Merlin Moncure <mmoncure@gmail.com>:

On Mon, Jul 27, 2015 at 4:41 PM, Joel Jacobson <joel@trustly.com> wrote:

On Fri, Jul 24, 2015 at 9:39 PM, Merlin Moncure <mmoncure@gmail.com>

wrote:

On Thu, Jul 23, 2015 at 1:49 PM, Josh Berkus <josh@agliodbs.com> wrote:

Batch Jobs: large data-manipulation tasks which need to be broken up
into segments, with each segment committing separately. Example:
updating 1 million records in batches of 1000.

Autonomous transactions are not a good fit for this case; stored
procedures are a better way to go for any scenario where you don't
want be be in a snapshot (for example, suppose you want to change
isolation level on the fly).

Hm, you mean we need real "stored procedures" in PostgreSQL and not just
"functions"?

Yes, exactly.

Autonomous transactions aren't really set up for cases where the
function runs for a very long time or indefinitely. This is the
'advancing xmin' problem as Josh puts it but I think the problem is
much bigger than that. Anyways, this is mostly irrelevant to
autonomous transactions as long as the design isn't extended to try
and cover that case.

Is the Autonomous Transaction feature only going to be exposed through
pl/pgsql?

I hope not.

The integration with plpgsql can be secondary question. In this case I
prefer a relation to block statement without possibility to explicit
COMMIT. Minimally in functions.

some like

BEGIN
BEGIN AUTONOMOUS
...
END;
END;

This is consistent with current subtransaction support, and disallow some
corner cases like forgotten COMMIT.

Regards

Pavel