create partitioned table with (like table INCLUDING ALL ) fails with "insufficient columns in UNIQUE constraint definition"
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"
=# create table knowledge_test (like knowledge including all ) ;
On 2018/12/13 6:08, Stuart wrote:
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.
I don't think this is a bug. It's rather a current *limitation* of
defining UNIQUE constraints on partitioned tables that they cannot be
created without including the partition key.
Thanks,
Amit
On 2018 Zvita 13, China 12:14:33 +03 Amit Langote wrote:
On 2018/12/13 6:08, Stuart wrote:
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.knowl
edge"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.I don't think this is a bug. It's rather a current *limitation* of
defining UNIQUE constraints on partitioned tables that they cannot be
created without including the partition key.Thanks,
Amit
Amit,
Thanks for your response. So partitioning does not recognize a primary key
constraint on the partition key as a unique constraint? Shouldn't it?
Thanks,
Stuart
Hi,
On 2018/12/14 8:35, Stuart wrote:
Amit,
Thanks for your response. So partitioning does not recognize a primary key
constraint on the partition key as a unique constraint? Shouldn't it?
It does. When you dropped all UNIQUE constraints on the source table that
didn't contain object_id in it except PRIMARY KEY (object_id), then it did
indeed work, as seen in your first email:
=# drop index knowledge_categoryfilepathurl_un ;
DROP INDEX=# \d+ knowledge
[ ... ]
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
Thanks,
Amit