Re: Autonomous Transaction (WIP)

<div class="WordSection1"><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">On</span><span
style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">01July 2014 12:26, Pavel Stehule Wrote:</span><p
class="MsoNormal"><spanstyle="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> </span><p
class="MsoNormal">>>Haveyou checked the discussion in Developer meeting notes. Please<p
class="MsoNormal">>>checkthe same at below link:<p class="MsoNormal">>><a
href="http://wiki.postgresql.org/wiki/PgCon_2012_Developer_Meeting#Autonomous_Transactions"
target="_blank">http://wiki.postgresql.org/wiki/PgCon_2012_Developer_Meeting#Autonomous_Transactions</a><p
class="MsoNormal"> <pclass="MsoNormal" style="margin-bottom:12.0pt">>Are these notes still valid?<p
class="MsoNormal"style="margin-bottom:12.0pt">>* Why autonomous transaction should be close to  functions? We can
implementAT as first step and next step can be implementation of integration AT to stored procedures.<p
class="MsoNormal"style="margin-bottom:12.0pt">We have implemented AT on the line of sub-transaction. Also we have
integratedAT with stored procedure i.e. we can create an autonomous transaction inside the store procedure, which can
bealso committed.<p class="MsoNormal" style="margin-bottom:12.0pt">>* When autonomous transaction is independent on
parenttransaction, then locks parent and autonomous transaction should be in conflict<p class="MsoNormal"
style="margin-bottom:12.0pt">Yesour implementation makes the autonomous transaction independent of main transaction and
henceas per our design parent (main) transaction and autonomous may get conflicted.  For which we have implemented
deadlockdetection mechanism between autonomous transaction and its parent transaction.<p class="MsoNormal"
style="margin-bottom:12.0pt">>I though about integration to PL/pgSQL and I don't think so close integration between
autonomoustransaction and procedure is optimal. More practical is design so autonomous transaction is similar to
subtransaction.<pclass="MsoNormal" style="margin-bottom:12.0pt">Yes as mentioned above, our implementation of
autonomoustransaction is on track of subtransaction.<p class="MsoNormal" style="margin-bottom:12.0pt">>Then we can
simplywrote some code like<p class="MsoNormal">>  BEGIN<p class="MsoNormal">>    .. some code<p
class="MsoNormal">> WHEN OTHERS THEN<p class="MsoNormal">>    .. I would to write permanently to log <p
class="MsoNormal">>   BEGIN AUTONOMOUS<p class="MsoNormal">>      INSERT INTO log VALUES(..);<p
class="MsoNormal">>   WHEN OTHERS<p class="MsoNormal">>      RAISE WARNING 'Cannot to write to log ..';<p
class="MsoNormal">>     RAISE EXCEPTION ' ...' forward up exception from autonomous transaction to parent
transaction<pclass="MsoNormal">>    END<p class="MsoNormal" style="margin-bottom:12.0pt">>  END; <p
class="MsoNormal"style="margin-bottom:12.0pt">>Now I am thinking so PL/SQL design of autonomous transactions is
relativelylimited and is not best to follow it.<p class="MsoNormal" style="margin-bottom:12.0pt">With our approach, we
canuse autonomous transaction in procedure as given below:<p class="MsoNormal">  BEGIN<p class="MsoNormal">    .. some
code<pclass="MsoNormal">  WHEN OTHERS THEN<p class="MsoNormal">    .. I would to write permanently to log <p
class="MsoNormal">   <b>START AUTONOMOUS TRANSACTION</b><p class="MsoNormal">      INSERT INTO log VALUES(..);<p
class="MsoNormal">  <b>COMMIT:  </b><p class="MsoNormal">    WHEN OTHERS<p class="MsoNormal">      RAISE WARNING
'Cannotto write to log ..';<p class="MsoNormal">      RAISE EXCEPTION ' ...' forward up exception from autonomous
transactionto parent transaction<p class="MsoNormal">    END<p class="MsoNormal" style="margin-bottom:12.0pt">  END; <p
class="MsoNormal"style="margin-bottom:12.0pt">Please let me know if I have missed to answer any of your queries.<p
class="MsoNormal"style="margin-bottom:12.0pt">Thanks and Regards,<p class="MsoNormal"
style="margin-bottom:12.0pt">KumarRajeev Rastogi<p class="MsoNormal" style="margin-bottom:12.0pt"> <p class="MsoNormal"
style="margin-bottom:12.0pt"> </div>