Re: Autonomous Transaction (WIP) - Mailing list pgsql-hackers

<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:00, Amit Kapila Wrote:</span><p
class="MsoNormal"><spanstyle="font-size:10.0pt;font-family:"Tahoma","sans-serif""> </span><p class="MsoNormal">>On
Tue,Jul 1, 2014 at 11:46 AM, Rajeev rastogi <<a
href="mailto:rajeev.rastogi@huawei.com">rajeev.rastogi@huawei.com</a>>wrote:<span
style="font-size:10.0pt;font-family:"Tahoma","sans-serif""></span><pclass="MsoNormal">>> On 30 June 2014 22:50,
PavelStehule Wrote:<br /> >> >I didn't find a related message.<br /> >> >?<br /> >><br />
>>I think there have been some confusion, the design idea were never rejected but yes there were few feedback/
concern,which I had clarified. Also some of the other concerns are already fixed in latest patch.<br /><br /> >Simon
hasmentioned that exactly this idea has been rejected at<p class="MsoNormal">>PGCon 2 years back. Please refer that
inbelow mail:<p class="MsoNormal">><a
href="http://www.postgresql.org/message-id/BF2827DCCE55594C8D7A8F7FFD3AB7713DDE136A@SZXEML508-MBX.china.huawei.com">http://www.postgresql.org/message-id/BF2827DCCE55594C8D7A8F7FFD3AB7713DDE136A@SZXEML508-MBX.china.huawei.com</a><p
class="MsoNormal"> <pclass="MsoNormal">>As far as I can see, you never came back with the different solution.<p
class="MsoNormal"> <pclass="MsoNormal">Yeah right. So for this I tried to search archived mails to get the details
aboutthe discussion but I could not find anything regarding design.<p class="MsoNormal">So I am not sure how shall I
makemy solution different from earlier as earlier solution is not accessible to me. Any help regarding this will be
reallygreat help to me.<p class="MsoNormal"> <p class="MsoNormal">Also from the current Autonomous transaction
discussionthread (including <span style="font-size:8.5pt;font-family:"Verdana","sans-serif";color:black"><a
href="http://www.postgresql.org/message-id/CA+U5nMKEUm4abRQBndLYt5LEdekTAe8rbiYW3977YHMeOWQ1kA@mail.gmail.com">CA+U5nMKEUm4abRQBndLYt5LEdekTAe8rbiYW3977YHMeOWQ1kA@mail.gmail.com</a>),
 </span><pclass="MsoNormal">I have summarized all important feedbacks as mentioned below along with the resolution
