Materialize/test/pgtest/transactions.pt

# Test implicit and explicit transaction semantics.
# See "Multiple Statements in a Simple Query"
# From https://www.postgresql.org/docs/current/protocol-flow.html

# Note: all of the "SELECT 1/(SELECT 0)" things are here to produce a
# query error. We do not use SELECT 1/0 because in Postgres that doesn't
# send a RowDescription message while the former does. This is probably
# due to Postgres constant folding rules that run before its execution
# phase. This isn't important to the protocol, and both (sending or not
# sending RowDescription) are valid. Because we send a RowDescription
# on SELECT 1/0, we use the more complicated form to force Postgres to
# also send one, unifying the responses here.

# "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."
send
Query {"query": "SELECT 1; SELECT 1/(SELECT 0); SELECT 2;"}
----

# Our error codes differ, so only extract the message.
until err_field_typs=M
ReadyForQuery
----
RowDescription {"fields":[{"name":"?column?"}]}
DataRow {"fields":["1"]}
CommandComplete {"tag":"SELECT 1"}
RowDescription {"fields":[{"name":"?column?"}]}
ErrorResponse {"fields":[{"typ":"M","value":"division by zero"}]}
ReadyForQuery {"status":"I"}

# There are two transactions here, the first (explicit) succeeds and
# the second (implicitly started after the COMMIT) fails.
send
Query {"query": "BEGIN; SELECT 1; COMMIT; SELECT 1/(SELECT 0); SELECT 2"}
----

until err_field_typs=M
ReadyForQuery
----
CommandComplete {"tag":"BEGIN"}
RowDescription {"fields":[{"name":"?column?"}]}
DataRow {"fields":["1"]}
CommandComplete {"tag":"SELECT 1"}
CommandComplete {"tag":"COMMIT"}
RowDescription {"fields":[{"name":"?column?"}]}
ErrorResponse {"fields":[{"typ":"M","value":"division by zero"}]}
ReadyForQuery {"status":"I"}

# The transaction fails, so statements after it should not be executed,
# thus the ROLLBACK should not be executed in the first Query.
send
Query {"query": "BEGIN; SELECT 1/(SELECT 0); ROLLBACK;"}
Query {"query": "ROLLBACK"}
----

until err_field_typs=M
ReadyForQuery
ReadyForQuery
----
CommandComplete {"tag":"BEGIN"}
RowDescription {"fields":[{"name":"?column?"}]}
ErrorResponse {"fields":[{"typ":"M","value":"division by zero"}]}
ReadyForQuery {"status":"E"}
CommandComplete {"tag":"ROLLBACK"}
ReadyForQuery {"status":"I"}

# In implicit transactions, an error with a later ROLLBACK still doesn't execute
# anything after the error, but also doesn't need to have the next query start
# with ROLLBACK.
send
Query {"query": "SELECT 1; SELECT 1/(SELECT 0); SELECT 2; ROLLBACK; SELECT 3"}
Query {"query": "SELECT 4"}
----

until err_field_typs=M
ReadyForQuery
ReadyForQuery
----
RowDescription {"fields":[{"name":"?column?"}]}
DataRow {"fields":["1"]}
CommandComplete {"tag":"SELECT 1"}
RowDescription {"fields":[{"name":"?column?"}]}
ErrorResponse {"fields":[{"typ":"M","value":"division by zero"}]}
ReadyForQuery {"status":"I"}
RowDescription {"fields":[{"name":"?column?"}]}
DataRow {"fields":["4"]}
CommandComplete {"tag":"SELECT 1"}
ReadyForQuery {"status":"I"}

# The entire query is parsed at once, preventing any of it from running if that fails.
send
Query {"query": "BEGIN; SELECT 1; COMMIT; SELCT 1/(SELECT 0);"}
----

until err_field_typs=C
ErrorResponse
ReadyForQuery
----
ErrorResponse {"fields":[{"typ":"C","value":"42601"}]}
ReadyForQuery {"status":"I"}

# "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."
send
Query {"query": "SELECT 1; BEGIN; SELECT 2;"}
Query {"query": "COMMIT"}
----

until
ReadyForQuery
ReadyForQuery
----
RowDescription {"fields":[{"name":"?column?"}]}
DataRow {"fields":["1"]}
CommandComplete {"tag":"SELECT 1"}
CommandComplete {"tag":"BEGIN"}
RowDescription {"fields":[{"name":"?column?"}]}
DataRow {"fields":["2"]}
CommandComplete {"tag":"SELECT 1"}
ReadyForQuery {"status":"T"}
CommandComplete {"tag":"COMMIT"}
ReadyForQuery {"status":"I"}

# "If the session is already in a transaction block, as a result of a
# BEGIN in some previous message, then the Query message simply continues
# that transaction block, whether the message contains one statement or
# several. However, if the Query message contains a COMMIT or ROLLBACK
# closing the existing transaction block, then any following statements
# are executed in an implicit transaction block."

send
Query {"query": "BEGIN; SELECT 1/(SELECT 0); SELECT 1;"}
Query {"query": "SELECT 2; ROLLBACK; SELECT 3;"}
Query {"query": "ROLLBACK; SELECT 4;"}
----

until err_field_typs=M
ReadyForQuery
ReadyForQuery
ReadyForQuery
----
CommandComplete {"tag":"BEGIN"}
RowDescription {"fields":[{"name":"?column?"}]}
ErrorResponse {"fields":[{"typ":"M","value":"division by zero"}]}
ReadyForQuery {"status":"E"}
ErrorResponse {"fields":[{"typ":"M","value":"current transaction is aborted, commands ignored until end of transaction block"}]}
ReadyForQuery {"status":"E"}
CommandComplete {"tag":"ROLLBACK"}
RowDescription {"fields":[{"name":"?column?"}]}
DataRow {"fields":["4"]}
CommandComplete {"tag":"SELECT 1"}
ReadyForQuery {"status":"I"}

# "A COMMIT or ROLLBACK appearing in an implicit transaction block is
# executed as normal, closing the implicit block; however, a warning
# will be issued since a COMMIT or ROLLBACK without a previous BEGIN
# might represent a mistake. If more statements follow, a new implicit
# transaction block will be started for them."

send
Query {"query": "SELECT 1; COMMIT; SELECT 2"}
Query {"query": "SELECT 3; ROLLBACK; SELECT 4"}
Query {"query": "COMMIT;"}
Query {"query": "ROLLBACK;"}
----

until
ReadyForQuery
ReadyForQuery
ReadyForQuery
ReadyForQuery
----
RowDescription {"fields":[{"name":"?column?"}]}
DataRow {"fields":["1"]}
CommandComplete {"tag":"SELECT 1"}
NoticeResponse {"fields":[{"typ":"S","value":"WARNING"},{"typ":"C","value":"25P01"},{"typ":"M","value":"there is no transaction in progress"}]}
CommandComplete {"tag":"COMMIT"}
RowDescription {"fields":[{"name":"?column?"}]}
DataRow {"fields":["2"]}
CommandComplete {"tag":"SELECT 1"}
ReadyForQuery {"status":"I"}
RowDescription {"fields":[{"name":"?column?"}]}
DataRow {"fields":["3"]}
CommandComplete {"tag":"SELECT 1"}
NoticeResponse {"fields":[{"typ":"S","value":"WARNING"},{"typ":"C","value":"25P01"},{"typ":"M","value":"there is no transaction in progress"}]}
CommandComplete {"tag":"ROLLBACK"}
RowDescription {"fields":[{"name":"?column?"}]}
DataRow {"fields":["4"]}
CommandComplete {"tag":"SELECT 1"}
ReadyForQuery {"status":"I"}
NoticeResponse {"fields":[{"typ":"S","value":"WARNING"},{"typ":"C","value":"25P01"},{"typ":"M","value":"there is no transaction in progress"}]}
CommandComplete {"tag":"COMMIT"}
ReadyForQuery {"status":"I"}
NoticeResponse {"fields":[{"typ":"S","value":"WARNING"},{"typ":"C","value":"25P01"},{"typ":"M","value":"there is no transaction in progress"}]}
CommandComplete {"tag":"ROLLBACK"}
ReadyForQuery {"status":"I"}

