Transaction not read only if BEGIN READ ONLY is executed on advanced protocol #87012
Description
Describe the problem
Issue found after fixing psycopg/psycopg#350. In order to execute the BEGIN to start a transaction, we used to use the libpq function PQexec(), but we had to switch to using PQexecParams() because of a bug in libpq 14.5.
If a transaction is started using PQexecParams("BEGIN READ ONLY", None), the transaction starts, but it won't be in read only mode.
To Reproduce
Run crdb:
docker run --rm --name crdb -p 26257:26257 cockroachdb/cockroach:v22.1.6 start-single-node --insecure
Run the following script using psycopg 3.1 or checking out master:
# bug-crdb-read-only.py
from psycopg import pq
DSN = "host=127.0.0.1 user=root port=26257 dbname=defaultdb"
conn = pq.PGconn.connect(DSN.encode())
conn.trace(1)
conn.set_trace_flags(pq.Trace.SUPPRESS_TIMESTAMPS | pq.Trace.REGRESS_MODE)
print("ok")
assert conn.transaction_status == pq.TransactionStatus.IDLE
conn.exec_(b"BEGIN READ ONLY")
assert conn.transaction_status == pq.TransactionStatus.INTRANS
res = conn.exec_(b"SHOW transaction_read_only")
assert res.get_value(0, 0) == b"on", res.get_value(0, 0)
conn.exec_(b"ROLLBACK")
print("bad")
assert conn.transaction_status == pq.TransactionStatus.IDLE
conn.exec_params(b"BEGIN READ ONLY", None)
assert conn.transaction_status == pq.TransactionStatus.INTRANS
res = conn.exec_(b"SHOW transaction_read_only")
assert res.get_value(0, 0) == b"on", res.get_value(0, 0)
conn.finish()The script passes on CRDB 21.2.14. On 22.1.6, it fails with output:
ok
F 20 Query "BEGIN READ ONLY"
B 10 CommandComplete "BEGIN"
B 5 ReadyForQuery T
F 31 Query "SHOW transaction_read_only"
B 46 RowDescription 1 "transaction_read_only" NNNN 2 NNNN 65535 -1 0
B 12 DataRow 1 2 'on'
B 11 CommandComplete "SHOW 1"
B 5 ReadyForQuery T
F 13 Query "ROLLBACK"
B 13 CommandComplete "ROLLBACK"
B 5 ReadyForQuery I
bad
F 23 Parse "" "BEGIN READ ONLY" 0
F 14 Bind "" "" 0 0 1 0
F 6 Describe P ""
F 9 Execute "" 0
F 4 Sync
B 4 ParseComplete
B 4 BindComplete
B 4 NoData
B 10 CommandComplete "BEGIN"
B 5 ReadyForQuery T
F 31 Query "SHOW transaction_read_only"
B 46 RowDescription 1 "transaction_read_only" NNNN 2 NNNN 65535 -1 0
B 13 DataRow 1 3 'off'
B 11 CommandComplete "SHOW 1"
B 5 ReadyForQuery T
Traceback (most recent call last):
File "bug-crdb-read-only.py", line 24, in <module>
assert res.get_value(0, 0) == b"on", res.get_value(0, 0)
AssertionError: b'off'
F 4 Terminate
Environment:
- CockroachDB version: 22.1.6 (working in 21.2.14)
- Server OS: Linux
Epic CRDB-14049
Jira issue: CRDB-19095