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

[Bryn Llewellyn]

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 = '9';
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 


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  wrote:

On Mon, Sep 30, 2019 at 04:11:47AM +, 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 

Re: Proposal to improve the content in subsection 8.16.6. "Composite Type Input and Output Syntax"

[Bryn Llewellyn]

Tom Lane  wrote:...It's already an example, so I don't see this as an improvementThe proposed additional text is flat-out wrong.What actually happens here is that text between quotes is consideredquoted (so that, for example, commas within it are not field separators),but that does not exclude there being other unquoted text within thesame field value.			regards, tom laneThanks, Tom. Your reply was very helpful. It prompted me to read, re-read, and then re-re-read 8.16.6 several times. I did notice that neither of “varchar” nor “char” are found in the whole of section 8.16. But I suppose that it’s obvious to the reader that “text” is to be taken as a term of art, denoting “character data types” and not as the name of one of these data types.I ran some fairly exhaustive empirical tests. Not to be mysterious, I created two “.sql” files. The zip is attached.record_literal.sql--This uses "type rt as (v text)” and "table t(k serial primary key, literal text, r rt)”.It inserts a series of text values into column “literal” as is and into column “r” typecast to “rt”.I aimed to end up with specified target values shown by “select '>'||(r).v||’<‘ from t”.Here are two example findings,First: for this specified target:>hello hello "you"