Re: pg17 issues with not-null contraints

On 2024-May-06, Justin Pryzby wrote:

> > (Do you really want the partition to be
> > created without the primary key already there?)
> 
> Why not ?  The PK will be added when I attach it one moment later.
> 
> CREATE TABLE part (LIKE parent);
> ALTER TABLE parent ATTACH PARTITION part ...

Well, if you load data in the meantime, you'll spend time during `ALTER
TABLE parent` for the index to be created.  (On the other hand, you may
want to first create the table, then load data, then create the
indexes.)

> Do you really think that after ATTACH, the constraints should be
> different depending on whether the child was created INCLUDING INDEXES ?
> I'll continue to think about this, but I still find that surprising.

I don't think I have a choice about this, because the standard says that
the resulting table must have NOT NULL on all columns which have a
nullability characteristic is known not nullable; and the primary key
forces that to be the case.

Thinking again, maybe this is wrong in the opposite direction: perhaps
we should have not-null constraints on those columns even if INCLUDING
CONSTRAINTS is given, because failing to do that (which is the current
behavior) is non-conformant.  In turn, this suggests that in order to
make the partitioning behavior consistent, we should _in addition_ make
CREATE TABLE PARTITION OF add explicit not-null constraints to the
columns of the primary key of the partitioned table.

This would also solve your complaint, because then the table would have
the not-null constraint in all cases.

This might be taking the whole affair too far, though; not sure.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"We’ve narrowed the problem down to the customer’s pants being in a situation
 of vigorous combustion" (Robert Haas, Postgres expert extraordinaire)