send
Query {"query": "BEGIN;"}
Query {"query": "BEGIN;"}
----

until
ReadyForQuery
ReadyForQuery
----
CommandComplete {"tag":"BEGIN"}
ReadyForQuery {"status":"T"}
NoticeResponse {"fields":[{"typ":"S","value":"WARNING"},{"typ":"C","value":"25001"},{"typ":"M","value":"there is already a transaction in progress"}]}
CommandComplete {"tag":"BEGIN"}
ReadyForQuery {"status":"T"}

send
Query {"query": "COMMIT;"}
----

until
ReadyForQuery
----
CommandComplete {"tag":"COMMIT"}
ReadyForQuery {"status":"I"}


# Test a failure, rollback, failure, rollback chain to verify that we never process after the first failure.
send
Query {"query": "SELECT 1/(SELECT 0); ROLLBACK; SELECT 2; SELECT 1/(SELECT 0); ROLLBACK;"}
----

until err_field_typs=M
ReadyForQuery
----
RowDescription {"fields":[{"name":"?column?"}]}
ErrorResponse {"fields":[{"typ":"M","value":"division by zero"}]}
ReadyForQuery {"status":"I"}

# Test DISCARD ALL, which "cannot be executed inside a transaction
# block". This is important to test here because in materialize it
# calls end_transaction, so we need to ensure it has correct transaction
# semantics.

# Should fail within an implicit transaction.
send
Query {"query": "SELECT 1; DISCARD ALL;"}
----

until err_field_typs=C
ReadyForQuery
----
RowDescription {"fields":[{"name":"?column?"}]}
DataRow {"fields":["1"]}
CommandComplete {"tag":"SELECT 1"}
ErrorResponse {"fields":[{"typ":"C","value":"25001"}]}
ReadyForQuery {"status":"I"}

# Should fail within an explicit transaction.
send
Query {"query": "BEGIN"}
Query {"query": "DISCARD ALL"}
Query {"query": "ROLLBACK"}
----

until err_field_typs=C
ReadyForQuery
ReadyForQuery
ReadyForQuery
----
CommandComplete {"tag":"BEGIN"}
ReadyForQuery {"status":"T"}
ErrorResponse {"fields":[{"typ":"C","value":"25001"}]}
ReadyForQuery {"status":"E"}
CommandComplete {"tag":"ROLLBACK"}
ReadyForQuery {"status":"I"}

# Should succeed as a single statement.
send
Query {"query": "DISCARD ALL"}
----

until
ReadyForQuery
----
CommandComplete {"tag":"DISCARD ALL"}
ReadyForQuery {"status":"I"}

# Should (apparently?) succeed as a second statement in an extended
# session. I expected this to fail but I guess postgres allows it.
send
Parse {"query": "SELECT 1"}
Bind
Execute
Parse {"query": "DISCARD ALL"}
Bind
Execute
Sync
----

until
ReadyForQuery
----
ParseComplete
BindComplete
DataRow {"fields":["1"]}
CommandComplete {"tag":"SELECT 1"}
ParseComplete
BindComplete
CommandComplete {"tag":"DISCARD ALL"}
ReadyForQuery {"status":"I"}

# Verify DISCARD ALL fails in explicit transaction during extended mode.
send
Parse {"query": "BEGIN"}
Bind
Execute
Parse {"query": "DISCARD ALL"}
Bind
Execute
Sync
Query {"query": "ROLLBACK"}
----

until err_field_typs=C
ReadyForQuery
ReadyForQuery
----
ParseComplete
BindComplete
CommandComplete {"tag":"BEGIN"}
ParseComplete
BindComplete
ErrorResponse {"fields":[{"typ":"C","value":"25001"}]}
ReadyForQuery {"status":"E"}
CommandComplete {"tag":"ROLLBACK"}
ReadyForQuery {"status":"I"}

