Re: pg17 issues with not-null contraints

On Mon, May 06, 2024 at 05:56:54PM +0200, Alvaro Herrera wrote:
> On 2024-May-04, Alvaro Herrera wrote:
> > On 2024-May-03, Justin Pryzby wrote:
> > 
> > > But if it's created with LIKE:
> > > postgres=# CREATE TABLE t1 (LIKE t);
> > > postgres=# ALTER TABLE t ATTACH PARTITION t1 DEFAULT ;
> > > 
> > > ..one also sees:
> > > 
> > > Not-null constraints:
> > >     "t1_i_not_null" NOT NULL "i"
> > 
> > Hmm, I think the problem here is not ATTACH; the not-null constraint is
> > there immediately after CREATE.  I think this is all right actually,
> > because we derive a not-null constraint from the primary key and this is
> > definitely intentional.  But I also think that if you do CREATE TABLE t1
> > (LIKE t INCLUDING CONSTRAINTS) then you should get only the primary key
> > and no separate not-null constraint.  That will make the table more
> > similar to the one being copied.
> 
> I misspoke -- it's INCLUDING INDEXES that we need here, not INCLUDING
> CONSTRAINTS ... and it turns out we already do it that way, so with this
> script
> 
> CREATE TABLE t (i int PRIMARY KEY) PARTITION BY RANGE (i);
> CREATE TABLE t1 (LIKE t INCLUDING INDEXES);
> ALTER TABLE t ATTACH PARTITION t1 DEFAULT ;
> 
> you end up with this
> 
> 55432 17devel 71313=# \d+ t
>                                       Partitioned table "public.t"
>  Column │  Type   │ Collation │ Nullable │ Default │ Storage │ Compression │ Stats target │ Description 
> ────────┼─────────┼───────────┼──────────┼─────────┼─────────┼─────────────┼──────────────┼─────────────
>  i      │ integer │           │ not null │         │ plain   │             │              │ 
> Partition key: RANGE (i)
> Indexes:
>     "t_pkey" PRIMARY KEY, btree (i)
> Partitions: t1 DEFAULT
> 
> 55432 17devel 71313=# \d+ t1
>                                            Table "public.t1"
>  Column │  Type   │ Collation │ Nullable │ Default │ Storage │ Compression │ Stats target │ Description 
> ────────┼─────────┼───────────┼──────────┼─────────┼─────────┼─────────────┼──────────────┼─────────────
>  i      │ integer │           │ not null │         │ plain   │             │              │ 
> Partition of: t DEFAULT
> No partition constraint
> Indexes:
>     "t1_pkey" PRIMARY KEY, btree (i)
> Access method: heap
> 
> which I think is what you want.  (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 ...

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.

-- 
Justin