Hello, Bruce. (We met at the OUGN conference last March on the Oslo-Kiel
ferry.) Thanks for your reply.
1. About AUTOCOMMIT
It’s 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 = 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 “a” 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’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—mainly 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’s 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 = 9 THEN
RAISE EXCEPTION 'My bad' USING errcode = '99999';
END IF;
INSERT INTO test1 (a) VALUES (i);
IF i % 2 = 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 “a” from 0 through
8, then report “My bad” 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 “commit” 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
<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’ll 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—else the execution
of a stored proc could not know whether the client code did its own BEGIN or if
this was done implicitly. I’ve been told that the server distinguishes between
a so-called explicit txn and and implicit txn. I’d be happy if someone would
explain this clearly to me.
On 30-Sep-2019, at 07:24, Bruce Momjian <br...@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 <br...@momjian.us> 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 +