# Test Sync end transaciton behavior:
# "At completion of each series of extended-query messages, the frontend
# should issue a Sync message. This parameterless message causes
# the backend to close the current transaction if it's not inside a
# BEGIN/COMMIT transaction block (“close” meaning to commit if no
# error, or roll back if error)."

# An error in extended protocol ignores other statements until Sync,
# even if it's a ROLLBACK.
send
Parse {"query": "SELECT 1"}
Bind
Execute
Parse {"query": "SELECT 1/(SELECT 0)"}
Bind
Execute
Parse {"query": "ROLLBACK"}
Bind
Execute
Parse {"query": "SELECT 2"}
Bind
Execute
Sync
----

until err_field_typs=M
ReadyForQuery
----
ParseComplete
BindComplete
DataRow {"fields":["1"]}
CommandComplete {"tag":"SELECT 1"}
ParseComplete
BindComplete
ErrorResponse {"fields":[{"typ":"M","value":"division by zero"}]}
ReadyForQuery {"status":"I"}

# A ROLLBACK must be the first message in a new extended session.
send
Query {"query": "BEGIN"}
Parse {"query": "SELECT 1"}
Bind
Execute
Parse {"query": "SELECT 1/(SELECT 0)"}
Bind
Execute
Parse {"query": "ROLLBACK"}
Bind
Execute
Parse {"query": "SELECT 2"}
Bind
Execute
Sync
Parse {"query": "SELECT 3"}
Bind
Execute
Parse {"query": "ROLLBACK"}
Bind
Execute
Sync
Parse {"query": "ROLLBACK"}
Bind
Execute
Parse {"query": "SELECT 4"}
Bind
Execute
Sync
----

until err_field_typs=M
ReadyForQuery
ReadyForQuery
ReadyForQuery
ReadyForQuery
----
CommandComplete {"tag":"BEGIN"}
ReadyForQuery {"status":"T"}
ParseComplete
BindComplete
DataRow {"fields":["1"]}
CommandComplete {"tag":"SELECT 1"}
ParseComplete
BindComplete
ErrorResponse {"fields":[{"typ":"M","value":"division by zero"}]}
ReadyForQuery {"status":"E"}
ErrorResponse {"fields":[{"typ":"M","value":"current transaction is aborted, commands ignored until end of transaction block"}]}
ReadyForQuery {"status":"E"}
ParseComplete
BindComplete
CommandComplete {"tag":"ROLLBACK"}
ParseComplete
BindComplete
DataRow {"fields":["4"]}
CommandComplete {"tag":"SELECT 1"}
ReadyForQuery {"status":"I"}

# Verify there are no missed messages.
send
Query {"query": "SELECT 45"}
----

until
ReadyForQuery
----
RowDescription {"fields":[{"name":"?column?"}]}
DataRow {"fields":["45"]}
CommandComplete {"tag":"SELECT 1"}
ReadyForQuery {"status":"I"}

# Verify a failed transaction should return rollback if commit is issued.

send
Query {"query": "BEGIN; SELECT 0; COMMIT"}
Query {"query": "BEGIN; SELECT 0/0;"}
Query {"query": "COMMIT"}
Query {"query": "SELECT 1"}
----

until err_field_typs=M ignore=RowDescription
ReadyForQuery
ReadyForQuery
ReadyForQuery
ReadyForQuery
----
CommandComplete {"tag":"BEGIN"}
DataRow {"fields":["0"]}
CommandComplete {"tag":"SELECT 1"}
CommandComplete {"tag":"COMMIT"}
ReadyForQuery {"status":"I"}
CommandComplete {"tag":"BEGIN"}
ErrorResponse {"fields":[{"typ":"M","value":"division by zero"}]}
ReadyForQuery {"status":"E"}
CommandComplete {"tag":"ROLLBACK"}
ReadyForQuery {"status":"I"}
DataRow {"fields":["1"]}
CommandComplete {"tag":"SELECT 1"}
ReadyForQuery {"status":"I"}

# Verify implicit transactions are properly upgraded
send
Query {"query": "CREATE TABLE t (a INT)"}
Query {"query": "INSERT INTO t VALUES (1); BEGIN; COMMIT;"}
Parse {"query": "SELECT a FROM t"}
Bind
Execute
Sync
----

until err_field_typs=M ignore=RowDescription
ReadyForQuery
ReadyForQuery
ReadyForQuery
----
CommandComplete {"tag":"CREATE TABLE"}
ReadyForQuery {"status":"I"}
CommandComplete {"tag":"INSERT 0 1"}
CommandComplete {"tag":"BEGIN"}
CommandComplete {"tag":"COMMIT"}
ReadyForQuery {"status":"I"}
ParseComplete
BindComplete
DataRow {"fields":["1"]}
CommandComplete {"tag":"SELECT 1"}
ReadyForQuery {"status":"I"}

# Verify implicit transactions are properly upgraded, and share fate
# with upgraded txn; the insert of 2 should get discarded
send
Query {"query": "INSERT INTO t VALUES (2); BEGIN; SELECT 0/0;"}
Parse {"query": "COMMIT"}
Bind
Execute
Sync
Parse {"query": "SELECT a FROM t"}
Bind
Execute
Sync
----

until err_field_typs=M ignore=RowDescription
ReadyForQuery
ReadyForQuery
ReadyForQuery
----
CommandComplete {"tag":"INSERT 0 1"}
CommandComplete {"tag":"BEGIN"}
ErrorResponse {"fields":[{"typ":"M","value":"division by zero"}]}
ReadyForQuery {"status":"E"}
ParseComplete
BindComplete
CommandComplete {"tag":"ROLLBACK"}
ReadyForQuery {"status":"I"}
ParseComplete
BindComplete
DataRow {"fields":["1"]}
CommandComplete {"tag":"SELECT 1"}
ReadyForQuery {"status":"I"}

# PG permits starting a read only txn with writes blended into its ops;
# evidence of this behavior by an errored txn does not commit write.
send
Query {"query": "INSERT INTO t VALUES (2); BEGIN READ ONLY; SELECT 0/0;"}
Parse {"query": "COMMIT"}
Bind
Execute
Sync
Parse {"query": "SELECT a FROM t"}
Bind
Execute
Sync
----

until err_field_typs=M ignore=RowDescription
ReadyForQuery
ReadyForQuery
ReadyForQuery
----
CommandComplete {"tag":"INSERT 0 1"}
CommandComplete {"tag":"BEGIN"}
ErrorResponse {"fields":[{"typ":"M","value":"division by zero"}]}
ReadyForQuery {"status":"E"}
ParseComplete
BindComplete
CommandComplete {"tag":"ROLLBACK"}
ReadyForQuery {"status":"I"}
ParseComplete
BindComplete
DataRow {"fields":["1"]}
CommandComplete {"tag":"SELECT 1"}
ReadyForQuery {"status":"I"}

# PG permits commits writes that are part of read-only txns
# n.b. this is possible only because we support selecting constants
# in write-only txns
send
Parse {"query": "INSERT INTO t VALUES (2)"}
Bind
Execute
Parse {"query": "BEGIN READ ONLY"}
Bind
Execute
Parse {"query": "SELECT 1"}
Bind
Execute
Parse {"query": "COMMIT"}
Bind
Execute
Sync
Parse {"query": "SELECT a FROM t"}
Bind
Execute
Sync
----

until err_field_typs=M ignore=RowDescription
ReadyForQuery
ReadyForQuery
----
ParseComplete
BindComplete
CommandComplete {"tag":"INSERT 0 1"}
ParseComplete
BindComplete
CommandComplete {"tag":"BEGIN"}
ParseComplete
BindComplete
DataRow {"fields":["1"]}
CommandComplete {"tag":"SELECT 1"}
ParseComplete
BindComplete
CommandComplete {"tag":"COMMIT"}
ReadyForQuery {"status":"I"}
ParseComplete
BindComplete
DataRow {"fields":["1"]}
DataRow {"fields":["2"]}
CommandComplete {"tag":"SELECT 2"}
ReadyForQuery {"status":"I"}