pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
rollback to savepoint issue
10+ messages / 7 participants
[nested] [flat]

* rollback to savepoint issue
@ 2023-09-04 09:51 Lorusso Domenico <domenico.l76@gmail.com>
  2023-09-04 12:45 ` Re: rollback to savepoint issue Erik Wienhold <ewie@ewie.name>
  0 siblings, 1 reply; 10+ messages in thread

From: Lorusso Domenico @ 2023-09-04 09:51 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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


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$;


I know I can modify the code, but I wish to understand why I can't use
rollback to save point

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: rollback to savepoint issue
  2023-09-04 09:51 rollback to savepoint issue Lorusso Domenico <domenico.l76@gmail.com>
@ 2023-09-04 12:45 ` Erik Wienhold <ewie@ewie.name>
  2023-09-04 14:22   ` Re: rollback to savepoint issue Dominique Devienne <ddevienne@gmail.com>
  2023-09-04 14:56   ` Re: rollback to savepoint issue David G. Johnston <david.g.johnston@gmail.com>
  0 siblings, 2 replies; 10+ messages in thread

From: Erik Wienhold @ 2023-09-04 12:45 UTC (permalink / raw)
  To: Lorusso Domenico <domenico.l76@gmail.com>; pgsql-general@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: rollback to savepoint issue
  2023-09-04 09:51 rollback to savepoint issue Lorusso Domenico <domenico.l76@gmail.com>
  2023-09-04 12:45 ` Re: rollback to savepoint issue Erik Wienhold <ewie@ewie.name>
@ 2023-09-04 14:22   ` Dominique Devienne <ddevienne@gmail.com>
  2023-09-04 14:32     ` Re: rollback to savepoint issue Dominique Devienne <ddevienne@gmail.com>
  2023-09-04 14:58     ` Re: rollback to savepoint issue Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 2 replies; 10+ messages in thread

From: Dominique Devienne @ 2023-09-04 14:22 UTC (permalink / raw)
  To: Erik Wienhold <ewie@ewie.name>; +Cc: Lorusso Domenico <domenico.l76@gmail.com>; pgsql-general@lists.postgresql.org

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

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: rollback to savepoint issue
  2023-09-04 09:51 rollback to savepoint issue Lorusso Domenico <domenico.l76@gmail.com>
  2023-09-04 12:45 ` Re: rollback to savepoint issue Erik Wienhold <ewie@ewie.name>
  2023-09-04 14:22   ` Re: rollback to savepoint issue Dominique Devienne <ddevienne@gmail.com>
@ 2023-09-04 14:32     ` Dominique Devienne <ddevienne@gmail.com>
  1 sibling, 0 replies; 10+ messages in thread

From: Dominique Devienne @ 2023-09-04 14:32 UTC (permalink / raw)
  To: Erik Wienhold <ewie@ewie.name>; +Cc: Lorusso Domenico <domenico.l76@gmail.com>; pgsql-general@lists.postgresql.org

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-EXCEPTIONS
>
>
> Hi 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

[1]:
https://www.cybertec-postgresql.com/en/subtransactions-and-performance-in-postgresql/

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: rollback to savepoint issue
  2023-09-04 09:51 rollback to savepoint issue Lorusso Domenico <domenico.l76@gmail.com>
  2023-09-04 12:45 ` Re: rollback to savepoint issue Erik Wienhold <ewie@ewie.name>
  2023-09-04 14:22   ` Re: rollback to savepoint issue Dominique Devienne <ddevienne@gmail.com>
@ 2023-09-04 14:58     ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 10+ messages in thread

From: Tom Lane @ 2023-09-04 14:58 UTC (permalink / raw)
  To: Dominique Devienne <ddevienne@gmail.com>; +Cc: Erik Wienhold <ewie@ewie.name>; Lorusso Domenico <domenico.l76@gmail.com>; pgsql-general@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: rollback to savepoint issue
  2023-09-04 09:51 rollback to savepoint issue Lorusso Domenico <domenico.l76@gmail.com>
  2023-09-04 12:45 ` Re: rollback to savepoint issue Erik Wienhold <ewie@ewie.name>
