This was motivated by the SELECT INTO EXACT discussion at
http://archives.postgresql.org/pgsql-patches/2005-07/msg00559.php.

The idea is to allow a PL/pgSQL exception to not automatically rollback
the work done by the current block.  The benefit is that exception
handling can be used as a program flow control technique, without
invoking transaction management mechanisms.  This also adds additional
means to enhanced Oracle PL/SQL compatibility.

The patch implements an optional NOSAVEPOINT keyword after the EXCEPTION
keyword that begins the exception handler definition.  Here is an
excerpt from the patched documentation:

--------beginning of excerpt-----------------------
If NOSAVEPOINT is not specified then a transaction savepoint is
established immediately prior to the execution of statements. If an
exception is raised then the effects of statements on the database are
rolled back to this savepoint. If NOSAVEPOINT is specified then no
savepoint is established. In this case a handled exception does not roll
back the effects of statements. An unhandled exception, however, will
still propagate out as usual, and any database effects may or may not be
rolled back, depending on the characteristics of the enclosing
block(s). 

        Tip:  Establishing a savepoint can be expensive. If you do not
        need the ability rollback the block's effect on the database,
        then either use the NOSAVEPOINT option, or avoid the EXCEPTION
        clause altogether.
--------end of excerpt-----------------------

Implementation question:

In pl_exec.c the new option causes the "BeginInternalSubTransaction,"
"ReleaseCurrentSubTransaction," and 
"RollbackAndReleaseCurrentSubTransaction" function calls to be skipped.
However, the corresponding "MemoryContextSwitchTo" and related calls are
still performed.  Should these calls also be dependent on the new
option?  Would that be more correct, and/or a performance improvement?
Index: doc/src/sgml/plpgsql.sgml
===================================================================
RCS file: /var/local/pgcvs/pgsql/doc/src/sgml/plpgsql.sgml,v
retrieving revision 1.75
diff -c -r1.75 plpgsql.sgml
*** doc/src/sgml/plpgsql.sgml	2 Jul 2005 08:59:47 -0000	1.75
--- doc/src/sgml/plpgsql.sgml	3 Aug 2005 19:42:48 -0000
***************
*** 2086,2092 ****
      <replaceable>declarations</replaceable> </optional>
  BEGIN
      <replaceable>statements</replaceable>
! EXCEPTION
      WHEN <replaceable>condition</replaceable> <optional> OR <replaceable>condition</replaceable> ... </optional> THEN
          <replaceable>handler_statements</replaceable>
      <optional> WHEN <replaceable>condition</replaceable> <optional> OR <replaceable>condition</replaceable> ... </optional> THEN
--- 2086,2092 ----
      <replaceable>declarations</replaceable> </optional>
  BEGIN
      <replaceable>statements</replaceable>
! EXCEPTION <optional>NOSAVEPOINT</optional>
      WHEN <replaceable>condition</replaceable> <optional> OR <replaceable>condition</replaceable> ... </optional> THEN
          <replaceable>handler_statements</replaceable>
      <optional> WHEN <replaceable>condition</replaceable> <optional> OR <replaceable>condition</replaceable> ... </optional> THEN
***************
*** 2104,2117 ****
       processing of the <replaceable>statements</replaceable> is
       abandoned, and control passes to the <literal>EXCEPTION</> list.
       The list is searched for the first <replaceable>condition</replaceable>
!      matching the error that occurred.  If a match is found, the
!      corresponding <replaceable>handler_statements</replaceable> are
!      executed, and then control passes to the next statement after
!      <literal>END</>.  If no match is found, the error propagates out
!      as though the <literal>EXCEPTION</> clause were not there at all:
!      the error can be caught by an enclosing block with
!      <literal>EXCEPTION</>, or if there is none it aborts processing
!      of the function.
      </para>
  
      <para>
--- 2104,2140 ----
       processing of the <replaceable>statements</replaceable> is
       abandoned, and control passes to the <literal>EXCEPTION</> list.
       The list is searched for the first <replaceable>condition</replaceable>
!      matching the error that occurred.  If a match is found, then the
!      exception is considered handled, and the corresponding
!      <replaceable>handler_statements</replaceable> are executed.  Control
!      then passes to the next statement after <literal>END</>.  If no match
!      is found, the unhandled error propagates out as though the
!      <literal>EXCEPTION</> clause were not there at all.  The error can then
!      be caught by an enclosing block with <literal>EXCEPTION</>, or if there
!      is none it aborts processing of the function.
!     </para>
! 
!     <para>
!      If <literal>NOSAVEPOINT</literal> is not specified then a transaction
!      savepoint is established immediately prior to the execution of
!      <replaceable>statements</replaceable>.  If an exception is raised then
!      the effects of <replaceable>statements</replaceable> on the database
!      are rolled back to this savepoint.  If <literal>NOSAVEPOINT</literal>
!      is specified then no savepoint is established.  In this case a handled
!      exception does not roll back the effects of
!      <replaceable>statements</replaceable>.  An unhandled exception,
!      however, will still propagate out as usual, and any database effects may
!      or may not be rolled back, depending on the characteristics of the enclosing
!      block(s).
! 
!     <tip>
!      <para>
!       Establishing a savepoint can be expensive.   If you do not need the
!       ability rollback the block's effect on the database, then either use
!       the <literal>NOSAVEPOINT</> option, or avoid the
!       <literal>EXCEPTION</literal> clause altogether.
!      </para>
!     </tip>
      </para>
  
      <para>
***************
*** 2128,2141 ****
      <para>
       If a new error occurs within the selected
       <replaceable>handler_statements</replaceable>, it cannot be caught
!      by this <literal>EXCEPTION</> clause, but is propagated out.
       A surrounding <literal>EXCEPTION</> clause could catch it.
      </para>
  
      <para>
       When an error is caught by an <literal>EXCEPTION</> clause,
       the local variables of the <application>PL/pgSQL</> function
!      remain as they were when the error occurred, but all changes
       to persistent database state within the block are rolled back.
       As an example, consider this fragment:
  
--- 2151,2165 ----
      <para>
       If a new error occurs within the selected
       <replaceable>handler_statements</replaceable>, it cannot be caught
!      By this <literal>EXCEPTION</> clause, but is propagated out.
       A surrounding <literal>EXCEPTION</> clause could catch it.
      </para>
  
      <para>
       When an error is caught by an <literal>EXCEPTION</> clause,
       the local variables of the <application>PL/pgSQL</> function
!      remain as they were when the error occurred.  However, unless
!      <literal>NOSAVEPOINT</literal> is specified, then all changes
       to persistent database state within the block are rolled back.
       As an example, consider this fragment:
  
***************
*** 2162,2175 ****
       contains <literal>Tom Jones</> not <literal>Joe Jones</>.
      </para>
  
-     <tip>
-      <para>
-       A block containing an <literal>EXCEPTION</> clause is significantly
-       more expensive to enter and exit than a block without one.  Therefore,
-       don't use <literal>EXCEPTION</> without need.
-      </para>
-     </tip>
- 
      <para>
       Within an exception handler, the <varname>SQLSTATE</varname>
       variable contains the error code that corresponds to the
--- 2186,2191 ----
Index: src/pl/plpgsql/src/gram.y
===================================================================
RCS file: /var/local/pgcvs/pgsql/src/pl/plpgsql/src/gram.y,v
retrieving revision 1.80
diff -c -r1.80 gram.y
*** src/pl/plpgsql/src/gram.y	2 Jul 2005 17:01:59 -0000	1.80
--- src/pl/plpgsql/src/gram.y	3 Aug 2005 19:18:13 -0000
***************
*** 186,191 ****
--- 186,192 ----
  %token	K_LOG
  %token	K_LOOP
  %token	K_NEXT
+ %token	K_NOSAVEPOINT
  %token	K_NOT
  %token	K_NOTICE
  %token	K_NULL
***************
*** 1534,1539 ****
--- 1535,1541 ----
  						 */
  						PLpgSQL_exception_block *new = palloc(sizeof(PLpgSQL_exception_block));
  						PLpgSQL_variable *var;
+ 						int tok;
  
  						var = plpgsql_build_variable("sqlstate", $2,
  													 plpgsql_build_datatype(TEXTOID, -1),
***************
*** 1547,1552 ****
--- 1549,1561 ----
  						((PLpgSQL_var *) var)->isconst = true;
  						new->sqlerrm_varno = var->dno;
  
+ 						new->nosavepoint = false;
+ 						tok = yylex();
+ 						if (tok == K_NOSAVEPOINT)
+ 						    new->nosavepoint = true;
+ 						else
+ 						    plpgsql_push_back_token(tok);						
+ 
  						$<exception_block>$ = new;
  					}
  					proc_exceptions
Index: src/pl/plpgsql/src/pl_exec.c
===================================================================
RCS file: /var/local/pgcvs/pgsql/src/pl/plpgsql/src/pl_exec.c,v
retrieving revision 1.151
diff -c -r1.151 pl_exec.c
*** src/pl/plpgsql/src/pl_exec.c	28 Jul 2005 07:51:13 -0000	1.151
--- src/pl/plpgsql/src/pl_exec.c	3 Aug 2005 19:18:13 -0000
***************
*** 842,848 ****
  		MemoryContext oldcontext = CurrentMemoryContext;
  		ResourceOwner oldowner = CurrentResourceOwner;
  
! 		BeginInternalSubTransaction(NULL);
  		/* Want to run statements inside function's memory context */
  		MemoryContextSwitchTo(oldcontext);
  
--- 842,850 ----
  		MemoryContext oldcontext = CurrentMemoryContext;
  		ResourceOwner oldowner = CurrentResourceOwner;
  
! 		if (!block->exceptions->nosavepoint)
! 			BeginInternalSubTransaction(NULL);
! 
  		/* Want to run statements inside function's memory context */
  		MemoryContextSwitchTo(oldcontext);
  
***************
*** 851,857 ****
  			rc = exec_stmts(estate, block->body);
  
  			/* Commit the inner transaction, return to outer xact context */
! 			ReleaseCurrentSubTransaction();
  			MemoryContextSwitchTo(oldcontext);
  			CurrentResourceOwner = oldowner;
  
--- 853,861 ----
  			rc = exec_stmts(estate, block->body);
  
  			/* Commit the inner transaction, return to outer xact context */
! 			if (!block->exceptions->nosavepoint)
! 				ReleaseCurrentSubTransaction();
! 
  			MemoryContextSwitchTo(oldcontext);
  			CurrentResourceOwner = oldowner;
  
***************
*** 872,878 ****
  			FlushErrorState();
  
  			/* Abort the inner transaction */
! 			RollbackAndReleaseCurrentSubTransaction();
  			MemoryContextSwitchTo(oldcontext);
  			CurrentResourceOwner = oldowner;
  
--- 876,884 ----
  			FlushErrorState();
  
  			/* Abort the inner transaction */
! 			if (!block->exceptions->nosavepoint)
! 				RollbackAndReleaseCurrentSubTransaction();
! 
  			MemoryContextSwitchTo(oldcontext);
  			CurrentResourceOwner = oldowner;
  
Index: src/pl/plpgsql/src/plpgsql.h
===================================================================
RCS file: /var/local/pgcvs/pgsql/src/pl/plpgsql/src/plpgsql.h,v
retrieving revision 1.64
diff -c -r1.64 plpgsql.h
*** src/pl/plpgsql/src/plpgsql.h	22 Jun 2005 01:35:02 -0000	1.64
--- src/pl/plpgsql/src/plpgsql.h	3 Aug 2005 19:18:13 -0000
***************
*** 327,332 ****
--- 327,333 ----
  {
  	int			sqlstate_varno;
  	int			sqlerrm_varno;
+ 	bool		nosavepoint;
  	List	   *exc_list;		/* List of WHEN clauses */
  } PLpgSQL_exception_block;
  
Index: src/pl/plpgsql/src/scan.l
===================================================================
RCS file: /var/local/pgcvs/pgsql/src/pl/plpgsql/src/scan.l,v
retrieving revision 1.42
diff -c -r1.42 scan.l
*** src/pl/plpgsql/src/scan.l	26 Jun 2005 19:16:07 -0000	1.42
--- src/pl/plpgsql/src/scan.l	3 Aug 2005 19:18:13 -0000
***************
*** 164,169 ****
--- 164,170 ----
  log				{ return K_LOG;				}
  loop			{ return K_LOOP;			}
  next			{ return K_NEXT;			}
+ nosavepoint		{ return K_NOSAVEPOINT;		}
  not				{ return K_NOT;				}
  notice			{ return K_NOTICE;			}
  null			{ return K_NULL;			}
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [EMAIL PROTECTED] so that your
       message can get through to the mailing list cleanly

Reply via email to