Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qdATz-002PuV-TD for pgsql-general@arkaria.postgresql.org; Mon, 04 Sep 2023 14:23:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qdATy-00BiE3-6t for pgsql-general@arkaria.postgresql.org; Mon, 04 Sep 2023 14:23:13 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qdATx-00BiDf-Qw for pgsql-general@lists.postgresql.org; Mon, 04 Sep 2023 14:23:13 +0000 Received: from mail-oo1-xc35.google.com ([2607:f8b0:4864:20::c35]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qdATq-002h9L-25 for pgsql-general@lists.postgresql.org; Mon, 04 Sep 2023 14:23:12 +0000 Received: by mail-oo1-xc35.google.com with SMTP id 006d021491bc7-57358a689d2so1088420eaf.2 for ; Mon, 04 Sep 2023 07:23:05 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1693837385; x=1694442185; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=cfG1YyOS0J5VN0JdKuOlr8GNenp7YTU5qeWhMrBTwBM=; b=Yw/h6rsNoo+M/H+cYVer+5GZb6tN7DU3YxgZ1DMyLaHVTU5Dh3Ku/MvhmHoq82vdXz vzNbE65mRaowuaia17itco6kfZoXIIJ4+f6w3pLQJLk3PX/Bok5O8se4FZdfdVPaPs5d hTRPaggd0/QN0UO0osdyJQc0IjNUiCEgQ6GIS9BsQEExcoLiNoTvbGW9zqH3F6KReBEH mrxZlRqhau5GAe+n4j2YBbc7bIZTJ1igLiqWr+w41krFJ+WS+oPL49FVwlHDUfgqFTIa 2fCNmhLiFM4Uv0h9YHwraolPh/V4vsNTsOSatK54XVOanXDAYoT5s3jawQn04rJJiVQU Bm6w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1693837385; x=1694442185; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=cfG1YyOS0J5VN0JdKuOlr8GNenp7YTU5qeWhMrBTwBM=; b=CxWW/ACdopyG2v1/KzeGk8e7jLtdvUYPgPFKrtTCH3b1K+vrBxPPO6p1RH/C/9zQhC RS5uxzY+JAyJ24+vZOxJVWo4iZz2zjCZEksYlCqfosPuFdU702qF8Oq9cYHe22hRE7fD 4aSHh13nN4aqrJJjzO1OSS0L0H07aojqjRSiG4C4J9XREgMFM9RpTyZnc97HB9uMPsUL FEXVp8flvF7OkeyyPU0g2p29RW6GUJH+/vc/bzc24WuZX7TDdd/o38vT42e4vYzHeA9N tLmQ1jUzvWmftfdfB/LymZf2ej4Q5m1H4Yk/pBEuyQdnGngg9eSoNknKDJmEttrVG39l VbZg== X-Gm-Message-State: AOJu0YzCOEJxKCJgjnyVpsWgl3Q3mu2zXbSfdV0kPIUgW7DLUlBF20Fo xdzQME2LX8pmeadXCQZrZ+JOInPZ0cHzg3Umjys= X-Google-Smtp-Source: AGHT+IEV5kt2npDj/17QvZl1oioY9oN/l37irX3WGYZHIKXa5fHUGD6GrkI4fT8lXbh3Qvn1LJr0sc6y8ydmolMYVjU= X-Received: by 2002:a05:6820:284:b0:573:397d:84f7 with SMTP id q4-20020a056820028400b00573397d84f7mr9129697ood.4.1693837385101; Mon, 04 Sep 2023 07:23:05 -0700 (PDT) MIME-Version: 1.0 References: <1341766181.156278.1693831501720@office.mailbox.org> In-Reply-To: <1341766181.156278.1693831501720@office.mailbox.org> From: Dominique Devienne Date: Mon, 4 Sep 2023 16:22:54 +0200 Message-ID: Subject: Re: rollback to savepoint issue To: Erik Wienhold Cc: Lorusso Domenico , pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000a575560604893f36" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000a575560604893f36 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, Sep 4, 2023 at 3:47=E2=80=AFPM Erik Wienhold wrote= : > On 04/09/2023 11:51 CEST Lorusso Domenico 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-PORT= ING-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 --000000000000a575560604893f36 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Mon, Sep 4, 2023 at 3:47=E2=80=AFPM Er= ik Wienhold <ewie@ewie.name> wr= ote:
On 04/09/2023 11:51 CEST Lorusso Domenico <domenico.l76@gmail.com&g= t; wrote:
Transaction control is not possible in functions.=C2=A0 Only i= n 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://w= ww.postgresql.org/docs/current/plpgsql-porting.html#PLPGSQL-PORTING-EXCEPTI= ONS

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 SAVEP= OINTs 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 establis= h and "move along" the savepoint,
per command. Which I = think is "expensive". So would anonymous DO blocks be a better so= lution?
If BEGIN/EXCEPTION uses an "implicit" / "h= idden" SAVEPOINT, would that still be better then?

And if we switch to anon DO+EXCEPTION blocks, is it just as easy to b= ind to the inner SQL command(s)?

Sorry if my quest= ions are a little vague. This is an area that's still fuzzy for me, I&#= 39;m not versed in pgPL/SQL.

Finally, I've rea= d "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 additio= nal "storage" must be allocated somewhere else, leading to conten= tion. Does that
apply to that BEGIN+EXCEPTION mechanism too?

Thanks for any insights. --DD
--000000000000a575560604893f36--