On 01 July 2014 12:26, Pavel Stehule Wrote:
>>Have you checked the discussion in Developer meeting notes. Please
>>check the same at below link:
>>http://wiki.postgresql.org/wiki/PgCon_2012_Developer_Meeting#Autonomous_Transactions
>Are these notes still valid?
>* Why autonomous transaction should be close to functions? We can implement
>AT as first step and next step can be implementation of integration AT to
>stored procedures.
We have implemented AT on the line of sub-transaction. Also we have integrated
AT with stored procedure i.e. we can create an autonomous transaction inside
the store procedure, which can be also committed.
>* When autonomous transaction is independent on parent transaction, then locks
>parent and autonomous transaction should be in conflict
Yes our implementation makes the autonomous transaction independent of main
transaction and hence as per our design parent (main) transaction and
autonomous may get conflicted. For which we have implemented deadlock
detection mechanism between autonomous transaction and its parent transaction.
> I though about integration to PL/pgSQL and I don't think so close integration
> between autonomous transaction and procedure is optimal. More practical is
> design so autonomous transaction is similar to subtransaction.
Yes as mentioned above, our implementation of autonomous transaction is on
track of subtransaction.
>Then we can simply wrote some code like
> BEGIN
> .. some code
> WHEN OTHERS THEN
> .. I would to write permanently to log
> BEGIN AUTONOMOUS
> INSERT INTO log VALUES(..);
> WHEN OTHERS
> RAISE WARNING 'Cannot to write to log ..';
> RAISE EXCEPTION ' ...' forward up exception from autonomous transaction
> to parent transaction
> END
> END;
>Now I am thinking so PL/SQL design of autonomous transactions is relatively
>limited and is not best to follow it.
With our approach, we can use autonomous transaction in procedure as given
below:
BEGIN
.. some code
WHEN OTHERS THEN
.. I would to write permanently to log
START AUTONOMOUS TRANSACTION
INSERT INTO log VALUES(..);
COMMIT:
WHEN OTHERS
RAISE WARNING 'Cannot to write to log ..';
RAISE EXCEPTION ' ...' forward up exception from autonomous transaction
to parent transaction
END
END;
Please let me know if I have missed to answer any of your queries.
Thanks and Regards,
Kumar Rajeev Rastogi
