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-26T16:11:52Z
Lists: pgsql-bugs, pgsql-hackers 
On Tue, Jul 26, 2022 at 9:03 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > 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.
>
> That's not going to help anybody unless we also provide a definition of
> "implicit transaction", which is a bit far afield for that man page.
>
> I did miss a bet in the proposed pipeline addendum, though.
> I should have written
>
>     ... However, there
>     are a few DDL commands (such as <command>CREATE DATABASE</command>)
>     that cannot be executed inside a transaction block.  If one of
>     these is executed in a pipeline, it will, upon success, force an
>     immediate commit to preserve database consistency.
>
> That ties the info to our standard wording in the per-command man
> pages.
>
>
And we are back around to the fact that only by using libpq directly, or
via the pipeline feature of pgbench, can one actually exert control over
the implicit transaction.  The psql and general SQL interface
implementation are just going to Sync after each command and so everything
looks like one transaction per command to them and only explicit
transactions matter.  From that, the adjustment you describe above is
sufficient for me.

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.