Re: [HACKERS] Autonomous Transaction (WIP)

[Amit Kapila]

On Thu, Jul 3, 2014 at 12:03 PM, Rajeev rastogi 
wrote:
> On 01 July 2014 12:00, Amit Kapila Wrote:
> >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/bf2827dcce55594c8d7a8f7ffd3ab7713dde1...@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.

I haven't read your idea/patch in any detail, so can't comment
on whether it is good or bad.  However I think if one of the
Committers has already mentioned that the same idea has been
rejected previously, then it makes little sense to further review
or update the patch unless you know the reason of rejection and
handle it in an acceptable way.

Now as far as I can understand, the problem seems to be in a way
you have defined Autonomous Transaction Storage which can lead
to consumption of additional client slots, this is just what I could make
sense from above mail but I am not completely sure on this matter.


With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Re: [HACKERS] Autonomous Transaction (WIP)

[Rajeev rastogi]

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

>On Tue, Jul 1, 2014 at 11:46 AM, Rajeev rastogi 
>mailto:rajeev.rast...@huawei.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/bf2827dcce55594c8d7a8f7ffd3ab7713dde1...@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+u5nmkeum4abrqbndlyt5ledektae8rbiyw3977yhmeowq...@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

Re: [HACKERS] Autonomous Transaction (WIP)

[Pavel Stehule]

2014-07-01 10:38 GMT+02:00 Rajeev rastogi :

>  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.
>

ok

>  >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;
>
I don't like this design (really) - it can be used in later implementation
of procedures - but I don't like a explicit transaction manipulation in
functions. It is Oracleism (and this part I don't want to follow, because
Oracle design is not lucky)  - and it is one reason, why Oracle SP are
significantly complex than PostgreSQL SP. After all I am thinking so
PostgreSQL relation between transactions and procedures are better, simply
for usage, simply for learning.  But it is little bit different topic.

Regards

Pavel





>  Please let me know if I have missed to answer any of your queries.
>
> Thanks and Regards,
>
> Kumar Rajeev Rastogi
>
>
>
>
>

Re: [HACKERS] Autonomous Transaction (WIP)

[Rajeev rastogi]

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

Re: [HACKERS] Autonomous Transaction (WIP)

[Pavel Stehule]

2014-07-01 8:29 GMT+02:00 Amit Kapila :

> On Tue, Jul 1, 2014 at 11:46 AM, Rajeev rastogi 
> 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/bf2827dcce55594c8d7a8f7ffd3ab7713dde1...@szxeml508-mbx.china.huawei.com
>
> As far as I can see, you never came back with the different solution.
>
> 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.

* When autonomous transaction is independent on parent transaction, then
locks parent and autonomous transaction should be in conflict

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.

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.

Regards

Pavel


>
> > 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.
>
> With Regards,
> Amit Kapila.
> EnterpriseDB: http://www.enterprisedb.com
>

Re: [HACKERS] Autonomous Transaction (WIP)

[Amit Kapila]

On Tue, Jul 1, 2014 at 11:46 AM, Rajeev rastogi 
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/bf2827dcce55594c8d7a8f7ffd3ab7713dde1...@szxeml508-mbx.china.huawei.com

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

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


> 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.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Re: [HACKERS] Autonomous Transaction (WIP)

[Pavel Stehule]

2014-07-01 8:16 GMT+02:00 Rajeev rastogi :

>  On 30 June 2014 22:50, Pavel Stehule Wrote:
>
> 2014-06-30 12:38 GMT+02:00 Abhijit Menon-Sen :
>
> >>If I understand correctly, the design of this patch has already been
> >>considered earlier and rejected. So I guess the patch should also be
> >>marked rejected?
>
>
>
> >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.
>
> 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.
>
> So is there any way to bring back and continue reviewing this patch.
>
> Please let me know if any issue or I am missing something.
>

I didn't watch a discuss about internal implementation, but now, when I am
testing this feature - it works well.

Surely - this feature has important but with relatively large impact and
should be extremely well tested. Now there are no any special test.
Probably we can reuse a tests for nested transactions.

I prefer this feature will be part of first commitfest due high complexity.

Regards

Pavel


>
>
> *Thanks and Regards,*
>
> *Kumar Rajeev Rastogi*
>

Re: [HACKERS] Autonomous Transaction (WIP)

[Rajeev rastogi]

On 30 June 2014 22:50, Pavel Stehule Wrote:
2014-06-30 12:38 GMT+02:00 Abhijit Menon-Sen 
mailto:a...@2ndquadrant.com>>:
>>If I understand correctly, the design of this patch has already been
>>considered earlier and rejected. So I guess the patch should also be
>>marked rejected?

>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.
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.
So is there any way to bring back and continue reviewing this patch.
Please let me know if any issue or I am missing something.

Thanks and Regards,
Kumar Rajeev Rastogi

Re: [HACKERS] Autonomous Transaction (WIP)

[Pavel Stehule]

2014-06-30 12:38 GMT+02:00 Abhijit Menon-Sen :

> If I understand correctly, the design of this patch has already been
> considered earlier and rejected. So I guess the patch should also be
> marked rejected?
>

I didn't find a related message.

?

Regards

Pavel


>
> -- Abhijit
>

Re: [HACKERS] Autonomous Transaction (WIP)

[Abhijit Menon-Sen]

If I understand correctly, the design of this patch has already been
considered earlier and rejected. So I guess the patch should also be
marked rejected?

-- Abhijit


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Autonomous Transaction (WIP)

[Pavel Stehule]

Hello

There are lot of unnecessary block over one statement in code

+   if ((inAutoX) && (chunk == events->head) && ((char *)event
< afterTriggers->events_stack[my_level].tailfree))
+   {
+   continue;
+   }
+


and there a few too long lines

Regards

Pavel


2014-06-24 18:40 GMT+02:00 Pavel Stehule :

> postgres=# select version();
>
> version
>
> -
>  PostgreSQL 9.5devel on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
> 4.8.2 20131212 (Red Hat 4.8.2-7), 64-bit
> (1 row)
>
>
>
> 2014-06-24 18:39 GMT+02:00 Pavel Stehule :
>
> Hello
>>
>> regress tests fails:
>>
>>  plancache... ok
>>  limit... ok
>>  plpgsql  ... ok
>>  copy2... ok
>>  temp ... FAILED
>>  domain   ... ok
>>  rangefuncs   ... ok
>>  prepare  ... ok
>>  without_oid  ... ok
>>  conversion   ... ok
>>  truncate ... ok
>>  alter_table  ... ok
>>  sequence ... ok
>>
>> I did some small tests and it works well. When I looked to code, I was
>> surprised by hardcoded max nesting level of autonomous transactions
>>
>> #define MAX_AUTOX_NESTING_LEVEL   3
>>
>> why? Is not it too restrictive?
>>
>> I am missing a regress tests.
>>
>> Regards
>>
>> Pavel
>>
>>
>>
>>
>>
>>
>> 2014-06-18 11:19 GMT+02:00 Rajeev rastogi :
>>
>>> On 17 June 2014 02:01, Alvaro Herrera Wrote:
>>>
>>> > What's the status of this patch?
>>>
>>> I have completed work on this and some more changes are done on top of
>>> earlier patch shared:
>>> 1. Fixed all of the issues observed.
>>> 2. Addressed some of the feedback from community like
>>> a. Change the syntax to
>>> START AUTONOMOUS TRANSACTION [READ ONLY | READ
>>> WRITE]
>>> b. As Pavan had pointed, I have made transaction behavior (only
>>> read-only properties) of main and autonomous transaction independent.
>>> 3. Added documentation for this feature.
>>> 4. Rebased to latest git code.
>>>
>>> Please find the attached latest patch and provide opinion.
>>>
>>> Thanks and Regards,
>>> Kumar Rajeev Rastogi
>>>
>>>
>>>
>>> --
>>> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-hackers
>>>
>>>
>>
>

Re: [HACKERS] Autonomous Transaction (WIP)

[Pavel Stehule]

postgres=# select version();

version
-
 PostgreSQL 9.5devel on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.8.2 20131212 (Red Hat 4.8.2-7), 64-bit
(1 row)



2014-06-24 18:39 GMT+02:00 Pavel Stehule :

> Hello
>
> regress tests fails:
>
>  plancache... ok
>  limit... ok
>  plpgsql  ... ok
>  copy2... ok
>  temp ... FAILED
>  domain   ... ok
>  rangefuncs   ... ok
>  prepare  ... ok
>  without_oid  ... ok
>  conversion   ... ok
>  truncate ... ok
>  alter_table  ... ok
>  sequence ... ok
>
> I did some small tests and it works well. When I looked to code, I was
> surprised by hardcoded max nesting level of autonomous transactions
>
> #define MAX_AUTOX_NESTING_LEVEL   3
>
> why? Is not it too restrictive?
>
> I am missing a regress tests.
>
> Regards
>
> Pavel
>
>
>
>
>
>
> 2014-06-18 11:19 GMT+02:00 Rajeev rastogi :
>
>> On 17 June 2014 02:01, Alvaro Herrera Wrote:
>>
>> > What's the status of this patch?
>>
>> I have completed work on this and some more changes are done on top of
>> earlier patch shared:
>> 1. Fixed all of the issues observed.
>> 2. Addressed some of the feedback from community like
>> a. Change the syntax to
>> START AUTONOMOUS TRANSACTION [READ ONLY | READ
>> WRITE]
>> b. As Pavan had pointed, I have made transaction behavior (only
>> read-only properties) of main and autonomous transaction independent.
>> 3. Added documentation for this feature.
>> 4. Rebased to latest git code.
>>
>> Please find the attached latest patch and provide opinion.
>>
>> Thanks and Regards,
>> Kumar Rajeev Rastogi
>>
>>
>>
>> --
>> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
>>
>>
>


temp.out
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Autonomous Transaction (WIP)

[Pavel Stehule]

Hello

regress tests fails:

 plancache... ok
 limit... ok
 plpgsql  ... ok
 copy2... ok
 temp ... FAILED
 domain   ... ok
 rangefuncs   ... ok
 prepare  ... ok
 without_oid  ... ok
 conversion   ... ok
 truncate ... ok
 alter_table  ... ok
 sequence ... ok

I did some small tests and it works well. When I looked to code, I was
surprised by hardcoded max nesting level of autonomous transactions

#define MAX_AUTOX_NESTING_LEVEL   3

why? Is not it too restrictive?

I am missing a regress tests.

Regards

Pavel






2014-06-18 11:19 GMT+02:00 Rajeev rastogi :

> On 17 June 2014 02:01, Alvaro Herrera Wrote:
>
> > What's the status of this patch?
>
> I have completed work on this and some more changes are done on top of
> earlier patch shared:
> 1. Fixed all of the issues observed.
> 2. Addressed some of the feedback from community like
> a. Change the syntax to
> START AUTONOMOUS TRANSACTION [READ ONLY | READ
> WRITE]
> b. As Pavan had pointed, I have made transaction behavior (only
> read-only properties) of main and autonomous transaction independent.
> 3. Added documentation for this feature.
> 4. Rebased to latest git code.
>
> Please find the attached latest patch and provide opinion.
>
> Thanks and Regards,
> Kumar Rajeev Rastogi
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
>


regression.diffs
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Autonomous Transaction (WIP)

[Alvaro Herrera]

What's the status of this patch?

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Autonomous Transaction (WIP)

[Rajeev rastogi]

On 14 April 2014 20:10, Simon Riggs wrote:

>>Autonomous Transaction Storage:
>>As for main transaction, structure PGXACT is used to store main transactions, 
>>which are created in shared memory of size:
>>   (Number of process)*sizeof(struct PGXACT)
>>Similarly a new structure will be defined to store autonomous transaction:
>>   Struct PGAutonomousXACT

Oh...I had already added this patch for 2014-June CommitFest, thinking that 
everyone is busy with  work to wrap up 9.4.

> I already proposed exactly this design two years ago and it was rejected at 
> the PgCon hackers meeting.
> I have a better design worked out now and will likely be working on it for 9.5

Can we work together to take this feature to final goal.
May be you can go through my complete patch and see whatever part of the patch 
and related design can be re-used along with your new design.
Also if possible you can share your design (even rough is OK), I will see if I 
can contribute to that in some-way.

Thanks and Regards,
Kumar Rajeev Rastogi

Re: [HACKERS] Autonomous Transaction (WIP)

[Simon Riggs]

On 7 April 2014 05:06, Rajeev rastogi  wrote:


