Thread: create partitioned table with (like table INCLUDING ALL ) fails with "insufficient columns in UNIQUE constraint definition"

create partitioned table with (like table INCLUDING ALL ) fails with "insufficient columns in UNIQUE constraint definition"

From

Stuart

Date:

12 December 2018, 21:08:06

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"

---------------+-----------------------------+-----------+----------+-----------------------------------+----------+--------------+-------------

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"

---------------+-----------------------------+-----------+----------+-----------------------------------+----------+--------------+-------------

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

Tested creating a normal table using the same syntax, and it works.

=# \d+ knowledge

=# create table knowledge_test (like knowledge including all ) ;
CREATE TABLE

Re: create partitioned table with (like table INCLUDING ALL ) failswith "insufficient columns in UNIQUE constraint definition"

From

Amit Langote

Date:

13 December 2018, 09:14:33

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
constraintsdon't get recognized if there are other unique constraints and indexes in the original table.  Creating a
non-partitionedtable works ok using the same options.
 
> 
> 
> =# \d+ knowledge
>                                                             Table "public.knowledge"
>     Column     |            Type             | Collation | Nullable |              Default              | Storage  |
Statstarget | 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

Re: create partitioned table with (like table INCLUDING ALL ) fails with "insufficient columns in UNIQUE constraint definition"

From

Stuart

Date:

13 December 2018, 23:35:03

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

Re: create partitioned table with (like table INCLUDING ALL ) failswith "insufficient columns in UNIQUE constraint definition"

From

Amit Langote

Date:

14 December 2018, 00:42:09

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