[WIP] opt: add hint to ignore preserved-multiplicity consistency#82188
Closed
michae2 wants to merge 1 commit intocockroachdb:masterfrom
Closed
[WIP] opt: add hint to ignore preserved-multiplicity consistency#82188michae2 wants to merge 1 commit intocockroachdb:masterfrom
michae2 wants to merge 1 commit intocockroachdb:masterfrom
Conversation
Member
|
This change is |
733de8e to
6e3908c
Compare
For some queries (e.g. SELECT FOR UPDATE SKIP LOCKED) we need to disable optimizations that depend on preserved-multiplicity consistency of tables. Add an IGNORE_PRESERVED_CONSISTENCY hint that turns off these optimizations. With this hint, we will no longer use optimizations that assume: - a PK row exists for every secondary index row - a FK row exists for every referenced FK Release note: None
6e3908c to
4b350da
Compare
Collaborator
Author
|
Note to self: this is a little bit broken. Some of the rules that I modified would work fine and probably don't need to be modified. Also this needs tests. |
Collaborator
Author
|
Tracking issue is: #83156 |
Collaborator
Author
|
Notes from talking to @rytaft:
|
craig bot
pushed a commit
that referenced
this pull request
Aug 10, 2022
85720: sql: parser and optimizer support FOR {UPDATE,SHARE} SKIP LOCKED r=rytaft a=rytaft
This PR adds support for `SKIP LOCKED` by building on commits from #83627 and #82188. See below for details.
**sql: enable the skip-locked wait policy**
Now that KV support this, we can pass the wait policy through.
Fixes #40476
Release note (sql change): `SELECT ... FOR {UPDATE,SHARE} SKIP LOCKED`
is now supported. The option can be used to skip rows that cannot be
immediately locked instead of blocking on contended row-level lock
acquisition.
**opt: optimizer updates for support of SKIP LOCKED**
For queries using `SELECT FOR {SHARE,UPDATE} SKIP LOCKED`, we need to disable
optimizations that depend on preserved-multiplicity consistency of
tables. When `SKIP LOCKED` is used, we will no longer use optimizations that
assume:
- a PK row exists for every secondary index row
- a PK row exists for every referencing FK (if the PK table uses `SKIP LOCKED`)
One result of this change is that we will no longer push limits into index
joins if the primary index uses locking wait policy `SKIP LOCKED`.
This commit also disallows use of multiple column families in tables scanned
with `SKIP LOCKED`, since it could result in returning partial rows.
Release note: None
Co-authored-by: Michael Erickson <michae2@cockroachlabs.com>
Co-authored-by: Nathan VanBenschoten <nvanbenschoten@gmail.com>
Co-authored-by: Rebecca Taft <becca@cockroachlabs.com>
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
2 participants
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
For some queries (e.g. SELECT FOR UPDATE SKIP LOCKED) we need to disable
optimizations that depend on preserved-multiplicity consistency of
tables. Add an IGNORE_PRESERVED_CONSISTENCY hint that turns off these
optimizations. With this hint, we will no longer use optimizations that
assume:
Release note: None