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.