> *Autonomous Transaction Storage:*
>
> As for main transaction, structure PGXACT is used to store main
> transactions, which are created in shared memory of size:
>
> (Number of process)*sizeof(struct PGXACT)
>
> Similarly a new structure will be defined to store autonomous transaction:
>
> *Struct PGAutonomousXACT*
>

I already proposed exactly this design two years ago and it was rejected at
the PgCon hackers meeting.

I have a better design worked out now and will likely be working on it for
9.5

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] Autonomous Transaction (WIP)

[Rajeev rastogi]

On 09 April 2014 21:25, Robert Haas Wrote:

> >> > Deadlock Detection:
> >> I'm not sure how this would work out internally
> > In order to resolve deadlock, two member variable will be created in
> the structure PROLOCK:
> > Bitmask for lock types currently held by autonomous
> transaction.
> > LOCKMASKholdMaskByAutoTx[MAX_AUTO_TX_LEVEL]
> > Bitmask for lock types currently held by main transaction.
> > LOCKMASKholdMaskByNormalTx
> >
> > Now when we grant the lock to particular transaction, depending on
> > type of transaction, bit Mask will be set for either holdMaskByAutoTx
> or holdMaskByNormalTx.
> > Similar when lock is ungranted, corresponding bitmask will be reset.
> 
> That sounds pretty ugly, not to mention the fact that it will cause a
> substantial increase in the amount of memory required to store
> PROCLOCKs.  It will probably slow things down, too.

Actually I followed above design to keep it align with the existing design. As 
I understand, currently also
all lock conflict is checked based on the corresponding lock bit mask. 

This is good catch that shared memory required will increase but isn't it 
justified from user perspective
since we are allowing more transactions per session and hence memory required 
to store various kind of resources 
will increase.

Since we are just additionally setting the bitmask for each lock (in-case there 
is autonomous transaction, then there will
be one more additional bit mask setting and deadlock check), I don't think it 
should slow down the overall operation. 

Also We can keep number of autonomous transaction configurable(default-0), to 
keep it less impacting incase it is not configured.

An autonomous transaction can also conflict with main transaction, so in order 
to check conflict between them, 
I am distinguishing at this level.

Please correct me If I am wrong anywhere and also please provide your thought 
on this and on overall design.

Thanks and Regards,
Kumar Rajeev Rastogi


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Autonomous Transaction (WIP)

[Rajeev rastogi]

On 10 April 2014 11:18, Pavan Deolasee Wrote:
>>I could think of few  global variables like transaction properties 
>>related(i.e. read-only mode, isolation level etc). As I plan to keep 
>>transaction properties of autonomous transaction same as main transaction, so 
>>there is no need to have these global variables separately.
>>Apart from this there are global variables like with-in transaction counters, 
>>GUC, xactStartTimeStamp. I think there is no need to maintain these variables 
>>also separately. They can continue from previous value for autonomous 
>>transaction also similar to as sub->>transaction does.

>Hmm. Is that in line with what other databases do ? I would have preferred AT 
>to run like a standalone transaction without any influence of the starting 
>transaction, managing its own resources/locks/visibility/triggers etc.

To me it seems it is not very useful to keep the transaction properties 
separate except the read-only properties (though oracle does not share any 
transaction properties).

So we can have restriction that isolation and deferrable properties of main 
transaction will be inherited by autonomous transaction but read-only 
properties can be defined independently by autonomous transaction. Which looks 
to be fair restriction according to me.

In order to keep read-only properties separate, there is already infrastructure 
in PG. Inside the structure TransactionStateData, there is variable 
prevXactReadOnly (entry-time xact r/o state), which can keep the parent 
transaction read only properties and XactReadOnly can be changed to current 
transaction properties.
Moreover we can take this (transaction 
properties) as a feature enhancement also once a basic infrastructure is 
established, if acceptable to everyone.

Autonomous transaction will not share resource/lock/visibility etc with main 
transaction. This has been already taken care in WIP patch.
>>In-case of autonomous transaction, only specific global variables initialized 
>>are related to resources (similar to sub-transaction), which anyway  gets 
>>stored in current transaction state.
>>Please let me know if I am missing something or if you have some specific 
>>global variables related issue.
>No, I don't have any specific issues in mind. Mostly all such global state is 
>managed through various AtStart/AtEOX and related routines. So a careful 
>examination of all those routines will give a good idea what needs to be 
>handled. You probably will require to write
>AtATStart/AtATEOX and similar routines to manage the state at AT 
>start/commit/rollback. Sorry, I haven't looked at your WIP patch yet.

For some of the resources, I have already written AtATStart/AtATEOX kind of 
routines in WIP patch.

Comments/feedbacks/doubts are welcome.

Thanks and Regards,
Kumar Rajeev Rastogi

Re: [HACKERS] Autonomous Transaction (WIP)

[Pavan Deolasee]

On Thu, Apr 10, 2014 at 10:44 AM, Rajeev rastogi
wrote:

>  On 09 April 2014 12:14, Pavan Deolasee Wrote:
>
>  >Whenever I was asked to have a look at implementing this feature, I
> always wondered about the great amount of global state that a backend
> maintains which is normally tied to a single top transaction. Since AT will
> have same characteristics as a top level transaction, I
>
> >wonder how do you plan to separate those global state variables ? Sure,
> we can group them in a structure and put them on a stack when an AT starts
> and pop them off when the original top transaction becomes active again,
> finding all such global state variables is
>
> >going to be tricky.
>
>
>
> I could think of few  global variables like transaction properties
> related(i.e. read-only mode, isolation level etc). As I plan to keep
> transaction properties of autonomous transaction same as main transaction,
> so there is no need to have these global variables separately.
>
> Apart from this there are global variables like with-in transaction
> counters, GUC, xactStartTimeStamp. I think there is no need to maintain
> these variables also separately. They can continue from previous value for
> autonomous transaction also similar to as sub-transaction does.
>
>
>

Hmm. Is that in line with what other databases do ? I would have preferred
AT to run like a standalone transaction without any influence of the
starting transaction, managing its own resources/locks/visibility/triggers
etc.


>  In-case of autonomous transaction, only specific global variables
> initialized are related to resources (similar to sub-transaction), which
> anyway  gets stored in current transaction state.
>
>
>
> Please let me know if I am missing something or if you have some specific
> global variables related issue.
>
>
>

No, I don't have any specific issues in mind. Mostly all such global state
is managed through various AtStart/AtEOX and related routines. So a careful
examination of all those routines will give a good idea what needs to be
handled. You probably will require to write AtATStart/AtATEOX and similar
routines to manage the state at AT start/commit/rollback. Sorry, I haven't
looked at your WIP patch yet.

