Horton

Re: BUG #17434: CREATE/DROP DATABASE can be executed in the same transaction with other commands

David G. Johnston <david.g.johnston@gmail.com>

View thread View on postgr.es
From: "David G. Johnston" <david.g.johnston@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Yugo NAGATA <nagata@sraoss.co.jp>, Bruce Momjian <bruce@momjian.us>, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Date: 2022-07-28T16:13:05Z
Lists: pgsql-bugs, pgsql-hackers 
On Wed, Jul 27, 2022 at 7:50 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Yugo NAGATA <nagata@sraoss.co.jp> writes:
> > I've looked at the commited fix. What I wonder is whether a change in
> > IsInTransactionBlock() is necessary or not.
>
> > In fact, the result of IsInTransactionBlock does not make senses at
> > all in pipe-line mode regardless to the fix. ANALYZE could commit all
> > previous commands in pipelining, and this may not be user expected
> > behaviour.
>
> This seems pretty much isomorphic to the fact that CREATE DATABASE
> will commit preceding steps in the pipeline.  That's not great,
> I admit; we'd not have designed it like that if we'd had complete
> understanding of the behavior at the beginning.  But it's acted
> like that for a couple of decades now, so changing it seems far
> more likely to make people unhappy than happy.  The same for
> ANALYZE in a pipeline.
>
>
I agreed to leaving the description of CREATE DATABASE simplified by not
introducing the idea of implicit transactions or, equivalently,
"autocommit".

Just tossing out there that we should acknowledge that our wording in the
BEGIN Reference should remain status quo based upon the same reasoning.

"By default (without BEGIN), PostgreSQL executes transactions in
“autocommit” mode, that is, each statement is executed in its own
transaction and a commit is implicitly performed at the end of the
statement (if execution was successful, otherwise a rollback is done)."

https://www.postgresql.org/docs/current/sql-begin.html

Maybe write instead:

"By default (without BEGIN), PostgreSQL creates transactions based upon the
underlying messages passed between the client and server.  Typically this
means each statement ends up having its own transaction.  In any case,
statements that must not execute in a transaction (like CREATE DATABASE)
must use the default, and will always cause a commit or rollback to happen
upon completion."

It feels a bit out-of-place, maybe if the content scope is acceptable we
can work it better into the Tutorial-Advanced Features-Transaction section
and just replace the existing sentence with a link to there?

David J.

Commits

  1. Rethink handling of [Prevent|Is]InTransactionBlock in pipeline mode.

  2. Doc: add comments about PreventInTransactionBlock/IsInTransactionBlock.

  3. Force immediate commit after CREATE DATABASE etc in extended protocol.