Re: odd behaviour with serial, non null and partitioned table

Hello,

On 2023-Oct-17, Ashutosh Bapat wrote:

> Problem 1
> ========
> #create table tpart (a serial primary key, src varchar) partition by range(a);
> CREATE TABLE
> #create table t_p4 (a int primary key, src varchar);
> CREATE TABLE

> But tparts NOT NULL constraint is recorded in pg_constraint but not
> t_p4's. Is this expected?

Yes. tpart gets it from SERIAL, which implicitly requires a NOT NULL
marker. If you just say PRIMARY KEY as you did for t_p4, the column
gets marked attnotnull, but there's no explicit NOT NULL constraint.

> Here's what I was trying to do actually.
> #alter table tpart attach partition t_p4 for values from (7) to (9);
> ERROR: column "a" in child table must be marked NOT NULL
> This is a surprise since t_p4.a is marked as NOT NULL. That happens
> because MergeConstraintsIntoExisting() only looks at pg_constraint and
> not pg_attribute. Should this function look at pg_attribute as well?

Hmm ... well, not that way. Maybe attaching a partition should cause a
NOT NULL constraint to spawn automatically (we do this in other cases).
There's no need to verify the existing rows for it, since attnotnull is
already checked; but it would mean that if you DETACH the partition, the
constraint would remain, so the table would dump slightly differently
than if you hadn't ATTACHed and DETACHed it. But that sounds OK to me.

--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
Thou shalt check the array bounds of all strings (indeed, all arrays), for
surely where thou typest "foo" someone someday shall type
"supercalifragilisticexpialidocious" (5th Commandment for C programmers)