Subject: Re: pg17 issues with not-null contraints

From: Alvaro Herrera <[email-masked]>
Date: Sat, 4 May 2024 11:20:32 +0200

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.

Does that make sense to you?

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"Estoy de acuerdo contigo en que la verdad absoluta no existe...
El problema es que la mentira sí existe y tu estás mintiendo" (G. Lama)

---

From: Alvaro Herrera <[email-masked]>
Date: Mon, 6 May 2024 17:56:54 +0200

> 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/