From: | Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> |
---|---|
To: | Erik Wienhold <ewie(at)ewie(dot)name> |
Cc: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: rollback to savepoint issue |
Date: | 2023-09-05 12:01:44 |
Message-ID: | 202309051201.hnghlq3bit25@alvherre.pgsql |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 2023-Sep-04, Erik Wienhold wrote:
> On 04/09/2023 16:56 CEST David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
> > On Monday, September 4, 2023, Erik Wienhold <ewie(at)ewie(dot)name> wrote:
> >
> > > On 04/09/2023 11:51 CEST Lorusso Domenico <domenico(dot)l76(at)gmail(dot)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)
From | Date | Subject | |
---|---|---|---|
Next Message | Erik Wienhold | 2023-09-05 12:22:15 | Re: ./configure doesn't make effect? |
Previous Message | jacktby jacktby | 2023-09-05 11:41:15 | ./configure doesn't make effect? |