Thanks. While I agree we shouldn’t be applying these constraints post hoc on read or compaction, I think we need to make clear to the user whether we are validating a new constraint before accepting it for alter table. Which is to say I think alter table should require something like “NO VERIFY” or some other additional keywords to make clear we aren’t checking the constraint applies to existing data.
On 10 Feb 2025, at 15:10, Bernardo Botella <conta...@bernardobotella.com> wrote:
Hi. These was a topic we discussed during the ML thread:
Here was one of my answers on that: "Note: This constraints are only enforced at write time. So, an ALTER CONSTRAINT with more restrictive constraints shouldn’t affect preexisting data.”
Long story short, constraints are only checked at write time. If a constraint is added to a table with preexisting offending data, that data stays untouched.
I hope this helps, Bernardo
On Feb 10, 2025, at 7:00 AM, Benedict <bened...@apache.org> wrote:
This is counterintuitive to me. The constraint should be applied to the table, not to the update. NOT NULL should imply a value is always specified. How are you handling this for tables that already exist? Can we alter table to add constraints, and if so what are the semantics? On 10 Feb 2025, at 14:50, Bernardo Botella <conta...@bernardobotella.com> wrote:
Hi everyone,
Stefan Miklosovic and I have been working on a NOT_NULL (https://github.com/apache/cassandra/pull/3867) constraint to be added to the constraints tool belt, and a really interesting conversation came up.
First, as a problem statement, let's consider this:
-----------------------------------------
CREATE TABLE ks.tb2 (
id int,
cl1 int,
cl2 int,
val text CHECK NOT_NULL(val),
PRIMARY KEY (id, cl1, cl2)
)
cassandra@cqlsh> INSERT INTO ks.tb2 (id, cl1, cl2, val) VALUES ( 1, 2, 3, null);
InvalidRequest: Error from server: code=2200 [Invalid query] message="Column value does not satisfy value constraint for column 'val' as it is null."
cassandra@cqlsh> INSERT INTO ks.tb2 (id, cl1, cl2, val) VALUES ( 1, 2, 3, “text");
cassandra@cqlsh> select * from ks.tb2;
id | cl1 | cl2 | val
----+-----+-----+------
1 | 2 | 3 | text
(1 rows)
cassandra@cqlsh> INSERT INTO ks.tb2 (id, cl1, cl2) VALUES ( 1, 2, 4);
cassandra@cqlsh> select * from ks.tb2;
id | cl1 | cl2 | val
----+-----+-----+------
1 | 2 | 3 | text
1 | 2 | 4 | null
-----------------------------------------
As you see, we have a hole in which a 'null' value is getting written on column val even if we have a NOT_NULL on that particular column whenever the column is NOT specified on the write. That raises the question on how this particular constraint should behave.
If we consider the other constraints (scalar constraint and length constraint so far), this particular behavior is fine. But, if the constraint is NOT_NULL, then it becomes a little bit trickier.
The conclusions we have reached is that the meaning of constraints should be interpreted like: I check whatever you give me as part of the write, ignoring everything else. Let me elaborate:
If we decide to treat this particular NOT_NULL constraint differently, and check if the value for that column is present in the insert statement, we then open a different can of worms. What happens if the row already exists with a valid value, and that insert statement is only trying to do an update to a different column in the row? If that was the case, we would be forcing the user to specify the 'val' column value for every update, even if it is not needed.
Mainly for this reason, we think it is better to treat this NOT_NULL constraint just like the other constraints, and execute it ONLY on the values that are present on the insert statement.
The main con is that it may lead to a little bit of confussion (as in, why I just added a null value to the table even if I have a NOT_NULL constraint?). We have thought on aliviating this particular confusion by:
- Extensive documentation. Let's be upfront on what this constraint does and does not. (https://github.com/apache/cassandra/blob/ed58c404e8c880b69584e71a3690d3d9f73ef9fa/doc/modules/cassandra/pages/developing/cql/constraints.adoc#not_null-constraint)
- Adding, as part of this patch, yet another constraint (STRICTLY_NOT_NULL), that checks for the actual column value to be present in the insert statement..
If you've made it until here, that means you are really interested in constraints. Thanks! The question for you is, would you have any concern with this approach?
Thanks,
Bernardo
|
