cockroachdb
schemachanger: dropping a column with a constraint fails concurrent DML
If a column is referenced in a constraint (e.g. check, unique-without-index), then dropping that column (which requires dropping of the constraint as well) currently fails concurrent DML stmts.
A simple repro with a check constraint in cockroach demo would be
create table t (i int primary key, j int not null);
SET CLUSTER SETTING jobs.debug.pausepoints = 'schemachanger.root.$ cockroach demo.0';
set cluster setting jobs.registry.interval.adopt = "2s";
set cluster setting jobs.registry.interval.cancel = "2s";
alter table t drop column j;
insert into t values (0);
ERROR: failed to satisfy CHECK constraint (crdb_internal_column_2_name_placeholder IS NOT NULL): column "crdb_internal_column_2_name_placeholder" does not exist, referenced in "crdb_internal_column_2_name_placeholder IS NOT NULL"
SQLSTATE: 42703
CONSTRAINT: crdb_internal_column_2_name_placeholder_auto_not_null
The culprit is that we transition the column to WRITE_ONLY in the PrecommitPhase while the constraint is still in an enforceable state (i.e. the constraint is in WRITE_ONLY). This means we will need to check whether this DML (e.g. insert) satisfy the check constraint but the check constraint references a column that's no longer public (i.e. that column is under mutation in WRITE_ONLY state) so the optimizer screams.
Very related to some recent discuss in https://github.com/cockroachdb/cockroach/issues/111619 where it seems clearer and clearer that we need to make the column invisible to users but still public/read-able for optimizer's operations (or "internal operations"). That idea would solve this issue as well so that while the column is "invisible" to the user, it's still "readable", and for all intents and purposes, "public" from the eyes of the optimizer.
Jira issue: CRDB-35676
Epic CRDB-35306
I had more thoughts and I start to think that this is not a Optimizer issue but rather it's just a error formatting issue. After all, the error msg is actually correct "failed to satisfy the CHECK constraint ..."; it's just the inner error msg is a bit confusing. To that end, I created #118987 to return a cleaner error msg.
