Hello,
Today I'd like to present a way to get rid of code like this:
$$
BEGIN
BEGIN
INSERT INTO foo VALUES (1);
-- run some tests/checks/whatever
RAISE EXCEPTION 'OK';
EXCEPTION WHEN raise_exception THEN
IF SQLERRM <> 'OK' THEN
RAISE;
END IF;
END;
RETURN 'success';
END
$$
And replace it with code like this:
$$
BEGIN
<<testsomething>>
BEGIN
INSERT INTO foo VALUES (1);
-- run some tests/checks/whatever
EXIT USING ROLLBACK testsomething;
EXCEPTION WHEN others THEN
RAISE;
END;
RETURN 'success';
END
$$
I'm not set on the USING ROLLBACK syntax; it was the only thing I could
come up with that seemed even remotely sane and didn't break backwards
compatibility.
Thoughts? Patch attached, if someone cares.
.marko
*** a/doc/src/sgml/plpgsql.sgml
--- b/doc/src/sgml/plpgsql.sgml
***************
*** 2107,2113 **** END LOOP <optional> <replaceable>label</replaceable>
</optional>;
</indexterm>
<synopsis>
! EXIT <optional> <replaceable>label</replaceable> </optional> <optional> WHEN
<replaceable>boolean-expression</replaceable> </optional>;
</synopsis>
<para>
--- 2107,2113 ----
</indexterm>
<synopsis>
! EXIT <optional> <replaceable>label</replaceable> </optional> <optional> USING
ROLLBACK </optional> <optional> WHEN
<replaceable>boolean-expression</replaceable> </optional>;
</synopsis>
<para>
***************
*** 2121,2126 **** EXIT <optional> <replaceable>label</replaceable> </optional>
<optional> WHEN <re
--- 2121,2134 ----
</para>
<para>
+ If <literal>USING ROLLBACK</> is specified, instead of persisting the
+ changes made inside the escaped <literal>BEGIN</> blocks, they are
+ rolled back. The <replaceable>label</> must be the label of the
+ current or an outer level <literal>BEGIN</> block with an
+ <literal>EXCEPTION</> block.
+ </para>
+
+ <para>
If <literal>WHEN</> is specified, the loop exit occurs only if
<replaceable>boolean-expression</> is true. Otherwise, control passes
to the statement after <literal>EXIT</>.
*** a/src/pl/plpgsql/src/pl_exec.c
--- b/src/pl/plpgsql/src/pl_exec.c
***************
*** 1181,1188 **** exec_stmt_block(PLpgSQL_execstate *estate,
PLpgSQL_stmt_block *block)
resTypByVal, resTypLen);
}
! /* Commit the inner transaction, return to outer xact
context */
! ReleaseCurrentSubTransaction();
MemoryContextSwitchTo(oldcontext);
CurrentResourceOwner = oldowner;
--- 1181,1192 ----
resTypByVal, resTypLen);
}
! if (rc == PLPGSQL_RC_EXIT && estate->exitrollback)
! RollbackAndReleaseCurrentSubTransaction();
! else
! /* Commit the inner transaction, return to
outer xact context */
! ReleaseCurrentSubTransaction();
!
MemoryContextSwitchTo(oldcontext);
CurrentResourceOwner = oldowner;
***************
*** 1330,1335 **** exec_stmt_block(PLpgSQL_execstate *estate,
PLpgSQL_stmt_block *block)
--- 1334,1347 ----
return PLPGSQL_RC_EXIT;
if (strcmp(block->label, estate->exitlabel) != 0)
return PLPGSQL_RC_EXIT;
+ if (estate->exitrollback)
+ {
+ if (!block->exceptions)
+ ereport(ERROR,
+
(errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("the BEGIN
block targeted by EXIT USING ROLLBACK must have an EXCEPTION clause")));
+ estate->exitrollback = false;
+ }
estate->exitlabel = NULL;
return PLPGSQL_RC_OK;
***************
*** 1789,1794 **** exec_stmt_loop(PLpgSQL_execstate *estate, PLpgSQL_stmt_loop
*stmt)
--- 1801,1810 ----
return PLPGSQL_RC_EXIT;
if (strcmp(stmt->label, estate->exitlabel) != 0)
return PLPGSQL_RC_EXIT;
+ if (estate->exitrollback)
+ ereport(ERROR,
+
(errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("the target of
EXIT USING ROLLBACK must be a BEGIN block")));
estate->exitlabel = NULL;
return PLPGSQL_RC_OK;
***************
*** 1850,1855 **** exec_stmt_while(PLpgSQL_execstate *estate,
PLpgSQL_stmt_while *stmt)
--- 1866,1875 ----
return PLPGSQL_RC_EXIT;
if (strcmp(stmt->label, estate->exitlabel) != 0)
return PLPGSQL_RC_EXIT;
+ if (estate->exitrollback)
+ ereport(ERROR,
+
(errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("the target of
EXIT USING ROLLBACK must be a BEGIN block")));
estate->exitlabel = NULL;
return PLPGSQL_RC_OK;
***************
*** 1996,2001 **** exec_stmt_fori(PLpgSQL_execstate *estate, PLpgSQL_stmt_fori
*stmt)
--- 2016,2025 ----
strcmp(stmt->label, estate->exitlabel)
== 0)
{
/* labelled exit, matches the current stmt's
label */
+ if (estate->exitrollback)
+ ereport(ERROR,
+
(errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("the target of
EXIT USING ROLLBACK must be a BEGIN block")));
estate->exitlabel = NULL;
rc = PLPGSQL_RC_OK;
}
***************
*** 2348,2353 **** exec_stmt_foreach_a(PLpgSQL_execstate *estate,
PLpgSQL_stmt_foreach_a *stmt)
--- 2372,2381 ----
strcmp(stmt->label, estate->exitlabel)
== 0)
{
/* labelled exit, matches the current stmt's
label */
+ if (estate->exitrollback)
+ ereport(ERROR,
+
(errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("the target of
EXIT USING ROLLBACK must be a BEGIN block")));
estate->exitlabel = NULL;
rc = PLPGSQL_RC_OK;
}
***************
*** 2423,2428 **** exec_stmt_exit(PLpgSQL_execstate *estate, PLpgSQL_stmt_exit
*stmt)
--- 2451,2457 ----
}
estate->exitlabel = stmt->label;
+ estate->exitrollback = stmt->rollback;
if (stmt->is_exit)
return PLPGSQL_RC_EXIT;
else
***************
*** 3125,3130 **** plpgsql_estate_setup(PLpgSQL_execstate *estate,
--- 3154,3160 ----
estate->rettupdesc = NULL;
estate->exitlabel = NULL;
+ estate->exitrollback = false;
estate->cur_error = NULL;
estate->tuple_store = NULL;
***************
*** 4995,5000 **** exec_for_query(PLpgSQL_execstate *estate, PLpgSQL_stmt_forq
*stmt,
--- 5025,5034 ----
strcmp(stmt->label,
estate->exitlabel) == 0)
{
/* label matches this loop, so
exit loop */
+ if (estate->exitrollback)
+ ereport(ERROR,
+
(errcode(ERRCODE_SYNTAX_ERROR),
+
errmsg("the target of EXIT USING ROLLBACK must be a BEGIN block")));
estate->exitlabel = NULL;
rc = PLPGSQL_RC_OK;
}
*** a/src/pl/plpgsql/src/pl_funcs.c
--- b/src/pl/plpgsql/src/pl_funcs.c
***************
*** 1209,1214 **** dump_exit(PLpgSQL_stmt_exit *stmt)
--- 1209,1216 ----
{
dump_ind();
printf("%s", stmt->is_exit ? "EXIT" : "CONTINUE");
+ if (stmt->rollback)
+ printf(" USING ROLLBACK");
if (stmt->label != NULL)
printf(" label='%s'", stmt->label);
if (stmt->cond != NULL)
*** a/src/pl/plpgsql/src/pl_gram.y
--- b/src/pl/plpgsql/src/pl_gram.y
***************
*** 178,183 **** static List *read_raise_options(void);
--- 178,184 ----
%type <expr> expr_until_semi expr_until_rightbracket
%type <expr> expr_until_then expr_until_loop opt_expr_until_when
%type <expr> opt_exitcond
+ %type <boolean> opt_using_rollback
%type <ival> assign_var foreach_slice
%type <var> cursor_variable
***************
*** 1660,1675 **** foreach_slice :
}
;
! stmt_exit : exit_type opt_label opt_exitcond
{
PLpgSQL_stmt_exit *new;
new =
palloc0(sizeof(PLpgSQL_stmt_exit));
new->cmd_type =
PLPGSQL_STMT_EXIT;
new->is_exit = $1;
new->lineno =
plpgsql_location_to_lineno(@1);
! new->label = $2;
! new->cond = $3;
$$ = (PLpgSQL_stmt *)new;
}
--- 1661,1688 ----
}
;
! stmt_exit : exit_type opt_using_rollback opt_label opt_exitcond
{
PLpgSQL_stmt_exit *new;
new =
palloc0(sizeof(PLpgSQL_stmt_exit));
new->cmd_type =
PLPGSQL_STMT_EXIT;
new->is_exit = $1;
+ new->rollback = $2;
new->lineno =
plpgsql_location_to_lineno(@1);
! new->label = $3;
! new->cond = $4;
!
! if (new->rollback &&
!new->is_exit)
! ereport(ERROR,
!
(errcode(ERRCODE_SYNTAX_ERROR),
!
errmsg("USING ROLLBACK is only supported with EXIT"),
!
parser_errposition(@3)));
! else if (new->rollback &&
new->label == NULL)
! ereport(ERROR,
!
(errcode(ERRCODE_SYNTAX_ERROR),
!
errmsg("EXIT USING ROLLBACK requires a label"),
!
parser_errposition(@3)));
$$ = (PLpgSQL_stmt *)new;
}
***************
*** 1685,1690 **** exit_type : K_EXIT
--- 1698,1716 ----
}
;
+ opt_using_rollback :
+ {
+ $$ = false;
+ }
+ |
+ K_USING T_WORD
+ {
+ if (strcmp($2.ident,
"rollback") != 0)
+ yyerror("syntax error");
+ $$ = true;
+ }
+ ;
+
stmt_return : K_RETURN
{
int tok;
*** a/src/pl/plpgsql/src/plpgsql.h
--- b/src/pl/plpgsql/src/plpgsql.h
***************
*** 582,587 **** typedef struct
--- 582,588 ----
int cmd_type;
int lineno;
bool is_exit; /* Is this an exit or a
continue? */
+ bool rollback; /* If this is an exit, true if
we should roll back the current subtxn */
char *label; /* NULL if it's an unlabelled
EXIT/CONTINUE */
PLpgSQL_expr *cond;
} PLpgSQL_stmt_exit;
***************
*** 770,775 **** typedef struct PLpgSQL_execstate
--- 771,778 ----
TupleDesc rettupdesc;
char *exitlabel; /* the "target" label of the current
EXIT or
* CONTINUE
stmt, if any */
+ bool exitrollback; /* true if we should also roll back
when exiting
+ the
BEGIN/END block matching "exitlabel" */
ErrorData *cur_error; /* current exception handler's error */
Tuplestorestate *tuple_store; /* SRFs accumulate results here
*/
*** a/src/test/regress/expected/plpgsql.out
--- b/src/test/regress/expected/plpgsql.out
***************
*** 2896,2901 **** $$ language plpgsql;
--- 2896,3026 ----
ERROR: end label "outer_label" specified for unlabelled block
LINE 6: end loop outer_label;
^
+ -- exit using rollback
+ create temporary table foo(a text not null);
+ create function exit_using_rollback() returns void as $$
+ begin
+ <<subtxn>>
+ begin
+ insert into foo values ('not ok');
+ exit using rollback subtxn;
+ exception when others then
+ raise;
+ end;
+ insert into foo values ('ok');
+ end;
+ $$ language plpgsql;
+ select exit_using_rollback();
+ exit_using_rollback
+ ---------------------
+
+ (1 row)
+
+ select * from foo;
+ a
+ ----
+ ok
+ (1 row)
+
+ -- roll back two blocks at once
+ create or replace function exit_using_rollback() returns void as $$
+ begin
+ <<subtxn>>
+ begin
+ insert into foo values ('not ok');
+ begin
+ insert into foo values ('not ok');
+ exit using rollback subtxn;
+ end;
+ exception when others then
+ raise;
+ end;
+ end;
+ $$ language plpgsql;
+ select exit_using_rollback();
+ exit_using_rollback
+ ---------------------
+
+ (1 row)
+
+ select * from foo;
+ a
+ ----
+ ok
+ (1 row)
+
+ -- roll back two blocks at once, both with EXCEPTION blocks
+ create or replace function exit_using_rollback() returns void as $$
+ begin
+ <<subtxn>>
+ begin
+ insert into foo values ('not ok');
+ begin
+ insert into foo values ('not ok');
+ exit using rollback subtxn;
+ exception when others then
+ raise;
+ end;
+ exception when others then
+ raise;
+ end;
+ end;
+ $$ language plpgsql;
+ select exit_using_rollback();
+ exit_using_rollback
+ ---------------------
+
+ (1 row)
+
+ select * from foo;
+ a
+ ----
+ ok
+ (1 row)
+
+ drop table foo;
+ -- should fail: not allowed to target LOOP label (runtime failure)
+ create or replace function exit_using_rollback() returns void as $$
+ begin
+ <<invalid>>
+ loop
+ exit using rollback invalid;
+ end loop;
+ end;
+ $$ language plpgsql;
+ select exit_using_rollback();
+ ERROR: the target of EXIT USING ROLLBACK must be a BEGIN block
+ CONTEXT: PL/pgSQL function exit_using_rollback() line 4 at LOOP
+ -- should fail: invalid syntax
+ create or replace function exit_using_rollback() returns void as $$
+ begin
+ continue using rollback;
+ end;
+ $$ language plpgsql;
+ ERROR: USING ROLLBACK is only supported with EXIT
+ LINE 3: continue using rollback;
+ ^
+ -- should fail: requires label
+ create or replace function exit_using_rollback() returns void as $$
+ begin
+ exit using rollback;
+ end;
+ $$ language plpgsql;
+ ERROR: EXIT USING ROLLBACK requires a label
+ LINE 3: exit using rollback;
+ ^
+ -- should fail: requires exception handler (runtime failure)
+ create or replace function exit_using_rollback() returns void as $$
+ begin
+ <<nosubtxn>>
+ begin
+ exit using rollback nosubtxn;
+ end;
+ end;
+ $$ language plpgsql;
+ select exit_using_rollback();
+ ERROR: the BEGIN block targeted by EXIT USING ROLLBACK must have an
EXCEPTION clause
+ CONTEXT: PL/pgSQL function exit_using_rollback() line 4 at statement block
-- using list of scalars in fori and fore stmts
create function for_vect() returns void as $proc$
<<lbl>>declare a integer; b varchar; c varchar; r record;
*** a/src/test/regress/sql/plpgsql.sql
--- b/src/test/regress/sql/plpgsql.sql
***************
*** 2429,2434 **** begin
--- 2429,2532 ----
end;
$$ language plpgsql;
+ -- exit using rollback
+ create temporary table foo(a text not null);
+ create function exit_using_rollback() returns void as $$
+ begin
+ <<subtxn>>
+ begin
+ insert into foo values ('not ok');
+ exit using rollback subtxn;
+ exception when others then
+ raise;
+ end;
+ insert into foo values ('ok');
+ end;
+ $$ language plpgsql;
+
+ select exit_using_rollback();
+ select * from foo;
+
+ -- roll back two blocks at once
+ create or replace function exit_using_rollback() returns void as $$
+ begin
+ <<subtxn>>
+ begin
+ insert into foo values ('not ok');
+ begin
+ insert into foo values ('not ok');
+ exit using rollback subtxn;
+ end;
+ exception when others then
+ raise;
+ end;
+ end;
+ $$ language plpgsql;
+
+ select exit_using_rollback();
+ select * from foo;
+
+ -- roll back two blocks at once, both with EXCEPTION blocks
+ create or replace function exit_using_rollback() returns void as $$
+ begin
+ <<subtxn>>
+ begin
+ insert into foo values ('not ok');
+ begin
+ insert into foo values ('not ok');
+ exit using rollback subtxn;
+ exception when others then
+ raise;
+ end;
+ exception when others then
+ raise;
+ end;
+ end;
+ $$ language plpgsql;
+
+ select exit_using_rollback();
+ select * from foo;
+
+ drop table foo;
+
+ -- should fail: not allowed to target LOOP label (runtime failure)
+ create or replace function exit_using_rollback() returns void as $$
+ begin
+ <<invalid>>
+ loop
+ exit using rollback invalid;
+ end loop;
+ end;
+ $$ language plpgsql;
+
+ select exit_using_rollback();
+
+ -- should fail: invalid syntax
+ create or replace function exit_using_rollback() returns void as $$
+ begin
+ continue using rollback;
+ end;
+ $$ language plpgsql;
+
+ -- should fail: requires label
+ create or replace function exit_using_rollback() returns void as $$
+ begin
+ exit using rollback;
+ end;
+ $$ language plpgsql;
+
+ -- should fail: requires exception handler (runtime failure)
+ create or replace function exit_using_rollback() returns void as $$
+ begin
+ <<nosubtxn>>
+ begin
+ exit using rollback nosubtxn;
+ end;
+ end;
+ $$ language plpgsql;
+
+ select exit_using_rollback();
+
-- using list of scalars in fori and fore stmts
create function for_vect() returns void as $proc$
<<lbl>>declare a integer; b varchar; c varchar; r record;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
