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