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: support adding a column with a check constraint in same txn #33593

Closed
thoszhang opened this issue Jan 9, 2019 · 1 comment
Closed

sql: support adding a column with a check constraint in same txn #33593

thoszhang opened this issue Jan 9, 2019 · 1 comment
Assignees
@thoszhang
Labels
A-schema-changes

Comments

@thoszhang
Copy link
Contributor

Currently, adding a column and adding a check constraint on that column within the same transaction is not supported:

[email protected]:60903/defaultdb> BEGIN;
                             -> ALTER TABLE t ADD COLUMN b INT DEFAULT (INT '0');
                             -> ALTER TABLE t ADD CONSTRAINT c CHECK (b > 0);
                             -> COMMIT;
pq: column "b" not found for constraint "b"

We should support adding a constraint on the column that's being added. Additionally, we should validate the new constraint on the new column's default values, if applicable (as in the example above), and fail the transaction if validation fails.

This issue is being filed to track progress on #32504 (see the new logic tests for more examples). #33550 is a related issue.
@thoszhang thoszhang self-assigned this Jan 9, 2019
thoszhang pushed a commit to eriktrinh/cockroach that referenced this issue Jan 9, 2019
@lucy-zhang
sql: run check constraint mutations and validation by schema changer
296e932 
This change moves check constraint adds and drops through the schema
changer when the transaction commits, moving the constraint through the
same intermediate states as when index/columns are added or dropped. The
only small differences are:
 - Constraint adds can start in write-only and immediately start being
   enforced if all used columns are in write-only or public.
 - Constraint drops can move immediately from public to absent if they
   have not yet been validated.

Therefore, the check constraint is no longer immediately public when it
is added, but allows data validation of the constraint to be performed
when the constraint is added (and is now the default behaviour).
Constraints can now also be added on columns in the process of being
added.

This change also ensures that there are no data anomalies in either
versions of the schema when dropping a validated check constraint, as
previously the transition moved the constraint from public -> absent.
Writes on the new version were not being checked even though nodes on an
older version expect all rows to conform to the constraint.

Fixes cockroachdb#33593.
See also cockroachdb#33550.

Release note (sql change): Check constraint adds by default will
validate table data with the added constraint asynchronously after the
transaction commits.
thoszhang pushed a commit to eriktrinh/cockroach that referenced this issue Jan 10, 2019
@lucy-zhang
sql: run check constraint mutations and validation by schema changer
1762913 
This change moves check constraint adds and drops through the schema
changer when the transaction commits, moving the constraint through the
same intermediate states as when index/columns are added or dropped. The
only small differences are:
 - Constraint adds can start in write-only and immediately start being
   enforced if all used columns are in write-only or public.
 - Constraint drops can move immediately from public to absent if they
   have not yet been validated.

Therefore, the check constraint is no longer immediately public when it
is added, but allows data validation of the constraint to be performed
when the constraint is added (and is now the default behaviour).
Constraints can now also be added on columns in the process of being
added.

This change also ensures that there are no data anomalies in either
versions of the schema when dropping a validated check constraint, as
previously the transition moved the constraint from public -> absent.
Writes on the new version were not being checked even though nodes on an
older version expect all rows to conform to the constraint.

Fixes cockroachdb#33593.
See also cockroachdb#33550.

Release note (sql change): Check constraint adds by default will
validate table data with the added constraint asynchronously after the
transaction commits.
@vivekmenezes
Copy link
Contributor

#34301

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@thoszhang thoszhang

Labels
A-schema-changes
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@thoszhang @vivekmenezes