opt: ensure that implicitly partitioned unique partial indexes behave correctly #59195

rytaft · 2021-01-20T15:37:21Z

Currently, the optimizer assumes that unique constraints created with UNIQUE WITHOUT INDEX do not have a predicate. However, the optimizer will need to handle unique constraints with a predicate once implicitly partitioned unique partial indexes are supported. To ensure that this works, it might be worth allowing UNIQUE WITHOUT INDEX constraints to have a predicate. Regardless of whether we support predicates with UNIQUE WITHOUT INDEX, we will need to add tests with partitioned unique partial indexes as well.

cc @otan

The text was updated successfully, but these errors were encountered:

This commit allows creation of partial `UNIQUE WITHOUT INDEX` constraints. These partial unique constraints have a predicate to enforce uniqueness on the subset of rows in which the predicate evaluates to true, similar to a unique partial index. An example of a partial `UNIQUE WITHOUT INDEX` constraint: CREATE TABLE t ( a INT, UNIQUE WITHOUT INDEX (a) WHERE a > 0 ) **NOTE**: these constraints are not yet correctly enforcing uniqueness on a subset of rows in the table. This requires optimizer changes that will come in a follow-up commit. Specifically, this commit adds support for: - Creating these constraints in `CREATE TABLE` and `ALTER TABLE`. - Storing the predicates in table descriptor constraints. - Validating the predicates both during creation and during descriptor validation. - Displaying the predicates in `SHOW CREATE` and `SHOW CONSTRAINTS` output, and in the pg_catalog.pg_constraint table. - Accessing the predicates via the opt catalog. The primary motivation for this change is to simplify testing for implicitly partitioned partial unique indexes. An experimental session setting is required to use `UNIQUE WITHOUT INDEX` there are no current plans to make these constraints available to users by default. Informs cockroachdb#59195 There is no release note because these constraints are gated behind the experimental_enable_unique_without_index_constraints session variable. Release note: None

60289: sql: allow creation of partial UNIQUE WITHOUT INDEX constraints r=mgartner a=mgartner This commit allows creation of partial `UNIQUE WITHOUT INDEX` constraints. These partial unique constraints have a predicate to enforce uniqueness on the subset of rows in which the predicate evaluates to true, similar to a unique partial index. An example of a partial `UNIQUE WITHOUT INDEX` constraint: CREATE TABLE t ( a INT, UNIQUE WITHOUT INDEX (a) WHERE a > 0 ) **NOTE**: these constraints are not yet correctly enforcing uniqueness on a subset of rows in the table. This requires optimizer changes that will come in a follow-up commit. Specifically, this commit adds support for: - Creating these constraints in `CREATE TABLE` and `ALTER TABLE`. - Storing the predicates in table descriptor constraints. - Validating the predicates both during creation and during descriptor validation. - Displaying the predicates in `SHOW CREATE` and `SHOW CONSTRAINTS` output, and in the pg_catalog.pg_constraint table. - Accessing the predicates via the opt catalog. The primary motivation for this change is to simplify testing for implicitly partitioned partial unique indexes. An experimental session setting is required to use `UNIQUE WITHOUT INDEX` there are no current plans to make these constraints available to users by default. Informs #59195 There is no release note because these constraints are gated behind the experimental_enable_unique_without_index_constraints session variable. Release note: None Co-authored-by: Marcus Gartner <[email protected]>

Uniqueness checks on INSERTs are now performed for partial UNIQUE WITHOUT INDEX constraints. The semi-join filters in the uniqueness checks filter out rows that do not satisfy the predicate. Rows on both sides of the join are filtered. This is required to prevent duplicate key violations from occurring for rows that do not satisfy the predicate of the partial unique constraint. The WithScan and Scan of the uniqueness check now produce all ordinary table columns, rather than just the PK and unique columns, because predicates may refer any columns in the table. Informs cockroachdb#59195 There is no release note because these constraints are gated behind the experimental_enable_unique_without_index_constraints session variable. Release note: None

