Re: Autonomous Transaction is back

On Thu, Jul 23, 2015 at 1:31 AM, Rajeev rastogi
<rajeev(dot)rastogi(at)huawei(dot)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