pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Erik Wienhold <ewie@ewie.name>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: rollback to savepoint issue
Date: Tue, 5 Sep 2023 14:01:44 +0200
Message-ID: <202309051201.hnghlq3bit25@alvherre.pgsql> (raw)
In-Reply-To: <1165806895.176202.1693840943412@office.mailbox.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)





view thread (10+ messages)  latest in thread

Message-ID: <202309051201.hnghlq3bit25@alvherre.pgsql>
Permalink:  ../202309051201.hnghlq3bit25@alvherre.pgsql/
Also on:    postgresql.org/message-id/202309051201.hnghlq3bit25@alvherre.pgsql

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: alvherre@alvh.no-ip.org, ewie@ewie.name, david.g.johnston@gmail.com
  Subject: Re: rollback to savepoint issue
  In-Reply-To: <202309051201.hnghlq3bit25@alvherre.pgsql>
  References: <1165806895.176202.1693840943412@office.mailbox.org> <202309051201.hnghlq3bit25@alvherre.pgsql>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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