Thanks,
Pavan

-- 
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee

Re: [HACKERS] Autonomous Transaction (WIP)

[Rajeev rastogi]

On 09 April 2014 12:14, Pavan Deolasee Wrote:

>Whenever I was asked to have a look at implementing this feature, I always 
>wondered about the great amount of global state that a backend maintains which 
>is normally tied to a single top transaction. Since AT will have same 
>characteristics as a top level transaction, I
>wonder how do you plan to separate those global state variables ? Sure, we can 
>group them in a structure and put them on a stack when an AT starts and pop 
>them off when the original top transaction becomes active again, finding all 
>such global state variables is
>going to be tricky.

I could think of few  global variables like transaction properties related(i.e. 
read-only mode, isolation level etc). As I plan to keep transaction properties 
of autonomous transaction same as main transaction, so there is no need to have 
these global variables separately.
Apart from this there are global variables like with-in transaction counters, 
GUC, xactStartTimeStamp. I think there is no need to maintain these variables 
also separately. They can continue from previous value for autonomous 
transaction also similar to as sub-transaction does.

In-case of autonomous transaction, only specific global variables initialized 
are related to resources (similar to sub-transaction), which anyway  gets 
stored in current transaction state.

Please let me know if I am missing something or if you have some specific 
global variables related issue.

Thanks and Regards,
Kumar Rajeev Rastogi

Re: [HACKERS] Autonomous Transaction (WIP)

[Tom Lane]

Robert Haas  writes:
> On Wed, Apr 9, 2014 at 12:24 AM, Rajeev rastogi
>  wrote:
>> Now when we grant the lock to particular transaction, depending on type of 
>> transaction, bit
>> Mask will be set for either holdMaskByAutoTx or holdMaskByNormalTx.
>> Similar when lock is ungranted, corresponding bitmask will be reset.

> That sounds pretty ugly, not to mention the fact that it will cause a
> substantial increase in the amount of memory required to store
> PROCLOCKs.  It will probably slow things down, too.

More to the point, why isn't it a flat-out bad idea?  I can see no
justification for distinguishing normal and autonomous transactions
at this level.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Autonomous Transaction (WIP)

[Robert Haas]

On Wed, Apr 9, 2014 at 12:24 AM, Rajeev rastogi
 wrote:
>> > Deadlock Detection:
>> I'm not sure how this would work out internally
> In order to resolve deadlock, two member variable will be created in the 
> structure PROLOCK:
> Bitmask for lock types currently held by autonomous transaction.
> LOCKMASKholdMaskByAutoTx[MAX_AUTO_TX_LEVEL]
> Bitmask for lock types currently held by main transaction.
> LOCKMASKholdMaskByNormalTx
>
> Now when we grant the lock to particular transaction, depending on type of 
> transaction, bit
> Mask will be set for either holdMaskByAutoTx or holdMaskByNormalTx.
> Similar when lock is ungranted, corresponding bitmask will be reset.

That sounds pretty ugly, not to mention the fact that it will cause a
substantial increase in the amount of memory required to store
PROCLOCKs.  It will probably slow things down, too.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Autonomous Transaction (WIP)

[Heikki Linnakangas]

On 04/09/2014 09:55 AM, Hannu Krosing wrote:

This would be greatly simplified if we can accept the restriction that
there is only single
snapshot per backend (not per transaction). To me this seems a
completely sensible restriction.


Huh? In Read committed mode, every query within a transaction gets a 
different snapshot.


- Heikki


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Autonomous Transaction (WIP)

[Craig Ringer]

On 04/09/2014 02:44 PM, Pavan Deolasee wrote:
> On Wed, Apr 9, 2014 at 11:03 AM, Rajeev rastogi
> mailto:rajeev.rast...@huawei.com>> wrote:
> 
> 
> Though autonomous transaction uses mixed approach of sub-transaction
> as well as main
> transaction, transaction state of autonomous transaction is handled
> independently.
> 
> 
> Whenever I was asked to have a look at implementing this feature, I
> always wondered about the great amount of global state that a backend
> maintains which is normally tied to a single top transaction. Since AT
> will have same characteristics as a top level transaction, I wonder how
> do you plan to separate those global state variables ? Sure, we can
> group them in a structure and put them on a stack when an AT starts and
> pop them off when the original top transaction becomes active again,
> finding all such global state variables is going to be tricky.

... not to mention the fact that extensions may rely on having their own
global state.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Autonomous Transaction (WIP)

[Hannu Krosing]

On 04/09/2014 08:44 AM, Pavan Deolasee wrote:
> On Wed, Apr 9, 2014 at 11:03 AM, Rajeev rastogi
> mailto:rajeev.rast...@huawei.com>> wrote:
>
>
> Though autonomous transaction uses mixed approach of
> sub-transaction as well as main
> transaction, transaction state of autonomous transaction is
> handled independently.
>
>
> Whenever I was asked to have a look at implementing this feature, I
> always wondered about the great amount of global state that a backend
> maintains which is normally tied to a single top transaction. Since AT
> will have same characteristics as a top level transaction, I wonder
> how do you plan to separate those global state variables ? Sure, we
> can group them in a structure and put them on a stack when an AT
> starts and pop them off when the original top transaction becomes
> active again, finding all such global state variables is going to be
> tricky.
I would hope most of this to be solved by having one (read only) virtual
transaction and
then juggling the ATs in a way similar to current subtransaction machinery.

The main differences would be that:

 A) the top level transaction stays virtual

and

 B) ATs are committed independantly

This would be greatly simplified if we can accept the restriction that
there is only single
snapshot per backend (not per transaction). To me this seems a
completely sensible restriction.

Re syntax, I think we need a way to name the transactions so we can have
a way
to switch between multiple parallel active autonomous transactions.

-
BEGIN TRANSACTION myfirsttransaction;

do something in myfirsttransaction;

BEGIN TRANSACTION anothertransaction;

do something in anothertransaction;

SET TRANSACTION myfirsttransaction;

more work in myfirsttransaction;

ROLLBACK anothertransaction;

COMMIT; -- or COMMIT myfirsttransaction;


Cheers
Hannu


>
> Thanks,
> Pavan
>
> -- 
> Pavan Deolasee
> http://www.linkedin.com/in/pavandeolasee

