Re: Autonomous Transaction is back

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