Re: 12's AND CHAIN doesn't chain when transaction raised an error

On 2019-Aug-13, Philip Dubé wrote:

> The easiest way to see this is to BEGIN READ ONLY & then attempt an
> insert. Execute either of COMMIT AND CHAIN or ROLLBACK AND CHAIN &
> attempt the insert a second time
>
> This seems incorrect. The documentation should at least point out this
> behavior if it's intended

What do you mean with "doesn't chain"?

A simple experiment shows that "ROLLBACK AND CHAIN" in an aborted
transaction does indeed start a new transaction; so the "chain" part is
working to some extent. It is also true that if the original
transaction was READ ONLY, then the followup transaction after an error
is not READ ONLY; but if the first transaction is successful and you do
COMMIT AND CHAIN, then the second transaction *is* READ ONLY.
So there is some discrepancy here.

<commit statement> (17.7 in SQL:2016) General Rule 10) a) says
If <commit statement> contains AND CHAIN, then an SQL-transaction is
initiated. Any branch transactions of the SQL-transaction are
initiated with the same transaction access mode, transaction isolation
level, and condition area limit as the corresponding branch of the
SQL-transaction just terminated.

... which is exactly the same wording used in 17.8 <rollback statement>
General Rule 2) h) i).

(4.41.3 defines "An SQL-transaction has a transaction access mode that
is either read-only or read-write.")

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services