Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

sql: create built-in function to detect violations of unique constraints #73560

Closed
rytaft opened this issue Dec 7, 2021 · 5 comments · Fixed by #75548
Closed

sql: create built-in function to detect violations of unique constraints #73560

rytaft opened this issue Dec 7, 2021 · 5 comments · Fixed by #75548
Assignees
@rytaft
Labels
A-sql-builtins SQL built-in functions and semantics thereof. C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) E-quick-win Likely to be a quick win for someone experienced. O-postmortem Originated from a Postmortem action item. T-sql-queries SQL Queries Team

Comments

@rytaft
Copy link
Collaborator

rytaft commented Dec 7, 2021

Due to the issue described in #73024, on 21.2.0, unique constraints on REGIONAL BY ROW tables may not be enforced. It would be helpful to have an easy way to detect violations of unique constraints, such as a built-in SQL function. This function could simply run a SQL query under the covers, similar to the one suggested for detecting violations in the technical advisory for #73024.
@rytaft rytaft added C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) A-sql-builtins SQL built-in functions and semantics thereof. O-postmortem Originated from a Postmortem action item. labels Dec 7, 2021
@blathers-crl blathers-crl bot added the T-sql-queries SQL Queries Team label Dec 7, 2021
@nvanbenschoten
Copy link
Member

How would this differ from ALTER TABLE <t> VALIDATE CONSTRAINT <unique_constraint_without_index>, which you previously added in support for in de5c514. Is it just that you want a "force" option that skips the "already validated" short-circuiting?

@rytaft
Copy link
Collaborator Author

rytaft commented Dec 17, 2021

Sorry for the slow reply @nvanbenschoten!

How would this differ from ALTER TABLE <t> VALIDATE CONSTRAINT <unique_constraint_without_index>, which you previously added in support for in de5c514. Is it just that you want a "force" option that skips the "already validated" short-circuiting?

Yea, exactly. The idea is that this would be a simple way for customers to detect corruption due to #73024. But I'm also not 100% convinced the built-in is necessary if you see potential issues with it. We also opened #73632 to add unique constraint checks to SCRUB.

@nvanbenschoten
Copy link
Member

But I'm also not 100% convinced the built-in is necessary if you see potential issues with it.

I don't see any problems with it. I was just pointing out that we may already have 90% of the logic we'd want with ALTER TABLE <t> VALIDATE CONSTRAINT <unique_constraint_without_index>, so maybe we just need a ALTER TABLE <t> VALIDATE (FORCE) CONSTRAINT <unique_constraint_without_index> option.

@rytaft
Copy link
Collaborator Author

rytaft commented Dec 17, 2021

Good point -- I'll look into that as an option -- thanks!

@rytaft rytaft added the E-quick-win Likely to be a quick win for someone experienced. label Jan 10, 2022
@rytaft rytaft mentioned this issue Jan 26, 2022
Merged
@rytaft
Copy link
Collaborator Author

rytaft commented Jan 26, 2022

