Re: Chapter 43.8. "Transaction Management" fails to state twocritical restrictions

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