Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints - Mailing list pgsql-hackers

hi.
I played around with it.

current syntax, we don't need to deal with column constraint grammar.
like the following can fail directly:
create table t0(a int constraint nn not null a not valid);
we only support table constraint cases like:
alter table lp add constraint nc1 not null a not valid;

since CREATE TABLE with invalid constraint does not make sense, so
we can just issue a warning. like:
create table t0(a int, constraint nn not null a not valid);
WARNING:  CREATE TABLE NOT NULL NOT VALID CONSTRAINT WILL SET TO VALID
the wording needs to change...

for not null not valid syntax, we only need to support:
ALTER TABLE ADD CONSTRAINT conname NOT NULL column_name NOT VALID
ALTER TABLE ADD NOT NULL column_name NOT VALID
ALTER TABLE VALIDATE CONSTRAINT conname

The attached is what I came up with:
--------------------------------------------------------------------
ALTER TABLE ADD CONSTRAINT conname NOT NULL column_name NOT VALID
will create an invalidated check constraint. like
ALTER TABLE ADD CONSTRAINT conname CHECK (column_name IS NOT NULL) NOT VALID

when you validate the not-null constraint (internally it's a check constraint)
it will drop the check constraint and install a not-null constraint
with the same name.

drop a check constraint, it will call RemoveConstraintById.
within RemoveConstraintById it will lock pg_constraint.conrelid in
AccessExclusiveLock mode,
which is not ideal, because
ALTER TABLE VALIDATE CONSTRAINT only needs ShareUpdateExclusiveLock.
so we have to find a way to release that AccessExclusiveLock.

because we have converted a not-null constraint to a check constraint,
we need to somehow distinguish this case,
so pg_constraint adds another column: coninternaltype.
(the naming is not good, i guess)
because we dropped a invalid check constraint, but the inherited
constraint cannot be dropped.
so this ALTER TABLE VALIDATE CONSTRAINT will not work for partitions,
but it will work for the root partitioned table. (same logic for table
inheritance).
----------------------------------
demo:

create table t(a int);
alter table t add constraint nc1 not null a not valid;
\d t
                 Table "public.t"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 a      | integer |           |          |
Check constraints:
    "nc1" CHECK (a IS NOT NULL) NOT VALID

insert into t default values;
ERROR:  new row for relation "t" violates check constraint "nc1"
DETAIL:  Failing row contains (null).

alter table t validate constraint nc1;
\d+ t
                                            Table "public.t"
 Column |  Type   | Collation | Nullable | Default | Storage |
Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 a      | integer |           | not null |         | plain   |
    |              |
Not-null constraints:
    "nc1" NOT NULL "a"
Access method: heap

-------------------------------------------------------------------
some regress tests added.
need more polishing, but overall it works as the above described.

not sure if this idea is crazy or not,
what do you think?