Feedback-ID: ia2694551:Fastmail
Date: Mon, 6 May 2024 17:56:54 +0200
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Robert Haas <robertmhaas@gmail.com>, 
	Kyotaro Horiguchi <horikyota.ntt@gmail.com>, pgsql-hackers@lists.postgresql.org, 
	Alexander Lakhin <exclusion@gmail.com>, Dmitry Koval <d.koval@postgrespro.ru>
Subject: Re: pg17 issues with not-null contraints
Message-ID: <202405061556.2bgkainuq2cs@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <202405040920.2rg4mmjgv3ch@alvherre.pgsql>
Archived-At: <https://www.postgresql.org/message-id/202405061556.2bgkainuq2cs%40alvherre.pgsql>
Precedence: bulk

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/