-
Notifications
You must be signed in to change notification settings - Fork 3.7k
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.
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
sql: use the table descriptor ADD state correctly #20714
Comments
This problem will be resolved by the new schema lease mechanism which will lease the entire schema at a timestamp, thereby ensuring that a node sees the reference and the back-reference in the PUBLIC state. |
This seems quite real. See cockroach/pkg/sql/opt/optbuilder/mutation_builder_fk.go Lines 608 to 621 in 025c53d
|
|
The above analysis is barely relevant to the problem at hand. The above protocol might be useful in some other situations, but here I think we indeed need the optimizer to plan foreign key cascades to |
cc @mgartner |
A new table descriptor being added when referencing another table has to be added properly. An FK reference is implemented using two references (forward from new table to its FK reference, and backward from the FK referee to the new table). The forward reference is obvious, the backward reference on the other hand is used when updating the referee. Updating a referee table for example must ensure that it is not violating the FK reference. This becomes problematic if a node has cached a referee table without the backward reference and SQL is already putting data into the new table.
We solve this problem by placing a newly created table in the ADD state and then transition it into the PUBLIC state once all leased referee descriptors are using the backward link. A descriptor in the ADD state cannot be used to read/write to and so the table descriptor leasing logic disallows a node from holding a lease against such a table.
However one can have a situation where a referee sees a back reference to a table, the local node has a leased version of the new table in the ADD state, but the new table has transitioned to the PUBLIC state and another node has added data into the new table. Under the circumstances the referee must apply the backward FK checks it would normally apply.
This can be resolved by allowing the new table to be leased in the ADD state but ensure that only fk checks and cascade operations can use the descriptor. This can mean that the normal API still disallow returning descriptors in the ADD state while allowing them to be leased internally, and the FK operations use a separate API that allows grabbing references on leased descriptors in the ADD state.
Jira issue: CRDB-5914
The text was updated successfully, but these errors were encountered: