cockroachdb / cockroachdb/cockroach

sql/pgwire: portal created before update should not show the updated value

Open
#98,118 1 comment 0 reactions 0 assignees View on GitHub
C-bug T-sql-foundations
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

Sent the following pgwire messages to postgres 13.3 and cockroachdb on master (built on c078537681f). The behavior of CRDB is different from Postgres.

```
send
Query {"String": "BEGIN"}
Query {"String": "CREATE TABLE mytable (x int)"}
Query {"String": "INSERT INTO mytable VALUES (1),(2),(3)"}
Parse {"Name": "q3", "Query": "SELECT * FROM mytable"}
Bind {"DestinationPortal": "p3", "PreparedStatement": "q3"}
Query {"String": "UPDATE mytable SET x = 10"}
Execute {"Portal": "p3", "MaxRows": 2}
Sync
----

until
ReadyForQuery
ReadyForQuery
ReadyForQuery
ReadyForQuery
ReadyForQuery
----
```

Postgres:
```
{"Type":"CommandComplete","CommandTag":"BEGIN"}
{"Type":"ReadyForQuery","TxStatus":"T"}
{"Type":"CommandComplete","CommandTag":"CREATE TABLE"}
{"Type":"ReadyForQuery","TxStatus":"T"}
{"Type":"CommandComplete","CommandTag":"INSERT 0 3"}
{"Type":"ReadyForQuery","TxStatus":"T"}
{"Type":"ParseComplete"}
{"Type":"BindComplete"}
{"Type":"CommandComplete","CommandTag":"UPDATE 3"}
{"Type":"ReadyForQuery","TxStatus":"T"}
{"Type":"DataRow","Values":[{"text":"1"}]}
{"Type":"DataRow","Values":[{"text":"2"}]}
{"Type":"PortalSuspended"}
{"Type":"ReadyForQuery","TxStatus":"T"}
```

CRDB:
```
{"Type":"CommandComplete","CommandTag":"BEGIN"}
{"Type":"ReadyForQuery","TxStatus":"T"}
{"Type":"CommandComplete","CommandTag":"CREATE TABLE"}
{"Type":"ReadyForQuery","TxStatus":"T"}
{"Type":"CommandComplete","CommandTag":"INSERT 0 3"}
{"Type":"ReadyForQuery","TxStatus":"T"}
{"Type":"ParseComplete"}
{"Type":"BindComplete"}
{"Type":"CommandComplete","CommandTag":"UPDATE 3"}
{"Type":"ReadyForQuery","TxStatus":"T"}
{"Type":"DataRow","Values":[{"text":"10"}]}
{"Type":"DataRow","Values":[{"text":"10"}]}
{"Type":"PortalSuspended"}
{"Type":"ReadyForQuery","TxStatus":"T"}
```

So the portal is declared before the row updates, but in cockroach, the portal execution gives value after the updates (i.e. `10`).

Jira issue: CRDB-25086

Epic CRDB-61001

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.