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 <nag...@sraoss.co.jp>