Re: Autonomous Transaction is back

On Thu, Jul 23, 2015 at 2:49 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> On 07/23/2015 11:39 AM, Robert Haas wrote:
>> On Thu, Jul 23, 2015 at 2:33 PM, Josh Berkus <josh(at)agliodbs(dot)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