Re: [HACKERS] Autonomous Transaction (WIP)

[Pavan Deolasee]

On Wed, Apr 9, 2014 at 11:03 AM, Rajeev rastogi
wrote:

>
> Though autonomous transaction uses mixed approach of sub-transaction as
> well as main
> transaction, transaction state of autonomous transaction is handled
> independently.
>
>
Whenever I was asked to have a look at implementing this feature, I always
wondered about the great amount of global state that a backend maintains
which is normally tied to a single top transaction. Since AT will have same
characteristics as a top level transaction, I wonder how do you plan to
separate those global state variables ? Sure, we can group them in a
structure and put them on a stack when an AT starts and pop them off when
the original top transaction becomes active again, finding all such global
state variables is going to be tricky.

Thanks,
Pavan

-- 
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee

Re: [HACKERS] Autonomous Transaction (WIP)

[Rajeev rastogi]

On 09 April 2014 01:43, Tom Lane Wrote:

> > I'm also pretty unconvinced that multiple PGPROCs is the right way to
> > go.  First, PGPROCs have a bunch of state in them that is assumed to
> > exist once per backend.  We might find pretty substantial code churn
> > there if we try to go change that.  Second, why do other backends
> > really need to know about our ATs?  As far as I can see, if other
> > backends see the AT as a subtransaction of our top-level transaction
> > up until it actually commits, that ought to be just fine.
> 
> If we can make it work like that, sure.  I'm a bit worried about how
> you'd decouple a subtransaction and commit it atomically ... or if
> that's not atomic, will it create any problems?  

Though autonomous transaction uses mixed approach of sub-transaction as well as 
main
transaction, transaction state of autonomous transaction is handled 
independently.
So depending on the transaction state of autonomous transaction (for commit 
TBLOCK_AUTOCOMMIT), 
this transaction will be committed. While committing:
1.  Commit of record and logging the corresponding WAL happens in the same 
way as main transaction (except the way autonomous transaction and their 
sub-transaction accessed).
This will take care automatically of updating pg_clog also for 
autonomous transaction.
2.  Also it marks the autonomous transaction finish by setting appropriate 
fields of MyPgAutonomousXact in similar manner as done for main transaction.
3.  Freeing of all resource and popping out of parent transaction happens 
in the same way as sub-transaction.

> The point being that
> you need to change both pg_subtrans and pg_clog to make that state
> transition.

Yes I am changing both. But no specific changes were required. During commit 
and assignment of autonomous transaction, it is automatically taken care. 

Any comment/feedback/doubt are welcome?

Thanks and Regards,
Kumar Rajeev Rastogi








-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Autonomous Transaction (WIP)

[Rajeev rastogi]

On 09 April 2014 01:09, Rover Haas Wrote:
 
> I'm also pretty unconvinced that multiple PGPROCs is the right way to
> go.  First, PGPROCs have a bunch of state in them that is assumed to
> exist once per backend.  We might find pretty substantial code churn
> there if we try to go change that.  

Yes you right. That is why I am not creating a separate procarray entry to 
maintain autonomous transaction. Please find details in previous reply sent 
today sometime back.

> Second, why do other backends 
> really need to know about our ATs?  As far as I can see, if other
> backends see the AT as a subtransaction of our top-level transaction up
> until it actually commits, that ought to be just fine.  Maybe the
> backend needs to internally frob visibility rules, but that's not a
> matter for shared memory.

In order to get snapshot from other session, it will be required by other 
session to access autonomous transaction and their sub-transactions.

During snapshot creation, autonomous transaction is considered as main
transaction and list of all running autonomous transaction and their 
sub-transaction
gets stored in snapshot data.

e.g. Suppose below processes are running with given transactions:

Proc-1: 100
Proc-2: 101, 102 (Auto Tx1), 103 (Auto Tx2), 104 (Sub-tx of Auto Tx2)
Proc-3: 105, 106 (Auto Tx2), 107 (Auto Tx2)

Suppose latest completed transaction is 108.

Then Snapshot data for autonomous transaction 107 will be as below:
Xmin: 100
Xmax: 109
Snapshot->xip[]:  100, 101, 102, 103, 105, 106  

Snapshot->subxip[]:   104

Thanks and Regards,
Kumar Rajeev Rastogi




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Autonomous Transaction (WIP)

[Andres Freund]

On 2014-04-08 16:13:21 -0400, Tom Lane wrote:
> Robert Haas  writes:
> > I'm also pretty unconvinced that multiple PGPROCs is the right way to
> > go.  First, PGPROCs have a bunch of state in them that is assumed to
> > exist once per backend.  We might find pretty substantial code churn
> > there if we try to go change that.  Second, why do other backends
> > really need to know about our ATs?  As far as I can see, if other
> > backends see the AT as a subtransaction of our top-level transaction
> > up until it actually commits, that ought to be just fine.
> 
> If we can make it work like that, sure.  I'm a bit worried about how you'd
> decouple a subtransaction and commit it atomically ... or if that's not
> atomic, will it create any problems?  The point being that you need to
> change both pg_subtrans and pg_clog to make that state transition.

I think it can be made work sensibly - while those states are changed it
will still appear to be running via the procarray. There's some fun
around suboverflowed entries, but I think that can be handled by
reserving an entry for autonomous transactions.

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Autonomous Transaction (WIP)

[Tom Lane]

Robert Haas  writes:
> I'm also pretty unconvinced that multiple PGPROCs is the right way to
> go.  First, PGPROCs have a bunch of state in them that is assumed to
> exist once per backend.  We might find pretty substantial code churn
> there if we try to go change that.  Second, why do other backends
> really need to know about our ATs?  As far as I can see, if other
> backends see the AT as a subtransaction of our top-level transaction
> up until it actually commits, that ought to be just fine.

If we can make it work like that, sure.  I'm a bit worried about how you'd
decouple a subtransaction and commit it atomically ... or if that's not
atomic, will it create any problems?  The point being that you need to
change both pg_subtrans and pg_clog to make that state transition.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Autonomous Transaction (WIP)

[Andres Freund]

On 2014-04-08 15:39:18 -0400, Robert Haas wrote:
> I'm also pretty unconvinced that multiple PGPROCs is the right way to
> go.  First, PGPROCs have a bunch of state in them that is assumed to
> exist once per backend.  We might find pretty substantial code churn
> there if we try to go change that.  Second, why do other backends
> really need to know about our ATs?  As far as I can see, if other
> backends see the AT as a subtransaction of our top-level transaction
> up until it actually commits, that ought to be just fine.  Maybe the
> backend needs to internally frob visibility rules, but that's not a
> matter for shared memory.