60535: sql: support INSERT with partial UNIQUE WITHOUT INDEX constraints r=mgartner a=mgartner #### optbuilder: return scope from mutationBuilder.buildCheckInputScan This commit updates `mutationBuilder.buildCheckInputScan` to build a scope containing the constructed WithScan expression and the scope columns output by the expression. Also, the WithScan output column names are now the names of the columns in the underlying table, rather than the names of the input columns. Access to a scope for the WithScan expression will be required to support partial `UNIQUE WITHOUT INDEX` constraints. For uniqueness checks, a filter on the WithScan columns must be added to the semi-join filters. The WithScan scope will be required in order to build the filter expression. Additionally, the names of the WithScan output columns must match the names of the columns in the underlying table in order to build partial predicate expressions that refer to the table's columns. Release note: None #### sql: support INSERT with partial UNIQUE WITHOUT INDEX constraints Uniqueness checks on INSERTs are now performed for partial UNIQUE WITHOUT INDEX constraints. The semi-join filters in the uniqueness checks filter out rows that do not satisfy the predicate. Rows on both sides of the join are filtered. This is required to prevent duplicate key violations from occurring for rows that do not satisfy the predicate of the partial unique constraint. The WithScan and Scan of the uniqueness check now produce all ordinary table columns, rather than just the PK and unique columns, because predicates may refer any columns in the table. Informs #59195 There is no release note because these constraints are gated behind the experimental_enable_unique_without_index_constraints session variable. Release note: None Co-authored-by: Marcus Gartner <[email protected]>

…aints To support INSERT ON CONFLICT DO NOTHING statements on tables with partial UNIQUE WITHOUT INDEX constraints, partial constraints are now selected as arbiters. These arbiters are used to filter out insert rows that would conflict with existing rows in the table. Informs cockroachdb#59195 There is no release note because these constraints are gated behind the experimental_enable_unique_without_index_constraints session variable. Release note: None

60680: opt: support INSERT ON CONFLICT DO NOTHING with partial unique constraints r=mgartner a=mgartner #### opt: move INSERT DO NOTHING arbiter code to mutation_builder_arbiter.go Arbiter-related code in `pkg/sql/opt/optbuilder/insert.go` has grown unruly due to the added complexity of partial indexes and unique constraints. This commit moves some arbiter-related functions to a new file to accommodate the growth. It also breaks some anonymous closures into independent functions, for clarity. Release note: None #### opt: pass column ordinals directly to arbiter building functions This commit makes the arguments of `buildAntiJoinForDoNothingArbiter` and `buildDistinctOnForDoNothingArbiter` more intuitive. Columns ordinals are now passed directly to these functions, rather than a column count and ordinal-returning callback. Release note: None #### opt: create arbiterPredicateHelper for picking partial index arbiters This commit adds a new helper struct that can determine if a partial index can be used as an arbiter based on the arbiter predicate of an `INSERT ON CONFLICT` statement. This will also be a useful utility to determine if partial unique constraints can be used as arbiters. Release note: None #### opt: support INSERT ON CONFLICT DO NOTHING with partial unique constraints To support INSERT ON CONFLICT DO NOTHING statements on tables with partial UNIQUE WITHOUT INDEX constraints, partial constraints are now selected as arbiters. These arbiters are used to filter out insert rows that would conflict with existing rows in the table. Informs #59195 There is no release note because these constraints are gated behind the experimental_enable_unique_without_index_constraints session variable. Release note: None Co-authored-by: Marcus Gartner <[email protected]>

mgartner · 2021-03-05T01:35:12Z

This work was completed via a handful of PRs.

rytaft added C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) A-sql-optimizer SQL logical planning and optimizations. A-multiregion Related to multi-region T-multiregion labels Jan 20, 2021

rytaft assigned mgartner Feb 3, 2021

mgartner mentioned this issue Feb 12, 2021

sql: allow creation of partial UNIQUE WITHOUT INDEX constraints #60289

Merged

mgartner mentioned this issue Feb 12, 2021

sql: support INSERT with partial UNIQUE WITHOUT INDEX constraints #60535

Merged

mgartner mentioned this issue Feb 17, 2021

opt: support INSERT ON CONFLICT DO NOTHING with partial unique constraints #60680

Merged

mgartner closed this as completed Mar 5, 2021

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

opt: ensure that implicitly partitioned unique partial indexes behave correctly #59195

opt: ensure that implicitly partitioned unique partial indexes behave correctly #59195

rytaft commented Jan 20, 2021

mgartner commented Mar 5, 2021

opt: ensure that implicitly partitioned unique partial indexes behave correctly #59195

opt: ensure that implicitly partitioned unique partial indexes behave correctly #59195

Comments

rytaft commented Jan 20, 2021

mgartner commented Mar 5, 2021