create partitioned table with (like table INCLUDING ALL ) fails with "insufficient columns in UNIQUE constraint definition" - Mailing list pgsql-bugs
| From | Stuart |
|---|---|
| Subject | create partitioned table with (like table INCLUDING ALL ) fails with "insufficient columns in UNIQUE constraint definition" |
| Date | |
| Msg-id | 1848484.oBYbUDFMh6@station53.ousa.org Whole thread Raw |
| Responses |
Re: create partitioned table with (like table INCLUDING ALL ) failswith "insufficient columns in UNIQUE constraint definition"
|
| List | pgsql-bugs |
Dear team,
Another issue found with attempting to create partitioned table from (like table including all). Primary key constraints don't get recognized if there are other unique constraints and indexes in the original table. Creating a non-partitioned table works ok using the same options.
=# \d+ knowledge
Table "public.knowledge"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------------+-----------------------------+-----------+----------+-----------------------------------+----------+--------------+-------------
entry_date | timestamp(3) with time zone | | not null | now() | plain | |
revision_date | timestamp(3) with time zone | | | NULL::timestamp with time zone | plain | |
entered_by | text | | not null | "current_user"() | extended | |
revised_by | text | | | ''::text | extended | |
source_id | bigint | | | | plain | |
object_id | bigint | | not null | nextval('resource_seq'::regclass) | plain | |
description | text | | | ''::text | extended | |
category_id | bigint | | | | plain | |
producer_id | bigint | | not null | | plain | |
released | date | | | date(now()) | plain | |
copyprotected | date | | | | plain | |
isindexed | boolean | | | false | plain | |
filepath | text | | | ''::text | extended | |
url | text | | | ''::text | extended | |
Indexes:
"knowledge_pk" PRIMARY KEY, btree (object_id), tablespace "pgindex"
"knowledge_categoryfilepathurl_un" UNIQUE, btree (category_id, filepath, url) WHERE filepath IS NULL AND url IS NULL, tablespace "pgindex"
"knowledge_filepath_un" UNIQUE CONSTRAINT, btree (filepath), tablespace "pgindex"
"knowledge_url_un" UNIQUE CONSTRAINT, btree (url), tablespace "pgindex"
Inherits: products
=# create table knowledge_new (like knowledge INCLUDING ALL ) PARTITION BY RANGE ( object_id ) ;
ERROR: insufficient columns in UNIQUE constraint definition
DETAIL: UNIQUE constraint on table "knowledge_new" lacks column "object_id" which is part of the partition key.
=# create table knowledge_new (like knowledge INCLUDING ALL ) PARTITION BY RANGE ( object_id ) ;
ERROR: insufficient columns in UNIQUE constraint definition
DETAIL: UNIQUE constraint on table "knowledge_new" lacks column "object_id" which is part of the partition key.
=# alter table knowledge drop constraint knowledge_url_un ;
ALTER TABLE
=# create table knowledge_new (like knowledge INCLUDING ALL ) PARTITION BY RANGE ( object_id ) ;
ERROR: insufficient columns in UNIQUE constraint definition
DETAIL: UNIQUE constraint on table "knowledge_new" lacks column "object_id" which is part of the partition key.
=# alter table knowledge drop constraint knowledge_filepath_un ;
ALTER TABLE
=# create table knowledge_new (like knowledge INCLUDING ALL ) PARTITION BY RANGE ( object_id ) ;
ERROR: insufficient columns in UNIQUE constraint definition
DETAIL: UNIQUE constraint on table "knowledge_new" lacks column "object_id" which is part of the partition key.
=# drop index knowledge_categoryfilepathurl_un ;
DROP INDEX
=# \d+ knowledge
Table "public.knowledge_old"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------------+-----------------------------+-----------+----------+-----------------------------------+----------+--------------+-------------
entry_date | timestamp(3) with time zone | | not null | now() | plain | |
revision_date | timestamp(3) with time zone | | | NULL::timestamp with time zone | plain | |
entered_by | text | | not null | "current_user"() | extended | |
revised_by | text | | | ''::text | extended | |
source_id | bigint | | | | plain | |
object_id | bigint | | not null | nextval('resource_seq'::regclass) | plain | |
description | text | | | ''::text | extended | |
category_id | bigint | | | | plain | |
producer_id | bigint | | not null | | plain | |
released | date | | | date(now()) | plain | |
copyprotected | date | | | | plain | |
isindexed | boolean | | | false | plain | |
filepath | text | | | ''::text | extended | |
url | text | | | ''::text | extended | |
Indexes:
"knowledge_pk" PRIMARY KEY, btree (object_id), tablespace "pgindex"
Inherits: products
=# create table knowledge_new (like knowledge INCLUDING ALL ) PARTITION BY RANGE ( object_id ) ;
CREATE TABLE
ousa_new=# \d+ knowledge_new
Table "public.knowledge_new"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------------+-----------------------------+-----------+----------+-----------------------------------+----------+--------------+-------------
entry_date | timestamp(3) with time zone | | not null | now() | plain | |
revision_date | timestamp(3) with time zone | | | NULL::timestamp with time zone | plain | |
entered_by | text | | not null | "current_user"() | extended | |
revised_by | text | | | ''::text | extended | |
source_id | bigint | | | | plain | |
object_id | bigint | | not null | nextval('resource_seq'::regclass) | plain | |
description | text | | | ''::text | extended | |
category_id | bigint | | | | plain | |
producer_id | bigint | | not null | | plain | |
released | date | | | date(now()) | plain | |
copyprotected | date | | | | plain | |
isindexed | boolean | | | false | plain | |
filepath | text | | | ''::text | extended | |
url | text | | | ''::text | extended | |
Partition key: RANGE (object_id)
Indexes:
"knowledge_new_pkey" PRIMARY KEY, btree (object_id), tablespace "pgindex"
Number of partitions: 0
Tested creating a normal table using the same syntax, and it works.
=# \d+ knowledge
Table "public.knowledge"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------------+-----------------------------+-----------+----------+-----------------------------------+----------+--------------+-------------
entry_date | timestamp(3) with time zone | | not null | now() | plain | |
revision_date | timestamp(3) with time zone | | | NULL::timestamp with time zone | plain | |
entered_by | text | | not null | "current_user"() | extended | |
revised_by | text | | | ''::text | extended | |
source_id | bigint | | | | plain | |
object_id | bigint | | not null | nextval('resource_seq'::regclass) | plain | |
description | text | | | ''::text | extended | |
category_id | bigint | | | | plain | |
producer_id | bigint | | not null | | plain | |
released | date | | | date(now()) | plain | |
copyprotected | date | | | | plain | |
isindexed | boolean | | | false | plain | |
filepath | text | | | ''::text | extended | |
url | text | | | ''::text | extended | |
Indexes:
"knowledge_pk" PRIMARY KEY, btree (object_id), tablespace "pgindex"
"knowledge_categoryfilepathurl_un" UNIQUE, btree (category_id, filepath, url) WHERE filepath IS NULL AND url IS NULL, tablespace "pgindex"
"knowledge_filepath_un" UNIQUE CONSTRAINT, btree (filepath), tablespace "pgindex"
"knowledge_url_un" UNIQUE CONSTRAINT, btree (url), tablespace "pgindex"
=# create table knowledge_test (like knowledge including all ) ;
CREATE TABLE
pgsql-bugs by date: