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)