Postgres Pro
Downloads
Home   >   mailing lists

Errors creating partitioned tables from existing using (LIKE ) after renaming table constraints - Mailing list pgsql-bugs
From Stuart
Subject Errors creating partitioned tables from existing using (LIKE ) after renaming table constraints
Date
Msg-id 2047094.V130LYfLq4@station53.ousa.org
Whole thread Raw
Responses Re: Errors creating partitioned tables from existing using (LIKE) after renaming table constraints
List pgsql-bugs
Tree view

Dear team,

 

I am using PostGreSQL 11.1, I compiled from source on openSuSE Tumbleweed.

ousa_new=# SELECT version() ;
                                                         version
----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 11.1 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux) 8.2.1 20181025 [gcc-8-branch revision 265488], 64-bit

I imported a table definition from another database using:

 

pg_dump -d ousa -t knowledge_vectors -s | psql ousa_new

 

The table inherits from a hierarchy and I imported those parent tables first. I want to test partitioning so in the new db, I renamed the table and all its indexes and constraints so I can create a new table partitioned table based on this design using (like <table>).

 

ousa_new=# \d+ knowledge_vectors_old

Table "public.knowledge_vectors_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 | | plain | |

description | text | | | ''::text | extended | |

vectors | tsvector | | not null | | extended | |

Indexes:

"knowledgevectorsold_pk" PRIMARY KEY, btree (object_id), tablespace "pgindex"

"knowledgevectorsold_vector_idx" gin (vectors), tablespace "pgindex"

Check constraints:

"knowledgevectors_vectors_ck" CHECK (vectors <> ''::tsvector)

Foreign-key constraints:

"knowledgevectorsold_objectid_fk" FOREIGN KEY (object_id) REFERENCES knowledge(object_id)

Triggers:

knowledgevectors_revision_tr BEFORE UPDATE ON knowledge_vectors_old FOR EACH ROW EXECUTE PROCEDURE revised()

Inherits: ousa_objects

 

 

ousa_new=# alter table knowledge_vectors_old rename constraint knowledgevectors_vectors_ck to knowledgevectorsold_vectors_ck ;

ALTER TABLE

 

ousa_new=# \d+ knowledge_vectors_old

Table "public.knowledge_vectors_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 | | plain | |

description | text | | | ''::text | extended | |

vectors | tsvector | | not null | | extended | |

Indexes:

"knowledgevectorsold_pk" PRIMARY KEY, btree (object_id), tablespace "pgindex"

"knowledgevectorsold_vector_idx" gin (vectors), tablespace "pgindex"

Check constraints:

"knowledgevectorsold_vectors_ck" CHECK (vectors <> ''::tsvector)

Foreign-key constraints:

"knowledgevectorsold_objectid_fk" FOREIGN KEY (object_id) REFERENCES knowledge(object_id)

Triggers:

knowledgevectors_revision_tr BEFORE UPDATE ON knowledge_vectors_old FOR EACH ROW EXECUTE PROCEDURE revised()

Inherits: ousa_objects

 

On my attempt to create the new partitioned table using like, I get error that the constraint by the old name doesn't exist:

 

ousa_new=# create table knowledge_vectors (like knowledge_vectors_old INCLUDING ALL ) PARTITION BY RANGE ( object_id ) ;

ERROR: constraint "knowledgevectors_vectors_ck" for table "knowledge_vectors_old" does not exist

 

Only after I dropped the new constraint and recreated it, did the create table (like <table>) work.

 

Ousa_new=# alter table knowledge_vectors_old drop constraint knowledgevectorsold_vectors_ck ;

ALTER TABLE

 

ousa_new=# alter table knowledge_vectors_old add constraint knowledgevectorsold_vectors_ck CHECK (vectors <> ''::tsvector) ;

ALTER TABLE

 

ousa_new=# \d+ knowledge_vectors_old

Table "public.knowledge_vectors_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 | | plain | |

description | text | | | ''::text | extended | |

vectors | tsvector | | not null | | extended | |

Indexes:

"knowledgevectorsold_pk" PRIMARY KEY, btree (object_id), tablespace "pgindex"

"knowledgevectorsold_vector_idx" gin (vectors), tablespace "pgindex"

Check constraints:

"knowledgevectorsold_vectors_ck" CHECK (vectors <> ''::tsvector)

Foreign-key constraints:

"knowledgevectorsold_objectid_fk" FOREIGN KEY (object_id) REFERENCES knowledge(object_id)

Triggers:

knowledgevectors_revision_tr BEFORE UPDATE ON knowledge_vectors_old FOR EACH ROW EXECUTE PROCEDURE revised()

Inherits: ousa_objects

 

 

ousa_new=# create table knowledge_vectors (like knowledge_vectors_old INCLUDING ALL ) PARTITION BY RANGE ( object_id ) ;

CREATE TABLE

 

ousa_new=# \d+ knowledge_vectors

Table "public.knowledge_vectors"

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 | | plain | |

description | text | | | ''::text | extended | |

vectors | tsvector | | not null | | extended | |

Partition key: RANGE (object_id)

Indexes:

"knowledge_vectors_pkey" PRIMARY KEY, btree (object_id), tablespace "pgindex"

"knowledge_vectors_vectors_idx" gin (vectors), tablespace "pgindex"

Check constraints:

"knowledgevectorsold_vectors_ck" CHECK (vectors <> ''::tsvector)

Number of partitions: 0

 

 

The original table is:

 

ousa# \d+ knowledge_vectors

Table "public.knowledge_vectors"

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 | | plain | |

description | text | | | ''::text | extended | |

vectors | tsvector | | not null | | extended | |

Indexes:

"knowledgevectors_pk" PRIMARY KEY, btree (object_id), tablespace "pgindex"

"knowledgevectors_vector_idx" gin (vectors), tablespace "pgindex"

Check constraints:

"knowledgevectors_vectors_ck" CHECK (vectors <> ''::tsvector)

Triggers:

knowledgevectors_revision_tr BEFORE UPDATE ON knowledge_vectors FOR EACH ROW EXECUTE PROCEDURE revised()

Inherits: ousa_objects

 

 

 

pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: segmentation fault with simple UPDATE statement (postgres 10.5)
Next
From: PG Bug reporting form
Date:
Subject: BUG #15548: Unaccent does not remove combining diacritical characters