sql: pg mandates multi-stmt batches to execute as single txn

[knz]

As per pg's docs: https://www.postgresql.org/docs/12/protocol-flow.html#PROTOCOL-FLOW-MULTI-STATEMENT

When a simple Query message contains more than one SQL statement (separated by semicolons), those statements are executed as a single transaction, unless explicit transaction control commands are included to force a different behavior. For example, if the message contains

INSERT INTO mytable VALUES(1);
SELECT 1/0;
INSERT INTO mytable VALUES(2);

then the divide-by-zero failure in the SELECT will force rollback of the first INSERT. Furthermore, because execution of the message is abandoned at the first error, the second INSERT is never attempted at all.

If a pg client that expects this behavior is ran against crdb, this will yield inconsistent data without any clear signal to the client.

(The current crdb behavior is a plain ACID violation when viewed through the lens of pg semantics.)

Epic CRDB-8948

Jira issue: CRDB-5201

[andreimatei]

I think we simply didn't know about this, since it appears to only be documented starting in pg 11. But I've verified down to pg 9.4, and their behavior seems to have stayed consistent.

[knz]

I agree with "we didn't know" but it seems egregious given the amount of time we routinely spend comparing behaviors. I am very surprised we didn't notice earlier.

The reason, I think, is that when an error happens the messages sent back and forth are the same in both cases. So there's no visual hint. We probably never checked the side effects in the database.

My argument at the top remains though. If client apps rely on transactionality, we're failing them big time now, and in no way for them to notice until their data is in a bad state.

[rafiss]

Leaving some notes in here about how Postgres implements this. In the exec_simple_query function of src/backend/tcop/postgres.c:

• If there are multiple statements in the query, set use_implicit_block to true.
• For each statement in the query:
  • Unconditionally call start_xact_command(), which starts a transaction if one is not in progress already.
    • Enter the TBLOCK_STARTED state, or else remain in the TBLOCK_INPROGRESS or TBLOCK_IMPLICIT_INPROGRESS states.
    • Other cases are omitted for brevity.
  • If use_implicit_block is true, call BeginImplicitTransactionBlock(). This moves from the TBLOCK_STARTED state to the TBLOCK_IMPLICIT_INPROGRESS state. (No effect if the current state is anything else.)
  • Do other stuff.
  • If this is the last statement in the query:
    • If use_implicit_block is true, call EndImplicitTransactionBlock(). This moves from the TBLOCK_IMPLICIT_INPROGRESS state to the TBLOCK_STARTED state. (No effect if the current state is anything else.)
    • Unconditionally call finish_xact_command(). (This calls CommitTransaction() if the current state is TBLOCK_STARTED. If the current state is TBLOCK_INPROGRESS or TBLOCK_IMPLICIT_INPROGRESS it just increments the command counter.)
  • Else if the current statement is a TransactionStmt, call finish_xact_command(). (This calls CommitTransaction() if the current state is TBLOCK_END.)

We should be able to do something similar in our own state machine.

NB: I skipped over some edge cases above (which are also described in the Postgres docs). For example, if a BEGIN is issued from inside an implicit transaction block, then the state switches to TBLOCK_INPROGRESS. As the docs say, "If the BEGIN follows some statements that were executed as an implicit transaction block, those statements are not immediately committed; in effect, they are retroactively included into the new regular transaction block."