Re: Autonomous Transaction (WIP)

On 01 July 2014 12:00, Amit Kapila Wrote:

>On Tue, Jul 1, 2014 at 11:46 AM, Rajeev rastogi <rajeev(dot)rastogi(at)huawei(dot)com<mailto:rajeev(dot)rastogi(at)huawei(dot)com>> wrote:
>> On 30 June 2014 22:50, Pavel Stehule Wrote:
>> >I didn't find a related message.
>> >?
>>
>> 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.

>Simon has mentioned that exactly this idea has been rejected at
>PGCon 2 years back. Please refer that in below mail:
>http://www.postgresql.org/message-id/BF2827DCCE55594C8D7A8F7FFD3AB7713DDE136A@SZXEML508-MBX.china.huawei.com

>As far as I can see, you never came back with the different solution.

Yeah right. So for this I tried to search archived mails to get the details about the discussion but I could not find anything regarding design.
So I am not sure how shall I make my solution different from earlier as earlier solution is not accessible to me. Any help regarding this will be really great help to me.

Also from the current Autonomous transaction discussion thread (including CA+U5nMKEUm4abRQBndLYt5LEdekTAe8rbiYW3977YHMeOWQ1kA(at)mail(dot)gmail(dot)com<http://www.postgresql.org/message-id/CA+U5nMKEUm4abRQBndLYt5LEdekTAe8rbiYW3977YHMeOWQ1kA@mail.gmail.com>),
I have summarized all important feedbacks as mentioned below along with the resolution suggested:

1. Pavel Stehule (07-04-2014): -1 for Oracle syntax - it is hardly inconsistent with Postgres

Changed the syntax to “START AUTONOMOUS TRANSACTION”

2. Pavan (10-04-2014): Making autonomous transaction properties independent of main transaction.
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. But others opinion is different then we can make this property also independent.

3. Alvaro Herrarta (09-04-2014): Autonomous transaction to have their own separate proc entry.
This was concluded to not have separate proc entry for autonomous transaction and same concluded.

4. Tom Lane (09-04-2014): The point being that you need to change both pg_subtrans and pg_clog to make that state transition.
This is handled for autonomous transaction.

5. Robert Haas (09-04-2014): Not in favour of current design related to "maintaining lockmask for autonomous transaction".

I had replied for this mail regarding why this design is kept but still if design for this part is not acceptable, then I can rework to make it better. In order to do so I would be very happy to have more discussion to get concrete feedback and direction to improve this.

6. Tom Lane (09-04-2014): no justification for distinguishing normal and autonomous transactions at this level (locking level).
I had replied this also earlier. Reason for distinguishing at this level is to handle any kind of deadlock possibility between main and autonomous transaction. Deadlock handling between main and autonomous transaction was one of the requirement discussed at PGCon 2012 as part of autonomous transaction discussion. Please let me know if I am missing something in this.

All of the above mentioned changes are included in latest patch shared.
Please let me know if I have missed any other important points from the earlier discussion, I would like to address that also.
>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

From the discussion, I am able to make out two important points:

1. Main transaction and autonomous transaction should be independent and can conflict.

This is already included in our latest patch.

2. Utility commands like VACUUM and CREATE INDEX CONCURRENTLY should be able to work from autonomous transaction.

Both of the above mentioned utility commands are not supported even inside the main transaction. So it is not working within autonomous transaction.

Any opinion about this?
Please let me know if I have missed any points from the link given.

>> So I wanted to have this patch in commitfest application, so that we can have a healthy discussion and rectify all the issues.
>> But now I see that this patch has already been moved to rejected category, which will put break on further review.
>I believe ideally this patch should have been marked as
>"Returned with feedback" as you already got a feedback long
>back and never come up with solution for same.

Since this patch is very big and complex, it is better we continue discussing from the first CommitFest itself so that we can get ample time to share everyone’s opinion and then address all possible issue.

Any Opinions/Suggestions are welcome. Also let me know if I have missed something.

Thanks and Regards,
Kumar Rajeev Rastogi