Re: pg17 issues with not-null contraints - Mailing list pgsql-hackers
From | Alvaro Herrera |
---|---|
Subject | Re: pg17 issues with not-null contraints |
Date | |
Msg-id | 202405061556.2bgkainuq2cs@alvherre.pgsql Whole thread Raw |
In response to | Re: pg17 issues with not-null contraints (Alvaro Herrera <alvherre@alvh.no-ip.org>) |
Responses |
Re: pg17 issues with not-null contraints
|
List | pgsql-hackers |
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/
pgsql-hackers by date: