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