opt: support INSERT ON CONFLICT DO UPDATE and unique partial indexes#53171
Merged
Conversation
Member
|
This change is |
Contributor
Author
|
The first commit is from #53067. |
mgartner
commented
Aug 21, 2020
Contributor
Author
There was a problem hiding this comment.
The canary column is changing because of this change in optbuilder/insert.go:
- canaryScopeCol := &fetchScope.cols[findNotNullIndexCol(mb.tab.Index(cat.PrimaryIndex))]
+ canaryScopeCol := &fetchScope.cols[findNotNullIndexCol(index)]The changes in these tests looks fine — a different not-null column is found. In this case, instead of the primary key, its the indexed non-null column a. I don't think this should not affect the results or performance of the UPSERT in any way.
e5d190a to
d1be69f
Compare
rytaft
approved these changes
Aug 24, 2020
rytaft
left a comment
Collaborator
There was a problem hiding this comment.
Reviewed 14 of 14 files at r1, 2 of 3 files at r2, 3 of 3 files at r3.
Reviewable status:complete! 1 of 0 LGTMs obtained
This commit adds support for `INSERT ON CONFLICT DO UPDATE` statements
on tables with unique partial indexes. In order to use a unique partial
index as an arbiter, a `WHERE` clause must be provided that implies the
partial index's predicate expression. For example:
CREATE TABLE t (a INT, b INT, UNIQUE INDEX (a) WHERE b > 0)
INSERT INTO t VALUES (1, 1)
ON CONFLICT (a) WHERE b > 0
DO UPDATE SET b = 10
In the event that multiple arbiter indexes are found, an error is
returned to the user. Postgres supports multiple arbiters, but this is
left as a TODO for now, until there a strong enough motivation to
justify the added complexity.
Similar to the previous commit that added support for `INSERT ON
CONFLICT DO NOTHING` for unique partial indexes, there are three primary
changes to the expression tree build by `optbuilder`:
1. Fetched rows are filtered by the partial index predicate.
2. Only insert rows that satisfy the partial index predicate are
joined with fetched rows in the left outer join.
3. A new column is projected so that the `EnsureUpsertDistinctOn`
expression only errors when rows that satisfy the partial index
predicate conflict with each other.
Release note: None
d1be69f to
6364f27
Compare
Contributor
Author
|
bors r=rytaft |
Contributor
Author
|
TFTR @rytaft! |
Contributor
|
Build succeeded: |
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
3 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.
This commit adds support for
INSERT ON CONFLICT DO UPDATEstatementson tables with unique partial indexes. In order to use a unique partial
index as an arbiter, a
WHEREclause must be provided that implies thepartial index's predicate expression. For example:
In the event that multiple arbiter indexes are found, an error is
returned to the user. Postgres supports multiple arbiters, but this is
left as a TODO for now, until there a strong enough motivation to
justify the added complexity.
Similar to the previous commit that added support for
INSERT ON CONFLICT DO NOTHINGfor unique partial indexes, there are three primarychanges to the expression tree build by
optbuilder:Fetched rows are filtered by the partial index predicate.
Only insert rows that satisfy the partial index predicate are
joined with fetched rows in the left outer join.
A new column is projected so that the
EnsureUpsertDistinctOnexpression only errors when rows that satisfy the partial index
predicate conflict with each other.
Fixes #52603
Release note: None