Re: Two features left - Mailing list pgsql-general

Jon,

What I would like to be able to do is within a loop for example, commit
each iteration.


Jon Swinth wrote:
>
> Hmm... I'm not quite sure what you mean by function start point.  It has been
> a while since I did any embeded DB code.  Everything now is through a JDBC
> interface using standard SQL calls.
>
> It sounds like you are wanting to be able to place a BEGIN statement within a
> function call to make sure the calls within the function are in a transaction
> and to be able to abort to that point.  The issue I see with doing your
> nested transactions vs. savepoint is that you would have to invent a way to
> mark the end of the sub-transaction without a commit/rollback.  Here is an
> example:
>
> BEGIN;
> UPDATE...
> function_a(){
>   BEGIN
>   UPDATE
>   function_b() {
>     BEGIN
>     UPDATE
>   } //end function_b
>   UPDATE
>   ABORT
> } //end function_a
> UPDATE
> COMMIT;
>
> How does the system know that the ABORT in the second half of function_a
> should rollback to the BEGIN in function_a rather than the BEGIN in
> function_b?  The other issue I have seen is where you want to overwrite a
> point, which you can usually do with a SAVEPOINT structure.  This is
> especially usefull in a looping structure where you want to be albe to roll
> out one loop.
>
> BEGIN;
> UPDATE...
> function_a(){
>   UPDATE
>   SAVEPOINT a ;
>   LOOP
>   function_b() {
>     SAVEPOINT b;
>     UPDATE
>     UPDATE
>     UPDATE
>     IF error ROLLBACK TO b ;
>   } //end function_b
>   UPDATE
>   SAVEPOINT a ;
>   END LOOP;
> } //end function_a
> UPDATE
> COMMIT;
>
> In this case the function_b may be something that tries to place something
> somewhere and has multiple updates.  If one of the updates fails then you
> want to be able to rollback to the beginning loop value and let the next
> iteration of the loop try out the next location.  This type of structure is
> especially usefull when there are many simultanious threads going on doing a
> simular operation.
>
> On Wednesday 27 November 2002 01:45 pm, Jean-Luc Lachance wrote:
> > Jon,
> >
> > That is all fine and dandy, but aren't function start point candidate
> > for a rollback to point?
> > A transaction is currently implicitely started on function call, and we
> > get into the same problem as with nested transaction when a function
> > calls another one.
> >
> > Don't get me wrong, I thing SAVEPOINT and ROLLBACK TO is a great idea,
> > but nested transaction is needed.
> >
> > JLL
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly