Re: Autonomous Transaction (WIP)

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