[MDEV-16356] Allow ALGORITHM=NOCOPY for ADD CONSTRAINT Created: 2018-05-31 Updated: 2024-01-18 |
|
| Status: | Open |
| Project: | MariaDB Server |
| Component/s: | Storage Engine - InnoDB |
| Fix Version/s: | None |
| Type: | Task | Priority: | Major |
| Reporter: | Marko Mäkelä | Assignee: | Oleksandr Byelkin |
| Resolution: | Unresolved | Votes: | 2 |
| Labels: | CONSTRAINT, foreign-keys, online-ddl | ||
| Issue Links: |
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Description |
|
The ADD FOREIGN KEY and ADD CHECK constraints currently require the table to be rebuilt with ALGORITHM=COPY. We could support these operations instantly if the user specified a WITHOUT VALIDATION clause. We currently do support instant ADD FOREIGN KEY after SET foreign_key_checks=0, but there is no such mechanism for ADD CONSTRAINT CHECK. When validation is not suppressed, we should only scan the table and validate the added constraints. There is no need to write any data to the table. For LOCK=NONE operation, we may need to ‘subscribe’ to concurrent modifications somehow. |
| Comments |
| Comment by Alexander Barkov [ 2023-03-24 ] |
|
This feature would solve the problem described in MDEV-30669 (TEXT to JSON conversion). |
| Comment by Marko Mäkelä [ 2023-03-24 ] |
|
I think that this is easiest to implement in the SQL layer. For checking FOREIGN KEY constraints, some type of join (outer join?) will be necessary. For anything else (CHECK or JSON validity constraints or adding a NOT NULL attribute to a column in a ROW_FORMAT=REDUNDANT table), a plain table scan would do. In the storage engine, this can be facilitated by the same APIs that serve regular SELECT and joins. |