Autonomous Transaction is back

<div class="WordSection1"><p class="MsoNormal">After few failed attempt to propose Autonomous transaction earlier. I
alongwith Simon Riggs would like to propose again but completely different in approach.<p class="MsoNormal"> <p
class="MsoNormal">Wealso had discussion about this feature in last PGCon2015 Unconference Day, those who missed this
discussion,please refer <p class="MsoNormal">                                <a
href="https://wiki.postgresql.org/wiki/AutonomousTransactionsUnconference2015">
https://wiki.postgresql.org/wiki/AutonomousTransactionsUnconference2015</a><pclass="MsoNormal"> <p
class="MsoNormal"> <pclass="MsoNormal">Before jumping into the design and code proposal for this feature, me along with
SimonRiggs wanted to propose its behavior and usage to keep everyone in the same boat.<p class="MsoNormal">So we have
summarizedthe behavior and usage of the Autonomous Transaction based on the discussion with community members in last
PGCon2015Unconference Day:<p class="MsoNormal"> <p class="MsoNormal"><b>Behavior of Autonomous Transaction</b>:<p
class="MsoNormal">1.           The autonomous transaction treated as a completely different transaction from the master
transaction.<pclass="MsoNormal">2.            It should be allowed to deadlock with master transaction. We need to
work-outa solution to avoid deadlock.<p class="MsoNormal">3.            It can support multiple level of nesting based
onthe configuration (may be max as 70).<p class="MsoNormal">4.            Outer (i.e. main or upper autonomous)
transactionto be suspended while the inner autonomous transaction is running.<p class="MsoNormal">5.            Outer
transactionshould not see data of inner till inner is committed (serializable upper transaction should not see even
afterinner transaction commit).<p class="MsoNormal"> <p class="MsoNormal"><b>How to Use Autonomous Transaction</b>:<p
class="MsoNormal">1.We can issue explicit command to start an Autonomous transaction as below:<p
class="MsoNormal">               BEGIN AUTONOMOUS TRANSACTION  (Don’t worry about keywords at this point.)<p
class="MsoNormal">               Do you work.<p class="MsoNormal">                COMMIT/ROLLBACK       (Will
commit/rollbackthe autonomous transaction and will return to main transaction or upper autonomous
transaction).             <p class="MsoNormal"> <p class="MsoNormal">2. The above commands can be issued either inside
theprocedure to make few statements of procedure inside autonomous transaction or even in stand-alone query
execution.<pclass="MsoNormal">3. We can make whole procedure itself as autonomous, which will be similar to start
autonomoustransaction in the beginning of the procedure and commit/rollback at the end of the procedure.   <p
class="MsoNormal"> <pclass="MsoNormal">There was another discussion in Unconference Day to decide whether to implement
COMMIT/ROLLBACKinside the procedure or autonomous transaction. So our opinion about this is that <p
class="MsoNormal">               COMMIT/ROLLBACK inside procedure will be somewhat different from Autonomous
Transactionas incase of first, once we commit inside the procedure, <p class="MsoNormal" style="text-indent:36.0pt">it
commitseverything done before call of procedure. This is the behavior of Oracle. <p class="MsoNormal"
style="text-indent:36.0pt">Soin this case user required to be very careful to not do any operation before call of
procedure,which is not yet intended to be committed inside procedure.<p class="MsoNormal">                <p
class="MsoNormal">Sowe can prefer to implement Autonomous Transaction, which will not only be compatible with Oracle
butalso gives really strong required features.<p class="MsoNormal"> <p class="MsoNormal">I have not put the use-cases
hereas already we agree about its strong use-cases.<p class="MsoNormal"> <p class="MsoNormal">Requesting for everyone's
opinionregarding this based on which we can proceed to enhance/tune/re-write our design.                <p
class="MsoNormal"> <pclass="MsoNormal"><i><span style="color:black">Thanks and Regards,</span></i><p
class="MsoNormal"><i>KumarRajeev Rastogi<span style="color:black"> </span></i><p class="MsoNormal"> </div>