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

On Thu, Jul 14, 2022 at 5:37 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Bruce Momjian <bruce@momjian.us> writes:
> > Did we make any decision on this?
>
> Hmm, that one seems to have slipped past me.  I agree it doesn't
> look good.  But why isn't the PreventInTransactionBlock() check
> blocking the command from even starting?
>
>
I assume because pgbench never sends a BEGIN command so the create database
sees itself in an implicit transaction and happily goes about its business,
expecting the system to commit its work immediately after it says it is
done.  But that never happens, instead the next command comes along and
crashes the implicit transaction it is now sharing with the create database
command.  Create database understands how to rollback if it is the one that
causes the failure but isn't designed to operate in a situation where it
has to rollback because of someone else.  That isn't how implicit
transactions are supposed to work, whether in the middle of a pipeline or
otherwise.  Or at least that is my, and apparently CREATE DATABASE's,
understanding of implicit transactions: one top-level command only.

Slight tangent, but while I'm trying to get my own head around this I just
want to point out that the first sentence of the following doesn't make
sense given the above understanding of implicit transactions, and the
paragraph as a whole is tough to comprehend.

If the pipeline used an implicit transaction, then operations that have
already executed are rolled back and operations that were queued to follow
the failed operation are skipped entirely. The same behavior holds if the
pipeline starts and commits a single explicit transaction (i.e. the first
statement is BEGIN and the last is COMMIT) except that the session remains
in an aborted transaction state at the end of the pipeline. If a pipeline
contains multiple explicit transactions, all transactions that committed
prior to the error remain committed, the currently in-progress transaction
is aborted, and all subsequent operations are skipped completely, including
subsequent transactions. If a pipeline synchronization point occurs with an
explicit transaction block in aborted state, the next pipeline will become
aborted immediately unless the next command puts the transaction in normal
mode with ROLLBACK.

https://www.postgresql.org/docs/current/libpq-pipeline-mode.html#LIBPQ-PIPELINE-USING

I don't know what the answer is here but I don't think "tell the user not
to do that" is appropriate.

David J.