Just to follow up on this, I decided to go with the builtin function since it can be used to easily validate all unique constraints in the DB at once, which seems more useful at this point. I think we could add support for VALIDATE (FORCE) CONSTRAINT or something like that, but it would require a bit of effort (shouldn't be too bad, though). Anyway, we can revisit in the future if it seems like we want to support both.

rytaft added a commit to rytaft/cockroach that referenced this issue Jan 26, 2022
@rytaft
sql: add builtin function to validate unique constraints
f82c8fe 
Fixes cockroachdb#73560

Release note (sql change): Added a new builtin function called
crdb_internal.validate_unique_constraints, which can be used
to validate the unique constraints of all tables in the current
database. If any constraint fails validation, the function will
return an error with a hint about which data caused the constraint
violation. These violations can then be resolved manually by updating
or deleting the rows in violation. This will be useful to users who
think they may have been affected by cockroachdb#73024.
rytaft added a commit to rytaft/cockroach that referenced this issue Jan 28, 2022
@rytaft
sql: add builtin functions to revalidate unique constraints
102de17 
Fixes cockroachdb#73560

Release note (sql change): Added new builtin functions called
crdb_internal.revalidate_unique_constraint, and
crdb_internal.revalidate_unique_constraints, which can be used
to revalidate existing unique constraints. There are overloads to support
validation of a single constraint, all unique constraints in a table,
or all unique constraints of all tables in the current
database. If any constraint fails validation, the function will
return an error with a hint about which data caused the constraint
violation. These violations can then be resolved manually by updating
or deleting the rows in violation. This will be useful to users who
think they may have been affected by cockroachdb#73024.
rytaft added a commit to rytaft/cockroach that referenced this issue Jan 28, 2022
@rytaft
sql: add builtin functions to revalidate unique constraints
4891239 
Fixes cockroachdb#73560

Release note (sql change): Added new builtin functions called
crdb_internal.revalidate_unique_constraint,
crdb_internal.revalidate_unique_constraints_in_table, and
crdb_internal.revalidate_unique_constraints_in_all_tables, which can
be used to revalidate existing unique constraints. The different
variations support validation of a single constraint, validation of
all unique constraints in a table, and validation of all unique
constraints in all tables in the current database, respectively.
If any constraint fails validation, the functions will return an
error with a hint about which data caused the constraint violation.
These violations can then be resolved manually by updating or deleting
the rows in violation. This will be useful to users who think they may
have been affected by cockroachdb#73024.
rytaft added a commit to rytaft/cockroach that referenced this issue Jan 31, 2022
@rytaft
sql: add builtin functions to revalidate unique constraints
ade1876 
Fixes cockroachdb#73560

Release note (sql change): Added new builtin functions called
crdb_internal.revalidate_unique_constraint,
crdb_internal.revalidate_unique_constraints_in_table, and
crdb_internal.revalidate_unique_constraints_in_all_tables, which can
be used to revalidate existing unique constraints. The different
variations support validation of a single constraint, validation of
all unique constraints in a table, and validation of all unique
constraints in all tables in the current database, respectively.
If any constraint fails validation, the functions will return an
error with a hint about which data caused the constraint violation.
These violations can then be resolved manually by updating or deleting
the rows in violation. This will be useful to users who think they may
have been affected by cockroachdb#73024.
craig bot pushed a commit that referenced this issue Feb 2, 2022
@rytaft @yuzefovich
Merge #75548 #75741
c0dbb83 
75548: sql: add builtin functions to revalidate unique constraints r=rytaft a=rytaft

Fixes #73560

Release note (sql change): Added new builtin functions called
`crdb_internal.revalidate_unique_constraint`,
`crdb_internal.revalidate_unique_constraints_in_table`, and
`crdb_internal.revalidate_unique_constraints_in_all_tables`, which can
be used to revalidate existing unique constraints. The different
variations support validation of a single constraint, validation of
all unique constraints in a table, and validation of all unique
constraints in all tables in the current database, respectively.
If any constraint fails validation, the functions will return an
error with a hint about which data caused the constraint violation.
These violations can then be resolved manually by updating or deleting
the rows in violation. This will be useful to users who think they may
have been affected by #73024.

75741: colfetcher: fix index join with low workmem limit when using Streamer r=yuzefovich a=yuzefovich

Previously, it was possible for an index join to run into "budget
exceeded" errors when using the Streamer API because we used the
hard-coded value of 4MiB for input batch size without paying attention
to possibly lower values of `distsql_workmem` limit. For example, we
could have buffered 3MiB worth of spans to be enqueued into the Streamer
that only has 1MiB of budget overall. This is now fixed by reducing the
input size limit to be a quarter of `distsql_workmem` limit. We don't
need to do a similar change to the row-by-row engine because there we
hard-code the workmem limit to be at least 8MiB.

Fixes: #75703

Release note: None

Co-authored-by: Rebecca Taft <[email protected]>
Co-authored-by: Yahor Yuzefovich <[email protected]>
@craig craig bot closed this as completed in 7ca432d Feb 2, 2022
rytaft added a commit to rytaft/cockroach that referenced this issue Feb 2, 2022
@rytaft
sql: add builtin functions to revalidate unique constraints
43565ef 
Fixes cockroachdb#73560

Release note (sql change): Added new builtin functions called
crdb_internal.revalidate_unique_constraint,
crdb_internal.revalidate_unique_constraints_in_table, and
crdb_internal.revalidate_unique_constraints_in_all_tables, which can
be used to revalidate existing unique constraints. The different
variations support validation of a single constraint, validation of
all unique constraints in a table, and validation of all unique
constraints in all tables in the current database, respectively.
If any constraint fails validation, the functions will return an
error with a hint about which data caused the constraint violation.
These violations can then be resolved manually by updating or deleting
the rows in violation. This will be useful to users who think they may
have been affected by cockroachdb#73024.
@rytaft rytaft mentioned this issue Feb 2, 2022
Merged
@mgartner mgartner moved this to Done in SQL Queries Jul 24, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@rytaft rytaft

Labels
A-sql-builtins SQL built-in functions and semantics thereof. C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) E-quick-win Likely to be a quick win for someone experienced. O-postmortem Originated from a Postmortem action item. T-sql-queries SQL Queries Team
Projects
SQL Queries
Archived in project
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

sql: add builtin functions to revalidate unique constraints rytaft/cockroach
2 participants
@rytaft @nvanbenschoten