[YSQL] Enable INSERT ON CONFLICT batching for null-not-distinct indexes #24834
Labels
area/ysql
Yugabyte SQL (YSQL)
kind/enhancement
This is an enhancement of an existing feature
priority/medium
Medium priority issue
Comments
yugabyte-ci
added
kind/enhancement
|
Draft implementation in development branch that was revert: 3fac454 (arpang/yugabyte-db) |
arpang
pushed a commit
to arpang/yugabyte-db
that referenced
this issue
Nov 12, 2024
…LLS NOT DISTINCT unique indexes [Sample, DNR] Summary: D38354 and D39023 introduced batching support for INSERT ... ON CONFLICT queries. D39058 introduced NULLS NOT DISTINCT support for unique indexes. This revision adds support for INSERT ... ON CONFLICT batching for arbiter indexes that are marked with NULLS NOT DISTINCT. In particular, this revision handles a specific subtle case with batching NULL values. Consider the following example: ``` CREATE TABLE ab_tab (a int, b int); CREATE UNIQUE INDEX NONCONCURRENTLY ah_idx ON ab_tab (a HASH) NULLS NOT DISTINCT; -- Query 1 INSERT INTO ab_tab VALUES (1, 1), (1, 2) ON CONFLICT (a) DO UPDATE SET b = EXCLUDED.b; -- Query 2 INSERT INTO ab_tab VALUES (null, 1), (null, 2) ON CONFLICT (a) DO UPDATE SET b = EXCLUDED.b; ``` Batching for Query 1 is executed in the following manner: ``` Index scan on ah_idx WHERE a IN (1, 1) -- corresponding to (1, 1) and (1, 2) The expression is deduplicated in DocDB and any row corresponding to a IN (1) is returned. Store key, slot corresponding to a = 1 in map Load key, slot corresponding to a = 1 in map -- corresponding to (1, 1) Store intent corresponding to a = 1 in map Load key, slot corresponding to a = 1 in map -- corresponding to (1, 2) Error "row cannot be modified a second time" ``` NULL values cannot be a part of the IN clause. Thus NULL values have to be looked up one at a time. Batching for Query 2 would be executed in the following manner without the special handling in this revision: ``` Index scan on ah_idx WHERE a IS NULL -- corresponding to (null, 1) Store key, slot corresponding to a is NULL in map Index scan on ah_idx WHERE a is NULL -- corresponding to (null, 2) Store key, slot corresponding to a is NULL in map Error "key already exists" ``` Thus NULL values have to be deduplicated before they are looked up. **Credits** Most of the code has been borrowed from @aagrawal's revision (D39058). Test Plan: ./yb_build.sh --java-test 'org.yb.pgsql.TestPgRegressPgConstraints' ./yb_build.sh --java-test 'org.yb.pgsql.TestPgRegressPgMisc' ./yb_build.sh --java-test 'org.yb.pgsql.TestPgRegressInsertOnConflict' ./yb_build.sh --java-test 'org.yb.pgsql.TestPgRegressForeignKey' Subscribers: yugaware, aagrawal, yql, ybase, smishra Differential Revision: https://phorge.dev.yugabyte.com/D39852
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Jira Link: DB-13945
Description
Placeholder issue to enable INSERT ON CONFLICT batching for null-not-distinct indexes.
Issue Type
kind/enhancement
Warning: Please confirm that this issue does not contain any sensitive information
The text was updated successfully, but these errors were encountered: