Re: pg17 issues with not-null contraints

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?)

Now maybe in https://www.postgresql.org/docs/devel/sql-createtable.html
we need some explanation for this. Right now we have

INCLUDING INDEXES
Indexes, PRIMARY KEY, UNIQUE, and EXCLUDE constraints on the original table
will be created on the new table. Names for the new indexes and constraints
are chosen according to the default rules, regardless of how the originals were
named. (This behavior avoids possible duplicate-name failures for the new
indexes.)

Maybe something like this before the naming considerations:
When creating a table like another that has a primary key and indexes
are excluded, a not-null constraint will be added to every column of
the primary key.

resulting in

INCLUDING INDEXES
Indexes, PRIMARY KEY, UNIQUE, and EXCLUDE constraints on the original table
will be created on the new table. [When/If ?] indexes are excluded while
creating a table like another that has a primary key, a not-null
constraint will be added to every column of the primary key.

Names for the new indexes and constraints are chosen according to
the default rules, regardless of how the originals were named. (This
behavior avoids possible duplicate-name failures for the new
indexes.)

What do you think?

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/