Agreed. That's also how I imagined things to work.

I think except the visibility semantics, there's really not that much to
do if we were to reuse the subtransaction framework. There's some
complications with Hot Standby, but I think those can be solved.

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Autonomous Transaction (WIP)

[Robert Haas]

On Tue, Apr 8, 2014 at 2:43 PM, Alvaro Herrera  wrote:
> Greg Stark wrote:
>> If the autonomous transaction is actually a separate procarray entry
>> (which I suspect it would have to be, much like prepared transactions
>> and the dblink connections which are commonly used to kludge
>> autonomous transactions) then this should be fairly painless. If you
>> implement some kind of saving and restoring procarray data then it
>> probably wouldn't work out.
>
> I don't have time to digest this proposal ATM, but in previous occasion
> when we have discussed autonomous transactions (ATs), we have always
> considered natural that they have their own procarray entries; there are
> too many strange issues otherwise.
>
> Since the number of procarray entries is fixed at startup time, one
> natural consequence of this is that the number of ATs in flight at any
> moment is also fixed.  Normally we consider allocating a single AT per
> session to be sufficient.  So you can't have one AT start another AT,
> for instance -- that seems a reasonable restriction.

It depends.  A lot of Oracle users are used to having autonomous
transactions be very cheap, so you can just mark random procedures as
running in an autonomous transaction and forget about it.  If the call
stack is several levels deep, then you could easily have one such
procedure call another such procedure.  Of course, you may feel that's
bad practice or that we shouldn't emulate what $COMPETITOR does, and I
agree we don't have to necessarily do it that way just because they do
it that way, but I'm not sure it's accurate to say that nobody will
care.

I'm also pretty unconvinced that multiple PGPROCs is the right way to
go.  First, PGPROCs have a bunch of state in them that is assumed to
exist once per backend.  We might find pretty substantial code churn
there if we try to go change that.  Second, why do other backends
really need to know about our ATs?  As far as I can see, if other
backends see the AT as a subtransaction of our top-level transaction
up until it actually commits, that ought to be just fine.  Maybe the
backend needs to internally frob visibility rules, but that's not a
matter for shared memory.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Autonomous Transaction (WIP)

[Alvaro Herrera]

Greg Stark wrote:

> If the autonomous transaction is actually a separate procarray entry
> (which I suspect it would have to be, much like prepared transactions
> and the dblink connections which are commonly used to kludge
> autonomous transactions) then this should be fairly painless. If you
> implement some kind of saving and restoring procarray data then it
> probably wouldn't work out.

I don't have time to digest this proposal ATM, but in previous occasion
when we have discussed autonomous transactions (ATs), we have always
considered natural that they have their own procarray entries; there are
too many strange issues otherwise.

Since the number of procarray entries is fixed at startup time, one
natural consequence of this is that the number of ATs in flight at any
moment is also fixed.  Normally we consider allocating a single AT per
session to be sufficient.  So you can't have one AT start another AT,
for instance -- that seems a reasonable restriction.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Autonomous Transaction (WIP)

[Greg Stark]

On Mon, Apr 7, 2014 at 12:06 AM, Rajeev rastogi
 wrote:
>
> Deadlock Detection:
>
> It is possible that the main or upper autonomous transaction has taken a lock 
> on some resource, which might be required by lower autonomous transaction. If 
> it happens so then deadlock will occur. So in order to solve this issue, each 
> main and autonomous transaction will hold list of all locks acquired in 
> PROLOCK based on which deadlock will be resolved.


I'm not sure how this would work out internally -- it would depend on
how you plan to allocate the new transaction in the internal data
structures -- but the natural way to prevent/detect deadlocks would be
to have the parent transaction immediately take a lock on the
autonomous transaction as soon as it's started. That would cause any
lock in the autonomous transaction which caused it to wait on the
parent transaction to be detected as a deadlock. It would also cause
any monitoring tool to correctly show the parent transaction as
waiting on the autonomous transaction to finish.

If the autonomous transaction is actually a separate procarray entry
(which I suspect it would have to be, much like prepared transactions
and the dblink connections which are commonly used to kludge
autonomous transactions) then this should be fairly painless. If you
implement some kind of saving and restoring procarray data then it
probably wouldn't work out.


-- 
greg


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Autonomous Transaction (WIP)

[Pavel Stehule]

2014-04-07 12:16 GMT+02:00 Atri Sharma :

>
>
>
> On Mon, Apr 7, 2014 at 3:41 PM, Pavel Stehule wrote:
>
>>
>>
>>
>> 2014-04-07 11:59 GMT+02:00 Rajeev rastogi :
>>
>>   On 07 April 2014 12:12, Pavel Stehule wrote:
>>>
>>>  >+1 for feature
>>>
>>> Thanks
>>>
>>>
>>>
>>> >-1 for Oracle syntax - it is hardly inconsistent with Postgres
>>>
>>> We can discuss and come out with the syntax based on everyone agreement.
>>>
>>> >Autonomous transactions should be used everywhere - not only in plpgsql
>>>
>>>
>>>
>>> Yes you are right. I am not planning to support only using plpgsql.
>>> Initially we can support this
>>>
>>> Using the standalone SQL-commands and then later we can enhance based on
>>> this infrastructure
>>>
>>> to be used using plpgsql, triggers.
>>>
>>
>> ok
>>
>> long time I though about this feature.
>>
>> I am thinking so this should be fully isolated transaction - it should
>> not be subtransaction, because then you can break database consistency - RI
>>
>>
>>
> I am missing something here, but how does making it a subtransaction break
> consistency? Isnt that what should actually be happening so that the
> autonomous transaction's changes are actually visible till the parent
> transaction commits?
>

commit of autonomous transaction doesn't depends on outer transaction. So
anything what you can do, should be independent on outer transaction.

Pavel



>
> What am I missing here?
>
> Regards,
>
> Atri
>

Re: [HACKERS] Autonomous Transaction (WIP)

[Andres Freund]

On 2014-04-07 15:46:42 +0530, Atri Sharma wrote:
> On Mon, Apr 7, 2014 at 3:41 PM, Pavel Stehule wrote:
> I am missing something here, but how does making it a subtransaction break
> consistency? Isnt that what should actually be happening so that the
> autonomous transaction's changes are actually visible till the parent
> transaction commits?
> 
> What am I missing here?

