Thread: rollback to savepoint issue
Hello guys,
I can't use the savepoint and rollback to savepoint clause.
I've found some similar problems around on the web, but I can't catch the good way to proceed.
What I'm trying to do is:
- compare new set of attribute with older
- if some attributes are part of old set and not in the new one: delete the old
- but if parameter "on_misisng_delete" is false rollback delete command and rais exception
The original code in embedded in a function, but the problem is the same:
ERROR: syntax error at or near "to"
LINE 41: rollback to savepoint deleteAttribute;
^
SQL state: 42601
Character: 768
LINE 41: rollback to savepoint deleteAttribute;
^
SQL state: 42601
Character: 768
Code extracted:
do $body$
declare
on_missing_delete boolean=false;
_i integer;
_vAttributeName text='paperi';
_importo integer= 5000;
begin
savepoint deleteAttribute;
execute format($$
with ru as (
update public.%I as q
set is_closed=true
, modify_user_id='process:definitionUpdate'
where q.importo > $1
returning 1
)
select count(*) from ru
;$$
, _vAttributeName)
using _importo
into _i;
--> If I can't delete and there are row to delete raise excpetion
if not on_missing_delete and _i > 0 then
--> undo deletion
rollback to savepoint deleteAttribute;
raise exception 'New attributes list foresees to remove % attribute(s), but parameter "on missing delete" is false.', _i;
else
release savepoint deleteAttribute;
end if;
rollback;
end;
$body$;
declare
on_missing_delete boolean=false;
_i integer;
_vAttributeName text='paperi';
_importo integer= 5000;
begin
savepoint deleteAttribute;
execute format($$
with ru as (
update public.%I as q
set is_closed=true
, modify_user_id='process:definitionUpdate'
where q.importo > $1
returning 1
)
select count(*) from ru
;$$
, _vAttributeName)
using _importo
into _i;
--> If I can't delete and there are row to delete raise excpetion
if not on_missing_delete and _i > 0 then
--> undo deletion
rollback to savepoint deleteAttribute;
raise exception 'New attributes list foresees to remove % attribute(s), but parameter "on missing delete" is false.', _i;
else
release savepoint deleteAttribute;
end if;
rollback;
end;
$body$;
I know I can modify the code, but I wish to understand why I can't use rollback to save point
On 04/09/2023 11:51 CEST Lorusso Domenico <domenico.l76@gmail.com> wrote: > I can't use the savepoint and rollback to savepoint clause. > I've found some similar problems around on the web, but I can't catch the > good way to proceed. > > What I'm trying to do is: > * compare new set of attribute with older > * if some attributes are part of old set and not in the new one: delete > the old > * but if parameter "on_misisng_delete" is false rollback delete command > and rais exception > The original code in embedded in a function, but the problem is the same: Transaction control is not possible in functions. Only in procedures (CALL) and DO blocks. > ERROR: syntax error at or near "to" > LINE 41: rollback to savepoint deleteAttribute; Use BEGIN ... EXCEPTION ... END instead of manual savepoints. [1] [1] https://www.postgresql.org/docs/current/plpgsql-porting.html#PLPGSQL-PORTING-EXCEPTIONS -- Erik
On Mon, Sep 4, 2023 at 3:47 PM Erik Wienhold <ewie@ewie.name> wrote:
On 04/09/2023 11:51 CEST Lorusso Domenico <domenico.l76@gmail.com> wrote:
Transaction control is not possible in functions. Only in procedures (CALL)
and DO blocks.
> ERROR: syntax error at or near "to"
> LINE 41: rollback to savepoint deleteAttribute;
Use BEGIN ... EXCEPTION ... END instead of manual savepoints. [1]
[1] https://www.postgresql.org/docs/current/plpgsql-porting.html#PLPGSQL-PORTING-EXCEPTIONS
Hi Erik. And experts at large. What's the underlying mechanism though? An implicit SAVEPOINT?
The reason I'm asking, is that we are using explicit SAVEPOINTs in client C/C++ code,
to "emulate" per-Statement "isolation", like other DBs have (Oracle, SQLite), instead of
having to ROLLBACK the "whole" transaction.
Which means extra explicit round-trips to the server to establish and "move along" the savepoint,
per command. Which I think is "expensive". So would anonymous DO blocks be a better solution?
If BEGIN/EXCEPTION uses an "implicit" / "hidden" SAVEPOINT, would that still be better then?
And if we switch to anon DO+EXCEPTION blocks, is it just as easy to bind to the inner SQL command(s)?
Sorry if my questions are a little vague. This is an area that's still fuzzy for me, I'm not versed in pgPL/SQL.
Finally, I've read "horror stories" about SAVEPOINTs, with performance-cliffs when using them,
because if I recall correctly, there's only room in pages (?) to deal with just a few efficiently,
before additional "storage" must be allocated somewhere else, leading to contention. Does that
apply to that BEGIN+EXCEPTION mechanism too?
Thanks for any insights. --DD
On Mon, Sep 4, 2023 at 4:22 PM Dominique Devienne <ddevienne@gmail.com> wrote:
On Mon, Sep 4, 2023 at 3:47 PM Erik Wienhold <ewie@ewie.name> wrote:On 04/09/2023 11:51 CEST Lorusso Domenico <domenico.l76@gmail.com> wrote:
Transaction control is not possible in functions. Only in procedures (CALL)
and DO blocks.
> ERROR: syntax error at or near "to"
> LINE 41: rollback to savepoint deleteAttribute;
Use BEGIN ... EXCEPTION ... END instead of manual savepoints. [1]
[1] https://www.postgresql.org/docs/current/plpgsql-porting.html#PLPGSQL-PORTING-EXCEPTIONSHi Erik. And experts at large. What's the underlying mechanism though? An implicit SAVEPOINT?[...] Thanks for any insights. --DD
(I guess I should have done a little more research before posting...)
I think Laurenz' old post answers most of my question. I probably read it before too!
Different devs work on different parts of our (large) system / apps, and we don't all use
SAVEPOINTs. I don't, but one of my colleagues does. Thus my (hasty) questions above.
Sorry for the noise. --DD
On Monday, September 4, 2023, Erik Wienhold <ewie@ewie.name> wrote:
On 04/09/2023 11:51 CEST Lorusso Domenico <domenico.l76@gmail.com> wrote:
> I can't use the savepoint and rollback to savepoint clause.
> I've found some similar problems around on the web, but I can't catch the
> good way to proceed.
>
> What I'm trying to do is:
> * compare new set of attribute with older
> * if some attributes are part of old set and not in the new one: delete
> the old
> * but if parameter "on_misisng_delete" is false rollback delete command
> and rais exception
> The original code in embedded in a function, but the problem is the same:
Transaction control is not possible in functions. Only in procedures (CALL)
and DO blocks.
Then explain why the original savepoint command wasn’t a syntax, or runtime, error? Plus, the error is syntax, usually when you try something that exists but is disallowed the system gives you some kind of invalid state exception at runtime,
David J.
Dominique Devienne <ddevienne@gmail.com> writes:
> Hi Erik. And experts at large. What's the underlying mechanism though? An
> implicit SAVEPOINT?
Yes, savepoints and plpgsql exception blocks are built on the same
"subtransaction" infrastructure.
> Which means extra explicit round-trips to the server to establish and "move
> along" the savepoint,
> per command. Which I think is "expensive". So would anonymous DO blocks be
> a better solution?
In general, moving logic to the server side is an effective way of
removing network round trips. That could be DO blocks, functions,
or procedures. If you're going to execute the same code over and
over within a session, a function or procedure will probably be
a better choice than re-issuing identical DO blocks.
regards, tom lane
On 9/4/23 07:56, David G. Johnston wrote: > On Monday, September 4, 2023, Erik Wienhold <ewie@ewie.name > <mailto:ewie@ewie.name>> wrote: > > On 04/09/2023 11:51 CEST Lorusso Domenico <domenico.l76@gmail.com > <mailto:domenico.l76@gmail.com>> wrote: > > > I can't use the savepoint and rollback to savepoint clause. > > I've found some similar problems around on the web, but I can't > catch the > > good way to proceed. > > > > What I'm trying to do is: > > * compare new set of attribute with older > > * if some attributes are part of old set and not in the new > one: delete > > the old > > * but if parameter "on_misisng_delete" is false rollback delete > command > > and rais exception > > The original code in embedded in a function, but the problem is > the same: > > Transaction control is not possible in functions. Only in > procedures (CALL) > and DO blocks. > > > Then explain why the original savepoint command wasn’t a syntax, or > runtime, error? Plus, the error is syntax, usually when you try > something that exists but is disallowed the system gives you some kind > of invalid state exception at runtime, Maybe this?: https://www.postgresql.org/docs/current/sql-do.html "If DO is executed in a transaction block, then the procedure code cannot execute transaction control statements. Transaction control statements are only allowed if DO is executed in its own transaction." > > David J. > -- Adrian Klaver adrian.klaver@aklaver.com
On 04/09/2023 16:56 CEST David G. Johnston <david.g.johnston@gmail.com> wrote:
> On Monday, September 4, 2023, Erik Wienhold <ewie@ewie.name> wrote:
>
> > On 04/09/2023 11:51 CEST Lorusso Domenico <domenico.l76@gmail.com> wrote:
> >
> > > The original code in embedded in a function, but the problem is the same:
> >
> > Transaction control is not possible in functions. Only in procedures (CALL)
> > and DO blocks.
>
> Then explain why the original savepoint command wasn’t a syntax, or runtime,
> error?
I don't need to because CREATE {FUNCTION | PROCEDURE} already fails because of
ROLLBACK TO SAVEPOINT. And without a function to execute there can't be any
runtime error because of SAVEPOINT. My point was about transaction control in
plpgsql in general.
> Plus, the error is syntax, usually when you try something that exists
> but is disallowed the system gives you some kind of invalid state exception
> at runtime,
--
Erik
On 2023-Sep-04, Erik Wienhold wrote:
> On 04/09/2023 16:56 CEST David G. Johnston <david.g.johnston@gmail.com> wrote:
>
> > On Monday, September 4, 2023, Erik Wienhold <ewie@ewie.name> wrote:
> >
> > > On 04/09/2023 11:51 CEST Lorusso Domenico <domenico.l76@gmail.com> wrote:
> > >
> > > > The original code in embedded in a function, but the problem is the same:
> > >
> > > Transaction control is not possible in functions. Only in procedures (CALL)
> > > and DO blocks.
> >
> > Then explain why the original savepoint command wasn’t a syntax, or runtime,
> > error?
>
> I don't need to because CREATE {FUNCTION | PROCEDURE} already fails because of
> ROLLBACK TO SAVEPOINT. And without a function to execute there can't be any
> runtime error because of SAVEPOINT. My point was about transaction control in
> plpgsql in general.
You can create the function nonetheless if you set check_function_bodies
to OFF beforehand. Then it's a "syntax" error detected at runtime.
55432 17devel 1274266=# create function quarrel() returns void language plpgsql as $$ begin savepoint foo; insert into
foodefault values; rollback to savepoint foo; end $$;
ERROR: syntax error at or near "to"
LINE 1: ...int foo; insert into foo default values; rollback to savepoi...
^
55432 17devel 1274266=# set check_function_bodies to 0;
SET
55432 17devel 1274266=# create function quarrel() returns void language plpgsql as $$ begin savepoint foo; insert into
foodefault values; rollback to savepoint foo; end $$;
CREATE FUNCTION
55432 17devel 1274266=# select quarrel();
ERROR: syntax error at or near "to"
LINE 1: ...int foo; insert into foo default values; rollback to savepoi...
^
QUERY: begin savepoint foo; insert into foo default values; rollback to savepoint foo; end
CONTEXT: compilation of PL/pgSQL function "quarrel" near line 1
But it gets worse. If you create a procedure (no longer a function)
with a SAVEPOINT and ROLLBACK TO SAVEPOINT, it'll complain about the
ROLLBACK TO command, but not about SAVEPOINT; and if you remove that,
then it'll fail at runtime saying that SAVEPOINT is unsupported.
So it's not super consistent about how it report these various problems.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"El sudor es la mejor cura para un pensamiento enfermo" (Bardia)
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> But it gets worse. If you create a procedure (no longer a function)
> with a SAVEPOINT and ROLLBACK TO SAVEPOINT, it'll complain about the
> ROLLBACK TO command, but not about SAVEPOINT; and if you remove that,
> then it'll fail at runtime saying that SAVEPOINT is unsupported.
ROLLBACK in plpgsql is a different command with different syntax
options than the core grammar has. So this is a matter of when
syntax errors are detected vs. when semantic errors are detected.
regards, tom lane