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: Bruce Momjian <bruce@momjian.us>, Yugo Nagata <nagata@sraoss.co.jp>, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Date: 2022-07-26T15:48:23Z
Lists: pgsql-bugs, pgsql-hackers 
On Tue, Jul 26, 2022 at 8:37 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > Thanks!  This added section is clear and now affirms the understanding
> I've
> > come to with this thread, mostly.  I'm still of the opinion that the
> > definition of "cannot be executed inside a transaction block" means that
> we
> > must "auto-sync" (implicit commit) before and after the restricted
> command,
> > not just after, and that the new section should cover this - whether we
> do
> > or do not - explicitly.
>
> I'm not excited about your proposal to auto-commit before starting
> the command.  In the first place, we can't: we do not know whether
> the command will call PreventInTransactionBlock.  Restructuring to
> change that seems untenable in view of past cowboy decisions about
> use of PreventInTransactionBlock in the replication logic.  In the
> second place, it'd be a deviation from the current behavior (namely
> that a failure in CREATE DATABASE et al rolls back previous un-synced
> commands) that is not necessary to fix a bug, so changing that in
> the back branches would be a hard sell.  I don't even agree that
> it's obviously better than the current behavior, so I'm not much
> on board with changing it in HEAD either.
>
>
That leaves us with changing the documentation then, from:

CREATE DATABASE cannot be executed inside a transaction block.

to:

CREATE DATABASE cannot be executed inside an explicit transaction block (it
will error in this case), and will commit (or rollback on failure) any
implicit transaction it is a part of.

The content of the section you added works fine so long as we are clear
regarding the fact it can be executed in a transaction so long as it is
implicit.

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.