Re: Autonomous Transaction is back

On Fri, Jul 24, 2015 at 9:39 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> On Thu, Jul 23, 2015 at 1:49 PM, Josh Berkus <josh(at)agliodbs(dot)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.