suggested:<pclass="MsoNormal"> <p class="MsoListParagraph" style="text-indent:-18.0pt;mso-list:l0 level1 lfo1"><span
style="mso-list:Ignore">1.<spanstyle="font:7.0pt "Times New Roman"">      </span></span>Pavel Stehule (07-04-2014): -1
forOracle syntax - it is hardly inconsistent with Postgres<p class="MsoListParagraph"
style="margin-left:72.0pt">Changedthe syntax to “START AUTONOMOUS TRANSACTION”<p class="MsoListParagraph"
style="text-indent:-18.0pt;mso-list:l0level1 lfo1"><span style="mso-list:Ignore">2.<span style="font:7.0pt "Times New
Roman"">     </span></span>Pavan (10-04-2014): Making autonomous transaction properties independent of main
transaction.<pclass="MsoNormal" style="margin-left:72.0pt">Made all properties of autonomous transaction (including
read-only)independent from main transaction except isolation level, which I did not find very useful to keep different.
Butothers opinion is different then we can make this property also independent.<p class="MsoListParagraph"
style="text-indent:-18.0pt;mso-list:l0level1 lfo1"><span style="mso-list:Ignore">3.<span style="font:7.0pt "Times New
Roman"">     </span></span>Alvaro Herrarta (09-04-2014): Autonomous transaction to have their own separate proc
entry.<pclass="MsoNormal" style="margin-left:72.0pt">This was concluded to not have separate proc entry for autonomous
transactionand same concluded.<p class="MsoListParagraph" style="text-indent:-18.0pt;mso-list:l0 level1 lfo1"><span
style="mso-list:Ignore">4.<spanstyle="font:7.0pt "Times New Roman"">      </span></span>Tom Lane (09-04-2014): The
pointbeing that you need to change both pg_subtrans and pg_clog to make that state transition.<p class="MsoNormal"
style="margin-left:72.0pt">Thisis handled for autonomous transaction.<p class="MsoListParagraph"
style="text-indent:-18.0pt;mso-list:l0level1 lfo1"><span style="mso-list:Ignore">5.<span style="font:7.0pt "Times New
Roman"">     </span></span>Robert Haas (09-04-2014): Not in favour of current design related to "maintaining lockmask
forautonomous transaction".<p class="MsoListParagraph" style="margin-left:72.0pt">I had replied for this mail regarding
whythis design is kept but still if design for this part is not acceptable, then I can rework to make it better. In
orderto do so I would be very happy to have more discussion to get concrete feedback and direction to improve this.<p
class="MsoListParagraph"style="text-indent:-18.0pt;mso-list:l0 level1 lfo1"><span style="mso-list:Ignore">6.<span
style="font:7.0pt"Times New Roman"">      </span></span>Tom Lane (09-04-2014): no justification for distinguishing
normaland autonomous transactions at this level (locking level).<p class="MsoNormal" style="margin-left:72.0pt">I had
repliedthis also earlier. Reason for distinguishing at this level is to handle any kind of deadlock possibility between
mainand autonomous transaction. Deadlock handling between main and autonomous transaction was one of the requirement
discussedat PGCon 2012 as part of autonomous transaction discussion.  Please let me know if I am missing something in
this.<pclass="MsoNormal"> <p class="MsoNormal">All of the above mentioned changes are included in latest patch
shared.<pclass="MsoNormal">Please let me know if I have missed any other important points from the earlier discussion,
Iwould like to address that also.<p class="MsoNormal"><p class="MsoNormal">>Have you checked the discussion in
Developermeeting notes. Please<p class="MsoNormal">>check the same at below link:<p class="MsoNormal">><a
href="http://wiki.postgresql.org/wiki/PgCon_2012_Developer_Meeting#Autonomous_Transactions">http://wiki.postgresql.org/wiki/PgCon_2012_Developer_Meeting#Autonomous_Transactions</a><p
class="MsoNormal"> <pclass="MsoNormal">From the discussion, I am able to make out two important points:<p
class="MsoListParagraph"style="text-indent:-18.0pt;mso-list:l1 level1 lfo2"><span style="mso-list:Ignore">1.<span
style="font:7.0pt"Times New Roman"">      </span></span>Main transaction and autonomous transaction should be
independentand can conflict.<span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"></span><p
class="MsoListParagraph"style="margin-left:72.0pt">This is already included in our latest patch.<span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"></span><pclass="MsoListParagraph"
style="text-indent:-18.0pt;mso-list:l1level1 lfo2"><span style="mso-list:Ignore">2.<span style="font:7.0pt "Times New
Roman"">     </span></span>Utility commands like VACUUM and CREATE INDEX CONCURRENTLY should be able to work from
autonomoustransaction.<span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"></span><p
class="MsoListParagraph"style="margin-left:72.0pt">Both of the above mentioned utility commands are not supported even
insidethe main transaction. So it is not working within autonomous transaction.<p class="MsoListParagraph"
style="margin-left:72.0pt">Anyopinion about this?<p class="MsoNormal">Please let me know if I have missed any points
fromthe link given. <span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"></span><p
class="MsoListParagraph"><spanstyle="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> </span><p
class="MsoNormal">>>So I wanted to have this patch in commitfest application, so that we can have a healthy
discussionand rectify all the issues.<br /> >> But now I see that this patch has already been moved to rejected
category,which will put break on further review.<p class="MsoNormal">>I believe ideally this patch should have been
markedas<p class="MsoNormal">>"Returned with feedback" as you already got a feedback long<p
class="MsoNormal">>backand never come up with solution for same.<p class="MsoNormal"> <p class="MsoNormal">Since
thispatch is very big and complex, it is better we continue discussing from the first CommitFest itself so that we can
getample time to share everyone’s opinion and then address all possible issue.<p class="MsoNormal"> <p
class="MsoNormal">AnyOpinions/Suggestions are welcome. Also let me know if I have missed something.<br /><br /><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"></span><pclass="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> </span><pclass="MsoNormal"><i><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:black">Thanksand Regards,</span></i><p
class="MsoNormal"><i><spanstyle="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Kumar Rajeev
Rastogi</span></i><i><spanstyle="font-size:11.0pt;font-family:"Calibri","sans-serif";color:black"> </span></i></div>