START TRANSACTION;
INSERT INTO referenced_to_table ... id = 1;
START AUTONOMOUS SUBTRANSACTION;
INSERT INTO referencing_table id = 1 ...;
COMMIT AUTONOMOUS SUBTRANSACTION;
ROLLBACK;

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Autonomous Transaction (WIP)

[Atri Sharma]

On Mon, Apr 7, 2014 at 3:41 PM, Pavel Stehule wrote:

>
>
>
> 2014-04-07 11:59 GMT+02:00 Rajeev rastogi :
>
>   On 07 April 2014 12:12, Pavel Stehule wrote:
>>
>>  >+1 for feature
>>
>> Thanks
>>
>>
>>
>> >-1 for Oracle syntax - it is hardly inconsistent with Postgres
>>
>> We can discuss and come out with the syntax based on everyone agreement.
>>
>> >Autonomous transactions should be used everywhere - not only in plpgsql
>>
>>
>>
>> Yes you are right. I am not planning to support only using plpgsql.
>> Initially we can support this
>>
>> Using the standalone SQL-commands and then later we can enhance based on
>> this infrastructure
>>
>> to be used using plpgsql, triggers.
>>
>
> ok
>
> long time I though about this feature.
>
> I am thinking so this should be fully isolated transaction - it should not
> be subtransaction, because then you can break database consistency - RI
>
>
>
I am missing something here, but how does making it a subtransaction break
consistency? Isnt that what should actually be happening so that the
autonomous transaction's changes are actually visible till the parent
transaction commits?

What am I missing here?

Regards,

Atri

Re: [HACKERS] Autonomous Transaction (WIP)

[Pavel Stehule]

2014-04-07 11:59 GMT+02:00 Rajeev rastogi :

>  On 07 April 2014 12:12, Pavel Stehule wrote:
>
>  >+1 for feature
>
> Thanks
>
>
>
> >-1 for Oracle syntax - it is hardly inconsistent with Postgres
>
> We can discuss and come out with the syntax based on everyone agreement.
>
> >Autonomous transactions should be used everywhere - not only in plpgsql
>
>
>
> Yes you are right. I am not planning to support only using plpgsql.
> Initially we can support this
>
> Using the standalone SQL-commands and then later we can enhance based on
> this infrastructure
>
> to be used using plpgsql, triggers.
>

ok

long time I though about this feature.

I am thinking so this should be fully isolated transaction - it should not
be subtransaction, because then you can break database consistency - RI

I am happy so someone does this job

Regards

Pavel


>
>
> *Thanks and Regards,*
>
> *Kumar Rajeev Rastogi *
>
>
>

Re: [HACKERS] Autonomous Transaction (WIP)

[Rajeev rastogi]

On 07 April 2014 12:12, Pavel Stehule wrote:

>+1 for feature
Thanks

>-1 for Oracle syntax - it is hardly inconsistent with Postgres
We can discuss and come out with the syntax based on everyone agreement.
>Autonomous transactions should be used everywhere - not only in plpgsql

Yes you are right. I am not planning to support only using plpgsql.  Initially 
we can support this
Using the standalone SQL-commands and then later we can enhance based on this 
infrastructure
to be used using plpgsql, triggers.

Thanks and Regards,
Kumar Rajeev Rastogi

Re: [HACKERS] Autonomous Transaction (WIP)

[Rajeev rastogi]

