Re: not null constraints, again

On Sat, Sep 21, 2024 at 5:15 AM Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
>
> Okay, so here is v4 with these problems fixed, including correct
> propagation of constraint names to children tables, which I had
> inadvertently broken earlier. This one does pass the pg_upgrade tests
> and as far as I can see pg_dump does all the correct things also. I
> cleaned up the tests to remove everything that's unneeded, redundant, or
> testing behavior that no longer exists.
>

in findNotNullConstraintAttnum
if (con->contype != CONSTRAINT_NOTNULL)
continue;
if (!con->convalidated)
continue;

if con->convalidated is false, then we have a bigger problem?
maybe we can change to ERROR to expose/capture potential problems.
like:
if (con->contype != CONSTRAINT_NOTNULL)
continue;
if (!con->convalidated)
elog(ERROR, "not-null constraint is not validated");

------<<<<<<<<------------------
HeapTuple
findNotNullConstraint(Oid relid, const char *colname)
{
AttrNumber attnum = get_attnum(relid, colname);
return findNotNullConstraintAttnum(relid, attnum);
}

we can change to

HeapTuple
findNotNullConstraint(Oid relid, const char *colname)
{
AttrNumber attnum = get_attnum(relid, colname);
if (attnum <= InvalidAttrNumber)
return NULL;
return findNotNullConstraintAttnum(relid, attnum);
}
------<<<<<<<<------------------

sql-createtable.html
SECTION: LIKE source_table [ like_option ... ]
INCLUDING CONSTRAINTS
CHECK constraints will be copied. No distinction is made between
column constraints and table constraints. Not-null constraints are
always copied to the new table.

drop table if exists t, t_1,ssa;
create table t(a int, b int, not null a no inherit);
create table ssa (like t INCLUDING all);

Here create table like won't include no inherit not-null constraint,
seems to conflict with the doc?

------<<<<<<<<------------------
drop table if exists t, t_1;
create table t(a int primary key, b int, not null a no inherit);
create table t_1 () inherits (t);

t_1 will inherit the not-null constraint from t,
so the syntax "not null a no inherit" information is ignored.

other cases:
create table t(a int not null, b int, not null a no inherit);
create table t(a int not null no inherit, b int, not null a);

seems currently, column constraint have not-null constraint, then use
it and table constraint (not-null)
are ignored.
but if column constraint don't have not-null then according to table constraint.