Subject: pipeline mode and commands not allowed in a transaction block

From: Yugo NAGATA <[email-masked]>
Date: Tue, 1 Mar 2022 15:17:04 +0900

Hi,

I found that when we use pipeline mode, we can execute commands
which is not allowed in a transaction block, for example
CREATE DATABASE, in the same transaction with other commands.

In extended query protocol, a transaction starts when Parse, 
Bind, Executor, or Describe message is received, and is closed
when Sync message is received if COMMIT, ROLLBACK, or END is not
sent. In a pipeline mode, Sync message is sent at the end of the
pipeline instead of for each query. Therefore, multiple queries
can be in the same transaction without using an explicit
transaction block.

It is similar to implicit transaction block which starts when
multiple statements are sent in simple query protocol, but the
server doesn't regard it as an implicit transaction block. 
Therefore, problems that would not occur in implicit  transactions
could occur in transactions started in a pipeline mode.

For example, CREATE DATABASE  or DROP DATABASE can be executed
in the same transaction with other commands, and when the
transaction fails, this causes an inconsistency between the
system catalog and base directory. 

Do you think we should prevent such problems from server side? or, 
it is user's responsible to avoid such problematic use of pipeline
or protocol messages?

If we want to handle it from server side, I think a few ideas:

1. 
If the server receive more than one Execute messages before
receiving Sync, start an implicit transaction block. If the first
Execute message is for a command not allowed in a transaction
(CREATE DATABASE etc.), explicitly close the transaction after the
command not to share the transaction with other commands.

2.
When a pipeline start by calling PQenterPipelineMode in libpq, 
start an implicit transaction at the server. For this purpose, we
would need to add a new message to signal the start of pipeline mode
to the protocol. It is user responsible to avoid the problematic
protocol use when libpq is not used.

What do you think about it?

Regards,
Yugo Nagata

-- 
Yugo NAGATA <[email-masked]>