On 07 April 2014 12:20, Craig Ringer
> 
> > Syntax to create autonomous transaction can be as:
> >
> > */PRAGMA AUTONOMOUS TRANSACTION;/*
> 
> Wouldn't you want to use SET TRANSACTION for this?
> 
> Or a suffix on BEGIN, like BEGIN AUTONOMOUS TRANSACTION ?
> 
> What's the logic behind introducing "PRAGMA" ?
> 
> 
> If you wanted to use that syntax for Oracle compatibility you'd need to
> use:
> 
> PRAGMA AUTONOMOUS_TRANSACTION;
> 
> (note underscore). But really, this would no be a pragma at all,
> PostgreSQL doesn't really have the concept. Calling it that would just
> be misleading.

Actually it is same as oracle (i.e. PRAGMA AUTONOMOUS_TRANSACTION), it was just 
typo mistake in previous mail.
But if this is also not accepted then we can discuss and come out with a syntax 
based on everyone agreement.

> 
> > *_Starting of Autonomous  Transaction:_*
> >
> > Starting of autonomous transaction will be exactly same as starting
> > sub-transaction.
> 
> If you don't want it to dirty read data from the parent tx, or inherit
> parent locks, then it cannot be the same at all.

While starting sub-transaction, it is just initializing the resources required 
and
links the same to the parent transaction, which we require for autonomous 
transaction also.
I am not able to notice any issue as you mentioned above with this.
Please let me know if I am missing something or misunderstood your concern.

> > 2.  Freeing of all resource and popping of previous transaction
> > happens in the same way as sub-transaction.
> 
> I'm not sure what you mean here.

It means, during commit of autonomous transaction, freeing of all resource are 
done in the same way as done for sub-transaction.
Also current autonomous transaction gets popped out and points to the parent 
transaction in the similar way as done for sub-transaction.
 
> Overall, this looks like a HUGE job to make work well. I know some
> others have been doing work along the same lines, so hopefully you'll
> be able to collaborate and share ideas.

Yes it is huge works, so I have proposed in the beginning of 9.5 so that we can 
have multiple round of discussion and hence address
all concerns.
Also I have proposed to finish this feature in multiple rounds i.e. first 
patch, we can try to support autonomous transaction from
standalone SQL-command only, which will set-up infrastructure for future work 
in this area.

Using the WIP patch sent, I have done basic testing and it works fine.

Any comments?

Thanks and Regards,
Kumar Rajeev Rastogi





-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Autonomous Transaction (WIP)

[Ian Barwick]

On 07/04/14 15:50, Craig Ringer wrote:

On 04/07/2014 12:06 PM, Rajeev rastogi wrote:



Syntax to create autonomous transaction can be as:

 */PRAGMA AUTONOMOUS TRANSACTION;/*


Wouldn't you want to use SET TRANSACTION for this?

Or a suffix on BEGIN, like BEGIN AUTONOMOUS TRANSACTION ?

What's the logic behind introducing "PRAGMA" ?


If you wanted to use that syntax for Oracle compatibility you'd need to use:

 PRAGMA AUTONOMOUS_TRANSACTION;

(note underscore).


FWIW the implementation in the patch uses "PRAGMA 
AUTONOMOUS_TRANSACTION", the space is presumably a typo.



Regards

Ian Barwick


--
 Ian Barwick   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Autonomous Transaction (WIP)

[Craig Ringer]

On 04/07/2014 12:06 PM, Rajeev rastogi wrote:


> Syntax to create autonomous transaction can be as:
> 
> */PRAGMA AUTONOMOUS TRANSACTION;/*

Wouldn't you want to use SET TRANSACTION for this?

Or a suffix on BEGIN, like BEGIN AUTONOMOUS TRANSACTION ?

What's the logic behind introducing "PRAGMA" ?


If you wanted to use that syntax for Oracle compatibility you'd need to use:

PRAGMA AUTONOMOUS_TRANSACTION;

(note underscore). But really, this would no be a pragma at all,
PostgreSQL doesn't really have the concept. Calling it that would just
be misleading.




> *_Starting of Autonomous  Transaction:_*
> 
> Starting of autonomous transaction will be exactly same as starting
> sub-transaction.

If you don't want it to dirty read data from the parent tx, or inherit
parent locks, then it cannot be the same at all.

> 2.  Freeing of all resource and popping of previous transaction
> happens in the same way as sub-transaction.

I'm not sure what you mean here.


Overall, this looks like a HUGE job to make work well. I know some
others have been doing work along the same lines, so hopefully you'll be
able to collaborate and share ideas.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Autonomous Transaction (WIP)

[Pavel Stehule]

Hello

+1 for feature
-1 for Oracle syntax - it is hardly inconsistent with Postgres

Autonomous transactions should be used everywhere - not only in plpgsql

Regards

Pavel


2014-04-07 6:06 GMT+02:00 Rajeev rastogi :

>  I would like to propose “Autonomous Transaction” feature for 9.5.
> Details for the same are mentioned below:
>
>
>
> *What is Autonomous Transaction?*
>
> An autonomous transaction has its own COMMIT and ROLLBACK scope to ensure
> that its outcome does not affect the caller’s uncommitted changes.
> Additionally, the COMMITs and ROLLBACK in the calling transaction should
> not affect the changes that were finalized on the completion of autonomous
> transaction itself. Below are properties of autonomous transaction:
>
> 1.  The autonomous transaction does not see uncommitted changes made
> by the main transaction and does not share locks or resources with main
> transaction.
>
> 2.  Changes in autonomous transactions are visible to other
> transactions upon commit of the autonomous transactions. Thus, users can
> access the updated information without having to wait for the main
> transaction to commit.
>
> 3.  Autonomous transactions can start other autonomous transaction.
> There are no limit, other than resource limits, on how many levels of
> autonomous transaction can be started.
>
>
>
> *Use-case:*
>
> There are many use-case for this feature. One of the use-case is
> illustrated below
>
> Say a procedure is defined, which does some operation on the
> database and incase of any failure in operation on main table, it maintains
> the failure information in a separate relation. But because of current
> transaction behavior, once main table operation fails, it will rollback
> whole transaction and hence error logged in error relation will be also
> lost, which might have been required for future analysis.
>
>In order to solve this issue, we can use autonomous transaction as
> shown below:
>
> *CREATE OR REPLACE function operation(err_msg IN VARCHAR) returns void AS
> $$*
>
> *BEGIN*
>
> * INSERT INTO at_test(id, description) VALUES (998,
> ‘Description for 998’);*
>
> * INSERT INTO at_test(id, description) VALUES (999, NULL);*
>
> *EXCEPTION*
>
> * WHEN OTHER THEN*
>
> * PRAGMA AUTONOMOUS TRANSACTION;*
>
> * INSERT INTO error_logs(id, timestamp,
> err_msg) VALUES(nextval(‘errno’), timenow(), err_msg);*
>
> * COMMIT;*
>
> * RAISE not_null_violation;*
>
> *END;*
>
> *$$ LANGUAGE plpgsql;*
>
> So once we execute above procedure, second INSERT will fails and then
> within exception handling it will start autonomous transaction and log the
> error information in a separate table and then gets committed. So though
> operation to table at_test will fail and rollback, error information will
> persist in the error_logs table. After execution of procedure, record in
> two tables will be as below:
>
> *Postgres=# select * from error_logs;*
>
> *id |  log_time   | err_msg*
>
> *+-+-*
>
> *  5 | 2014-01-17 19:57:11 | error*
>
> *postgres=# select * from at_test;*
>
> *id | decsription*
>
> *+-*
>
> *(0 rows)*
>
>
>
> *Syntax:*
>
> Syntax to create autonomous transaction can be as:
>
> *PRAGMA AUTONOMOUS TRANSACTION;*
>
> This can be used with independent SQL commands, from procedure, triggers.
>
>
>
> *Implementation:*
>
> Implementation of autonomous transaction is based on the existing
> sub-transaction and main transaction. Most of the implementations are
> re-used for autonomous transaction also. Below are the brief details about
> the same:
>
>
>
> *Autonomous Transaction Storage:*
>
> As for main transaction, structure PGXACT is used to store main
> transactions, which are created in shared memory of size:
>
> (Number of process)*sizeof(struct PGXACT)
>
> Similarly a new structure will be defined to store autonomous transaction:
>
> *Struct PGAutonomousXACT*
>
> *{*
>
> *   TransactionId  xid;*
>
> *   TransactionId  xmin;*
>
> *   /* Store the level below main transaction as stored for
> sub-transaction*/*
>
> *   intnestingLevel;*
>
> *   struct XidCache   subxids;*
>
> *   bool overflowed;*
>
> *   bool delaychkpt;*
>
> *   uint  nxids;*
>
> *} PGAutonomousXACT;*
>
> All structure members of PGAutonomousXACT are same as used in PGXACT
> except nestingLevel as marked in bold color to store the level of
> transaction.
>
> Similar to main transaction, the memory allocated to store autonomous
> transaction will be:
>
> *(Number of process) * sizeof (struct PGAutonomousXACT)*MAX_AUTO_TX_LEVEL*
>
> Where MAX_AUTO_TX_LEVEL is maximum number of nested autonomous transaction
> level.
>
> Unlike main transaction, autonomous transaction cannot be a