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

hi.
you may like the attached. it's based on your idea: attnotnullvalid.

I came across a case, not sure if it's a bug.
CREATE TABLE ttchk (a INTEGER);
ALTER TABLE ttchk ADD CONSTRAINT cc check (a is NOT NULL) NOT VALID;
CREATE TABLE ttchk_child(a INTEGER) INHERITS(ttchk);
ttchk_child's constraint cc will default to valid,
but pg_dump && pg_restore will make ttchk_child's constraint invalid.
since it's an existing behavior, so not-null constraint will align with it.
--------------------------------------------------------------------
-----the following text is copied from the commit message------------

NOT NULL NOT VALID

* TODO: In doc/src/sgml/ref/alter_table.sgml, under the
<title>Compatibility</title> section,
  clarify how the "NOT NULL NOT VALID" syntax conforms with the standard.
* TODO: Should CREATE TABLE LIKE copy an existing invalid not-null
constraint to the new table,
  and if so, the new table's not-null will be marked as valid.

description entry of pg_attribute.attnotnullvalid:
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>attnotnullvalid</structfield> <type>bool</type>
+      </para>
+      <para>
+       The not-null constraint validity status of the column.
+       If true, it means this column has a valid not-null constraint,
+       false means this column doesn't have a not-null constraint or
has an unvalidated one.
+       If <structfield>attnotnull</structfield> is false, this must be false.
       </para></entry>

* attnotnull means that a not-null constraint exists; it doesn't imply anything
  regarding the constraint being valid or not.
  attnotnullvalid will indicate whether the constraint is valid; this column can
  only be true if attnotnull is already true.
  attnotnullvalid only added to FormData_pg_attribute, didn't add to
CompactAttribute.
  mainly because invalid not-null is not being commonly used.
  TupleDesc->TupleConstr->has_not_null now also represents invalid not-null
  constraint.

* For table in pg_catalog schema, if that column attnotnull attribute is true,
 then attnotnullvalid attribute is also true.  Similarly, if
attnotnull is false,
 then attnotnullvalid is false.  I added an SQL check at the end of
 src/test/regress/sql/constraints.sql (not sure it's necessary)

* CREATE TABLE specifying not valid not-null constraint will be set to valid,
  a warning is issued within function transformCreateStmt.
  that means InsertPgAttributeTuples can not insert attribute
  that is (attnotnull && !attnotnullvalid).
 I added an Assert in InsertPgAttributeTuples.
(also added to other places, to demo i didn't mess something, maybe
it's necessary).

* table rewrite won't validate invalid not-null constraint, that is aligned
  with check constraint.

* attnotnullvalid mainly changed in these two places:
  1. ATAddCheckNNConstraint, if you specified "NOT NULL NOT VALID", it
will change
    it from false to false, but will set attnotnull to true.
  2. QueueNNConstraintValidation, subroutine of ATExecValidateConstraint.
    when validing an not valid not-null constraint, toggle it from
false to true,
    also set attnotnull to true.

* A partitioned table can have an invalid NOT NULL constraint while its
  partitions have a valid one, but not the other way around.
  but pg_dump/pg_restore may not preserve the constraint name properly, but
  that's fine for not-null constraint, i think.

* regular table invalid not null constraint pg_dump also works fine.