Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

On 2025-Mar-28, jian he wrote:

> ATPrepAddPrimaryKey
> + if (!conForm->convalidated)
> + ereport(ERROR,
> + errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
> + errmsg("not-null constraint \"%s\" of table \"%s\" has not been validated",
> + NameStr(conForm->conname),
> + RelationGetRelationName(rel)),
> + errhint("You will need to use ALTER TABLE ... VALIDATE CONSTRAINT to
> validate it."));
>
> I think the error message is less helpful.
> Overall, I think we should say that:
> to add the primary key on column x requires a validated not-null
> constraint on column x.

I think you're right that this isn't saying what the problem is; we
should be saying something like

ERROR: cannot add primary key because of invalid not-null constraint "the_constr"
HINT: You will need to use ALTER TABLE .. VALIDATE CONSTRAINT to validate it.

> ------------------------------------------------------------------------
> i think your patch messed up with pg_constraint.conislocal.
> for example:
>
> CREATE TABLE parted (id bigint default 1,id_abc bigint) PARTITION BY LIST (id);
> alter TABLE parted add CONSTRAINT dummy_constr not null id not valid;
> CREATE TABLE parted_1 (id bigint default 1,id_abc bigint);
> alter TABLE parted_1 add CONSTRAINT dummy_constr not null id;
> ALTER TABLE parted ATTACH PARTITION parted_1 FOR VALUES IN ('1');

It's still not clear to me what to do to fix this problem. But while
trying to understand it, I had the chance to rework the pg_dump code
somewhat, so here it is. Feel free to propose fixes on top of this.
(BTW, I think the business of assigning to tbinfo->checkexprs both the
block for check constraints and the one for not-null constraints is
bogus. I didn't find what this breaks, but it looks wrong. We probably
need another struct _constraintInfo pointer in TableInfo.)

--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"¿Cómo puedes confiar en algo que pagas y que no ves,
y no confiar en algo que te dan y te lo muestran?" (Germán Poo)