[YSQL] Ensure no kConflict errors are thrown in a READ COMMITTED txn even if statement's output exceeds ysql_output_buffer_size (gflag with default of 256KB). #11572
Assignees
Labels
2024.1 Backport Required
area/docdb
YugabyteDB core features
area/ysql
Yugabyte SQL (YSQL)
kind/enhancement
This is an enhancement of an existing feature
pg-compatibility
Label for issues that result in differences b/w YSQL and Pg semantics
priority/medium
Medium priority issue
Comments
pkj415
changed the title
pkj415
added
the
pg-compatibility
yugabyte-ci
added
kind/bug
yugabyte-ci
added
kind/enhancement
yugabyte-ci
added
priority/highest
yugabyte-ci
added
priority/high
pkj415
changed the title
yugabyte-ci
added
priority/medium
pkj415
added a commit
that referenced
this issue
Jul 9, 2024
…mitted isolation Summary: Currently in YSQL, a user isn't allowed to change the transaction isolation level after starting a READ COMMITTED transaction. This is because of the following reason: (1) In PostgreSQL, the transaction isolation level can be changed only if no sub-transactions have been created. If a sub-transaction is created the user faces this error - `ERROR: SET TRANSACTION ISOLATION LEVEL must not be called in a subtransaction`. (2) In YSQL's read committed isolation, an internal savepoint is created before execution of each statement. This is an implementation detail and is required to be able to undo work and restart a statement in case of serialization and read restart errors. And each savepoint creates an internal sub-transaction. Hence, when starting a transaction in READ COMMITTED isolation and trying to change the isolation level using `SET TRANSACTION ISOLATION LEVEL ...` an internal sub-transaction is started before changing the isolation level. This leads to the above error. The same applies for `SET TRANSACTION DEFERRABLE` and `SET TRANSACTION READ WRITE`. This diff fixes the issues by allowing change of transaction characteristics i.e., isolation, deferrable, read-write if all sub-transactions are only internal read committed sub-transactions and no statement/query was executed yet. NOTE: This is a workaround. The correct way to do this would have been to not start an internal sub-transaction in the first place if a `SET TRANSACTION ...` statement is encountered. However, that isn't possible without major code changes since the internal sub-transaction is taken before parsing the statement. Moreover, once we improve the READ COMMITTED isolation implementation as part of GitHub issue ##11572, internal sub-transactions will not be needed and this problem will be solved without this workaround. Jira: [DB-328] Test Plan: ./yb_build.sh --java-test org.yb.pgsql.TestPgIsolationRegress#isolationRegress Reviewers: mtakahara Reviewed By: mtakahara Subscribers: amitanand, yql Differential Revision: https://phorge.dev.yugabyte.com/D20688
pkj415
added a commit
that referenced
this issue
Jul 16, 2024
…stics in read committed isolation Summary: Currently in YSQL, a user isn't allowed to change the transaction isolation level after starting a READ COMMITTED transaction. This is because of the following reason: (1) In PostgreSQL, the transaction isolation level can be changed only if no sub-transactions have been created. If a sub-transaction is created the user faces this error - `ERROR: SET TRANSACTION ISOLATION LEVEL must not be called in a subtransaction`. (2) In YSQL's read committed isolation, an internal savepoint is created before execution of each statement. This is an implementation detail and is required to be able to undo work and restart a statement in case of serialization and read restart errors. And each savepoint creates an internal sub-transaction. Hence, when starting a transaction in READ COMMITTED isolation and trying to change the isolation level using `SET TRANSACTION ISOLATION LEVEL ...` an internal sub-transaction is started before changing the isolation level. This leads to the above error. The same applies for `SET TRANSACTION DEFERRABLE` and `SET TRANSACTION READ WRITE`. This diff fixes the issues by allowing change of transaction characteristics i.e., isolation, deferrable, read-write if all sub-transactions are only internal read committed sub-transactions and no statement/query was executed yet. NOTE: This is a workaround. The correct way to do this would have been to not start an internal sub-transaction in the first place if a `SET TRANSACTION ...` statement is encountered. However, that isn't possible without major code changes since the internal sub-transaction is taken before parsing the statement. Moreover, once we improve the READ COMMITTED isolation implementation as part of GitHub issue ##11572, internal sub-transactions will not be needed and this problem will be solved without this workaround. Jira: [DB-328] Original commit: 3494424 / D20688 Test Plan: ./yb_build.sh --java-test org.yb.pgsql.TestPgIsolationRegress#isolationRegress Reviewers: mtakahara, amitanand Reviewed By: mtakahara Subscribers: yql Tags: #jenkins-ready Differential Revision: https://phorge.dev.yugabyte.com/D36581
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Jira Link: DB-508
Description
Ensure no kConflict errors are thrown in a READ COMMITTED txn even if statement's output exceeds ysql_output_buffer_size and results in flushing before a kReadRestart/kConflict error is hit.
We need to solve this only if #11573 will not be solved, else this will be solved implicitly.
The text was updated successfully, but these errors were encountered: