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: add unique constraint checks to SCRUB #73632

Closed
Tracked by #77439
rytaft opened this issue Dec 9, 2021 · 0 comments · Fixed by #78297
Closed
Tracked by #77439

sql: add unique constraint checks to SCRUB #73632

rytaft opened this issue Dec 9, 2021 · 0 comments · Fixed by #78297
Assignees
@rharding6373
Labels
A-sql-scrub C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) O-postmortem Originated from a Postmortem action item. T-sql-queries SQL Queries Team

Comments

@rytaft
Copy link
Collaborator

rytaft commented Dec 9, 2021 •
edited by cockroach-jira-scripts
Loading

The SCRUB tool checks that foreign key and check constraints on a table are valid and that there are no rows violating these constraints. It does not check for unique constraint violations, since in the past these constraints have been enforced by UNIQUE indexes. However, as of 21.1, this is no longer the case. For REGIONAL BY ROW tables, tables with implicitly partitioned UNIQUE indexes, and tables with UNIQUE WITHOUT INDEX constraints, the constraints are enforced by checks added by the optimizer when planning mutation queries. If there is a bug in the optimizer, as was the case in #73024, these constraints may be violated. SCRUB should be updated to check that these UNIQUE constraints are not violated.

Jira issue: CRDB-11674
@rytaft rytaft added C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) A-sql-scrub O-postmortem Originated from a Postmortem action item. labels Dec 9, 2021
@blathers-crl blathers-crl bot added the T-sql-queries SQL Queries Team label Dec 9, 2021
@rytaft rytaft mentioned this issue Dec 17, 2021
Closed
@rytaft rytaft assigned rharding6373 and unassigned rytaft Jan 28, 2022
@rytaft rytaft mentioned this issue Mar 7, 2022
Open
20 tasks
rharding6373 added a commit to rharding6373/cockroach that referenced this issue Mar 22, 2022
@rharding6373
sql: adds unique constraint checks to SCRUB
060077d 
The SCRUB tool validates table constraints. Before this change, it could
only validate `CONSTRAINT` and foreign key constraints. This PR enhances
SCRUB to also validate tables that contain `PRIMARY KEY`, `UNIQUE`, or
the experimental `UNIQUE WITHOUT INDEX` keywords.

Fixes: cockroachdb#73632

Release note (sql): This PR expands the capabilities of the experimental
SCRUB tool to include checking unique constraints for primary keys,
unique indexes, and unique columns without indexes. The usage and output
of SCRUB is unchanged, but if there is a unique constraint violation,
users will see the error message `unique_constraint_violation` for all
rows that violate the constraint, along with information about the row.

Release justification:
@rharding6373 rharding6373 mentioned this issue Mar 22, 2022
Merged
rharding6373 added a commit to rharding6373/cockroach that referenced this issue Mar 25, 2022
@rharding6373
sql: adds unique constraint checks to SCRUB
c5c5997 
The SCRUB tool validates table constraints. Before this change, it could
only validate `CONSTRAINT` and foreign key constraints. This PR enhances
SCRUB to also validate tables that contain `PRIMARY KEY`, `UNIQUE`, or
the experimental `UNIQUE WITHOUT INDEX` keywords.

Fixes: cockroachdb#73632

Release note (sql): This PR expands the capabilities of the experimental
SCRUB tool to include checking unique constraints for primary keys,
unique indexes, and unique columns without indexes. The usage and output
of SCRUB is unchanged, but if there is a unique constraint violation,
users will see the error message `unique_constraint_violation` for all
rows that violate the constraint, along with information about the row.
rharding6373 added a commit to rharding6373/cockroach that referenced this issue Mar 28, 2022
@rharding6373
sql: adds unique constraint checks to SCRUB
d7333dc 
The SCRUB tool validates table constraints. Before this change, it could
only validate `CHECK` and foreign key constraints. This PR enhances
SCRUB to also validate tables that contain `PRIMARY KEY`, `UNIQUE`, or
the experimental `UNIQUE WITHOUT INDEX` keywords.

Fixes: cockroachdb#73632

Release note (sql change): This PR expands the capabilities of the experimental
SCRUB tool to include checking unique constraints for primary keys,
unique indexes, and unique columns without indexes. The usage and output
of SCRUB is unchanged, but if there is a unique constraint violation,
users will see the error message `unique_constraint_violation` for all
rows that violate the constraint, along with information about the row.
rharding6373 added a commit to rharding6373/cockroach that referenced this issue May 20, 2022
@rharding6373
sql: adds unique constraint checks to SCRUB
9731e5c 
The SCRUB tool validates table constraints. Before this change, it could
only validate `CHECK` and foreign key constraints. This PR enhances
SCRUB to also validate tables that contain `PRIMARY KEY`, `UNIQUE`, or
the experimental `UNIQUE WITHOUT INDEX` keywords.

Fixes: cockroachdb#73632

Release note (sql change): This PR expands the capabilities of the experimental
SCRUB tool to include checking unique constraints for primary keys,
unique indexes, and unique columns without indexes. The usage and output
of SCRUB is unchanged, but if there is a unique constraint violation,
users will see the error message `unique_constraint_violation` for all
rows that violate the constraint, along with information about the row.
rharding6373 added a commit to rharding6373/cockroach that referenced this issue May 24, 2022
@rharding6373
sql: adds unique constraint checks to SCRUB
5250a7e 
The SCRUB tool validates table constraints. Before this change, it could
only validate `CHECK` and foreign key constraints. This PR enhances
SCRUB to also validate tables that contain `PRIMARY KEY`, `UNIQUE`, or
the experimental `UNIQUE WITHOUT INDEX` keywords.

Fixes: cockroachdb#73632

Release note (sql change): This PR expands the capabilities of the experimental
SCRUB tool to include checking unique constraints for primary keys,
unique indexes, and unique columns without indexes. The usage and output
of SCRUB is unchanged, but if there is a unique constraint violation,
users will see the error message `unique_constraint_violation` for all
rows that violate the constraint, along with information about the row.
craig bot pushed a commit that referenced this issue May 25, 2022
@rharding6373
Merge #78297
c86696e 
78297: sql: adds unique constraint checks to SCRUB r=rharding6373 a=rharding6373

The SCRUB tool validates table constraints. Before this change, it could
only validate `CHECK` and foreign key constraints. This PR enhances
SCRUB to also validate tables that contain `PRIMARY KEY`, `UNIQUE`, or
the experimental `UNIQUE WITHOUT INDEX` keywords.

Fixes: #73632

Release note (sql change): This PR expands the capabilities of the experimental
SCRUB tool to include checking unique constraints for primary keys,
unique indexes, and unique columns without indexes. The usage and output
of SCRUB is unchanged, but if there is a unique constraint violation,
users will see the error message `unique_constraint_violation` for all
rows that violate the constraint, along with information about the row.



Co-authored-by: rharding6373 <[email protected]>
@craig craig bot closed this as completed in a6567e3 May 25, 2022
@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

@rharding6373 rharding6373

Labels
A-sql-scrub C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) 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: adds unique constraint checks to SCRUB rharding6373/cockroach
2 participants
@rytaft @rharding6373