Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iF0wH-00046Y-5P for pgsql-docs@arkaria.postgresql.org; Mon, 30 Sep 2019 19:02:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iF0wF-0005Lm-Sg for pgsql-docs@arkaria.postgresql.org; Mon, 30 Sep 2019 19:02:27 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iF0wF-0005LT-DH for pgsql-docs@lists.postgresql.org; Mon, 30 Sep 2019 19:02:27 +0000 Received: from mail-pf1-x444.google.com ([2607:f8b0:4864:20::444]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1iF0wC-0003zG-Ja for pgsql-docs@lists.postgresql.org; Mon, 30 Sep 2019 19:02:26 +0000 Received: by mail-pf1-x444.google.com with SMTP id q21so6106953pfn.11 for ; Mon, 30 Sep 2019 12:02:24 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yugabyte.com; s=google; h=from:message-id:mime-version:subject:date:in-reply-to:cc:to :references; bh=gC9XVvfPIht73t8Wv7UQja6LpGCUI8nXEZVrqGDcTMw=; b=a4QlzZqjasNHtVShgjRJ0s07X121aak11yIAe70pHYZJKK+dUIQXRMo7f0THPZLk91 pQTa2BwA5T7KWgBUicAyCwr2Dp5QtocStPfYiyMt/1aXTQETlK505m881ICtYKI1Ozii HffNxSsg+Xoo6eEv9MVIsaY+N/rJATh3sEvMo= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:message-id:mime-version:subject:date :in-reply-to:cc:to:references; bh=gC9XVvfPIht73t8Wv7UQja6LpGCUI8nXEZVrqGDcTMw=; b=CeWd2jqDhb1xNRsl/U0DlQ53PDnvKEZMgldCkhdetlIwA53x6WNxn1Obc/nORQAsR5 WK69c/vMgZFHLPcUuMD6lgJupYTHX7JGe5010CIlWyd8Cuz3ceRB2me6jnDn62IXw4r/ 02nZHp+6JfU9dOMz50TBEvRJviLBxwQq8c/yqFn+gWY4IaawaHEFl58ljj1HqTkoX8nI tXShCX9lXUQ+kS5LFck034290p3GK6Cisw28ngA3FYokjQLooXvJVNKj1eGk02DlrY4c fqL/i+3jcg92KUp/pj29CdYJiihCgjhNPLJmTWAb/FF3m/SUbpbRfdNIIHJtc66jQD5b SAQA== X-Gm-Message-State: APjAAAUiSIZVSUrbAtckkInqUGVtSdQUAEM3Yygfcf//8KMjFOPt2tPN z/rEk4hykQOHUp5oZpSx3poN8w== X-Google-Smtp-Source: APXvYqybGGJwycwo6nwchIlRwsPvx2TwI0tEcnnyTjAQCvjDO97oVy/C9EtwVN+s44yR+rv9D6Scnw== X-Received: by 2002:a63:3585:: with SMTP id c127mr25052346pga.93.1569870143375; Mon, 30 Sep 2019 12:02:23 -0700 (PDT) Received: from [172.27.71.125] ([12.33.220.138]) by smtp.gmail.com with ESMTPSA id v19sm13966953pff.46.2019.09.30.12.02.22 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Mon, 30 Sep 2019 12:02:22 -0700 (PDT) From: Bryn Llewellyn Message-Id: <07A18A12-FEA3-40AD-A72B-B52BCEAC195E@yugabyte.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_21C1F1BD-824A-4E3E-9819-CCF9D8576F5F" Mime-Version: 1.0 (Mac OS X Mail 12.4 \(3445.104.11\)) Subject: Re: Chapter 43.8. "Transaction Management" fails to state two critical restrictions Date: Mon, 30 Sep 2019 12:02:21 -0700 In-Reply-To: <20190930142427.GA8385@momjian.us> Cc: pgsql-docs@lists.postgresql.org To: Bruce Momjian References: <156981670710.26184.2375673917366850651@wrigleys.postgresql.org> <20190930142427.GA8385@momjian.us> X-Mailer: Apple Mail (2.3445.104.11) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_21C1F1BD-824A-4E3E-9819-CCF9D8576F5F Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Hello, Bruce. (We met at the OUGN conference last March on the = Oslo-Kiel ferry.) Thanks for your reply. 1. About AUTOCOMMIT It=E2=80=99s very hard to get a clear account of what AUTOCOMMIT really = is. So consider the example from the docs section that I cited, and run = these psql commands at its prompt: DROP TABLE IF EXISTS test1; CREATE TABLE test1(a int); CREATE OR REPLACE PROCEDURE transaction_test1() LANGUAGE plpgsql AS $$ BEGIN FOR i IN 0..9 LOOP INSERT INTO test1 (a) VALUES (i); IF i % 2 =3D 0 THEN COMMIT; ELSE ROLLBACK; END IF; END LOOP; END $$; Now invoke it like this: \set AUTOCOMMIT on CALL transaction_test1(); And see what it produced like this: SELECT a FROM test1 ORDER BY a;=20 The result is what I expect (even values of =E2=80=9Ca=E2=80=9D from 0 = through 8). Now re-test like this \set AUTOCOMMIT on DELETE FROM test1; \set AUTOCOMMIT off CALL transaction_test1(); It causes this error: ERROR: invalid transaction termination CONTEXT: PL/pgSQL function transaction_test1() line 6 at COMMIT And (of course), following the ROLLBACK that you must do before any new = SQL works, table test1 is left empty. You get the identical outcome (as = I=E2=80=99d expect) if you execute this: START TRANSACTION; between setting AUTOCOMMIT to off and calling the proc. I work at YugaByte (www.yugabyte.com) and I filed this issue on = 1-Aug-2019: https://github.com/yugabyte/yugabyte-db/issues/1957 You can see from what I wrote how confused I was. (My earlier life was = at Oracle Corp where the corresponding notions are simple and = intuitive.) Then I sent the URL to the pgsql-general list asking for = comments. I got all sorts of replies=E2=80=94mainly trying to tell me = that my aim (ultimately to write a PL/pgSQL proc to encapsulate the = retry loop that you need when you execute a txn at the serializable = level) was silly. (The point there is that a serialization error = sometimes occurs first on attempting to commit the txn.) There were some = exchanges on Twitter. And eventually Peter Eisentraut tweeted to say = that the AUTOCOMMIT requirement was probably an implementation = restriction. 2. About committing in a block statement=E2=80=99s executable section = when the block statement has an exception section Simply modify the proc from the docs thus: CREATE OR REPLACE PROCEDURE transaction_test1() LANGUAGE plpgsql AS $$ BEGIN FOR i IN 0..9 LOOP IF i =3D 9 THEN RAISE EXCEPTION 'My bad' USING errcode =3D '99999'; END IF; INSERT INTO test1 (a) VALUES (i); IF i % 2 =3D 0 THEN COMMIT; ELSE ROLLBACK; END IF; END LOOP; EXCEPTION WHEN OTHERS THEN RAISE INFO 'sqlerrm: %', sqlerrm; RAISE INFO 'sqlstate: %', sqlstate; END $$; With proper semantics, it ought to commit the even values of =E2=80=9Ca=E2= =80=9D from 0 through 8, then report =E2=80=9CMy bad=E2=80=9D using = RAISE INFO, and then end silently. And invoke it (as we now know we must) like this: \set AUTOCOMMIT on CALL transaction_test1(); It actually reports this: INFO: sqlerrm: cannot commit while a subtransaction is active INFO: sqlstate: 2D000 In other words, executing =E2=80=9Ccommit=E2=80=9D in the circumstances = that I complained about causes the error that my OTHERS handler reports. This undocumented behavior means that stored procedures in PostgreSQL = simply cannot, in general, be used for their intended purpose. I just = filed this issue (on 29-Sep-2019): https://github.com/yugabyte/yugabyte-db/issues/2464 = I indent as write to the pgsql-hackers list, tell them this URL, and ask = for comments. Obviously, I=E2=80=99ll be delighted to be shown that my pessimistic = analysis is faulty. My aim is simply to implement my use cases. 3. Back to AUTOCOMMIT in general It seems to me that the notion is a conflation of what could have been a = pure client-side mode with a server-side mode. Apparently, different = client-side drivers do different things when AUTOCOMMIT is off (like = silently issuing a BEGIN or START TRANSACTION on your behalf before = submitting your intended SQL statement). But something must also be = going on server side=E2=80=94else the execution of a stored proc could = not know whether the client code did its own BEGIN or if this was done = implicitly. I=E2=80=99ve been told that the server distinguishes between = a so-called explicit txn and and implicit txn. I=E2=80=99d be happy if = someone would explain this clearly to me. On 30-Sep-2019, at 07:24, Bruce Momjian wrote: On Mon, Sep 30, 2019 at 04:11:47AM +0000, PG Doc comments form wrote: > The following documentation comment has been logged on the website: >=20 > Page: https://www.postgresql.org/docs/11/plpgsql-transactions.html > Description: >=20 > This chapter fails to state: >=20 > (1) If a PL/pgSQL procedure issues "commit" then it must be called = with > AUTOCOMMIT set to On. This is counter-intuitive. You'd expect the > opposite. You mean psql autocommit mode? > (2) If a PL/pgSQL procedure has a block statement with an exception = section > (this might be the procedure's defining block, or an inner block) then = that > block's executabe section must not issue "commit". Doing so causes a > run-time error. Uh, that's a good point since you are in a subtransaction at that point. What error do you get? --=20 Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + --Apple-Mail=_21C1F1BD-824A-4E3E-9819-CCF9D8576F5F Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 Hello, Bruce.  (We met at the OUGN conference last = March on the Oslo-Kiel ferry.) Thanks for your reply.

1. About AUTOCOMMIT

It=E2=80=99s very hard to get a clear = account of what AUTOCOMMIT really is. So consider the example from = the docs section that I cited, and run these psql commands at its = prompt:

DROP TABLE IF EXISTS test1;
CREATE TABLE test1(a int);

CREATE = OR REPLACE PROCEDURE transaction_test1()
LANGUAGE = plpgsql
AS $$
BEGIN
  =   FOR i IN 0..9 LOOP
      =   INSERT INTO test1 (a) VALUES (i);
  =       IF i % 2 =3D 0 THEN
  =           COMMIT;
  =       ELSE
        =     ROLLBACK;
      =   END IF;
    END LOOP;
END
$$;

Now invoke = it like this:

\set AUTOCOMMIT on
CALL transaction_test1();

And = see what it produced like this:

SELECT a = FROM test1 ORDER BY a; 

The result is = what I expect (even values of =E2=80=9Ca=E2=80=9D from 0 through 8). = Now re-test like this

\set AUTOCOMMIT = on
DELETE FROM test1;

\set = AUTOCOMMIT off
CALL transaction_test1();

It causes this error:

ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function transaction_test1() = line 6 at COMMIT

And (of course), = following the ROLLBACK that you must do before any new SQL works, = table test1 is left empty. You get the identical outcome (as I=E2=80=99= d expect) if you execute this:

START = TRANSACTION;

between setting AUTOCOMMIT to = off and calling the proc.

I work at = YugaByte (www.yugabyte.com) and I filed this issue on = 1-Aug-2019:

https://github.com/yugabyte/yugabyte-db/issues/1957

You can see from what I wrote how confused I = was. (My earlier life was at Oracle Corp where the corresponding = notions are simple and intuitive.) Then I sent the URL to the = pgsql-general list asking for comments. I got all sorts of = replies=E2=80=94mainly trying to tell me that my aim (ultimately to = write a PL/pgSQL proc to encapsulate the retry loop that you need = when you execute a txn at the serializable level) was silly. (The = point there is that a serialization error sometimes occurs first = on attempting to commit the txn.) There were some exchanges on = Twitter. And eventually Peter Eisentraut tweeted to say that = the AUTOCOMMIT requirement was probably an = implementation restriction.

2. About committing in a block statement=E2=80=99s executable = section when the block statement has an exception section

Simply modify the proc from the docs thus:

CREATE OR REPLACE PROCEDURE = transaction_test1()
LANGUAGE plpgsql
AS = $$
BEGIN
    FOR i IN 0..9 = LOOP
        IF i =3D 9 THEN
          RAISE EXCEPTION 'My = bad' USING errcode =3D '99999';
      =   END IF;

      =   INSERT INTO test1 (a) VALUES (i);
  =       IF i % 2 =3D 0 THEN
  =           COMMIT;
  =       ELSE
        =     ROLLBACK;
      =   END IF;
    END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    RAISE INFO 'sqlerrm:  %', = sqlerrm;
    RAISE INFO 'sqlstate: %', = sqlstate;
END
$$;

With proper semantics, it ought to commit the even values of = =E2=80=9Ca=E2=80=9D from 0 through 8, then report =E2=80=9CMy = bad=E2=80=9D using RAISE INFO, and then end silently.

And invoke it (as we now know we must) like = this:

\set AUTOCOMMIT on
CALL = transaction_test1();

It actually reports = this:

INFO:  sqlerrm:  cannot commit while a = subtransaction is active
INFO:  sqlstate: = 2D000

In other words, executing = =E2=80=9Ccommit=E2=80=9D in the circumstances that I complained = about causes the error that my OTHERS handler reports.

This undocumented behavior means that stored procedures = in PostgreSQL simply cannot, in general, be used for their = intended purpose. I just filed this issue (on 29-Sep-2019):

https://github.com/yugabyte/yugabyte-db/issues/2464

I indent as write to = the pgsql-hackers list, tell them this URL, and ask for = comments.

Obviously, I=E2=80=99ll be = delighted to be shown that my pessimistic analysis is faulty. My = aim is simply to implement my use cases.

3. Back to AUTOCOMMIT in general

It seems to me that the notion is a conflation of what could = have been a pure client-side mode with a server-side mode. = Apparently, different client-side drivers do different things when = AUTOCOMMIT is off (like silently issuing a BEGIN or START = TRANSACTION on your behalf before submitting your intended SQL = statement). But something must also be going on server side=E2=80=94e= lse the execution of a stored proc could not know whether the = client code did its own BEGIN or if this was done implicitly. = I=E2=80=99ve been told that the server distinguishes between = a so-called explicit txn and and implicit txn. I=E2=80=99d be happy = if someone would explain this clearly to me.

On 30-Sep-2019, at 07:24, Bruce Momjian = <bruce@momjian.us> wrote:

On = Mon, Sep 30, 2019 at 04:11:47AM +0000, PG Doc comments form wrote:
The following = documentation comment has been logged on the website:

Page: = https://www.postgresql.org/docs/11/plpgsql-transactions.html
Description:

This chapter fails = to state:

(1) If a PL/pgSQL procedure = issues "commit" then it must be called with
AUTOCOMMIT set = to On. This is counter-intuitive. You'd expect the
opposite.

You mean = psql autocommit mode?

(2) If a PL/pgSQL procedure has a block = statement with an exception section
(this might be the = procedure's defining block, or an inner block) then that
block's executabe section must not issue "commit". Doing so = causes a
run-time error.

Uh, that's a good point since you are in a subtransaction at = that point.
What error do you get?

--
 Bruce Momjian =  <bruce@momjian.us> =        http://momjian.us
=  EnterpriseDB =             &n= bsp;           &nbs= p;   http://enterprisedb.com

+= As you are, so once was I.  As I am, so you will be. +
+ =             &n= bsp;        Ancient Roman grave = inscription +

= --Apple-Mail=_21C1F1BD-824A-4E3E-9819-CCF9D8576F5F--