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.