@ 2023-09-04 14:56   ` David G. Johnston <david.g.johnston@gmail.com>
  2023-09-04 15:12     ` Re: rollback to savepoint issue Adrian Klaver <adrian.klaver@aklaver.com>
  2023-09-04 15:22     ` Re: rollback to savepoint issue Erik Wienhold <ewie@ewie.name>
  1 sibling, 2 replies; 10+ messages in thread

From: David G. Johnston @ 2023-09-04 14:56 UTC (permalink / raw)
  To: Erik Wienhold <ewie@ewie.name>; +Cc: Lorusso Domenico <domenico.l76@gmail.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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.

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: rollback to savepoint issue
  2023-09-04 09:51 rollback to savepoint issue Lorusso Domenico <domenico.l76@gmail.com>
  2023-09-04 12:45 ` Re: rollback to savepoint issue Erik Wienhold <ewie@ewie.name>
  2023-09-04 14:56   ` Re: rollback to savepoint issue David G. Johnston <david.g.johnston@gmail.com>
@ 2023-09-04 15:12     ` Adrian Klaver <adrian.klaver@aklaver.com>
  1 sibling, 0 replies; 10+ messages in thread

From: Adrian Klaver @ 2023-09-04 15:12 UTC (permalink / raw)
  To: David G. Johnston <david.g.johnston@gmail.com>; Erik Wienhold <ewie@ewie.name>; +Cc: Lorusso Domenico <domenico.l76@gmail.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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






^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: rollback to savepoint issue
  2023-09-04 09:51 rollback to savepoint issue Lorusso Domenico <domenico.l76@gmail.com>
  2023-09-04 12:45 ` Re: rollback to savepoint issue Erik Wienhold <ewie@ewie.name>
  2023-09-04 14:56   ` Re: rollback to savepoint issue David G. Johnston <david.g.johnston@gmail.com>
@ 2023-09-04 15:22     ` Erik Wienhold <ewie@ewie.name>
  2023-09-05 12:01       ` Re: rollback to savepoint issue Alvaro Herrera <alvherre@alvh.no-ip.org>
  1 sibling, 1 reply; 10+ messages in thread

From: Erik Wienhold @ 2023-09-04 15:22 UTC (permalink / raw)
  To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: rollback to savepoint issue
  2023-09-04 09:51 rollback to savepoint issue Lorusso Domenico <domenico.l76@gmail.com>
  2023-09-04 12:45 ` Re: rollback to savepoint issue Erik Wienhold <ewie@ewie.name>
  2023-09-04 14:56   ` Re: rollback to savepoint issue David G. Johnston <david.g.johnston@gmail.com>
  2023-09-04 15:22     ` Re: rollback to savepoint issue Erik Wienhold <ewie@ewie.name>
@ 2023-09-05 12:01       ` Alvaro Herrera <alvherre@alvh.no-ip.org>
  2023-09-05 13:19         ` Re: rollback to savepoint issue Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 10+ messages in thread

From: Alvaro Herrera @ 2023-09-05 12:01 UTC (permalink / raw)
  To: Erik Wienhold <ewie@ewie.name>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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 foo default 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 foo default 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)





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: rollback to savepoint issue
  2023-09-04 09:51 rollback to savepoint issue Lorusso Domenico <domenico.l76@gmail.com>
  2023-09-04 12:45 ` Re: rollback to savepoint issue Erik Wienhold <ewie@ewie.name>
  2023-09-04 14:56   ` Re: rollback to savepoint issue David G. Johnston <david.g.johnston@gmail.com>
  2023-09-04 15:22     ` Re: rollback to savepoint issue Erik Wienhold <ewie@ewie.name>
  2023-09-05 12:01       ` Re: rollback to savepoint issue Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2023-09-05 13:19         ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 10+ messages in thread

From: Tom Lane @ 2023-09-05 13:19 UTC (permalink / raw)
  To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Erik Wienhold <ewie@ewie.name>; David G. Johnston <david.g.johnston@gmail.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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





^ permalink  raw  reply  [nested|flat] 10+ messages in thread


end of thread, other threads:[~2023-09-05 13:19 UTC | newest]

Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-09-04 09:51 rollback to savepoint issue Lorusso Domenico <domenico.l76@gmail.com>
2023-09-04 12:45 ` Erik Wienhold <ewie@ewie.name>
2023-09-04 14:22   ` Dominique Devienne <ddevienne@gmail.com>
2023-09-04 14:32     ` Dominique Devienne <ddevienne@gmail.com>
2023-09-04 14:58     ` Tom Lane <tgl@sss.pgh.pa.us>
2023-09-04 14:56   ` David G. Johnston <david.g.johnston@gmail.com>
2023-09-04 15:12     ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-09-04 15:22     ` Erik Wienhold <ewie@ewie.name>
2023-09-05 12:01       ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-09-05 13:19         ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox