Обсуждение: Primary Key Constraint on inheritance table not getting route to child tables

Поиск
Список
Период
Сортировка

Primary Key Constraint on inheritance table not getting route to child tables

От
Rushabh Lathia
Дата:
Hi,

ALTER TABLE ADD Constraints PRIMARY KEY on inheritance table not getting route to child table.

But when we do ALTER TABLE DROP Constraint on the same, it complains about constraint does not
exists on child table.

Consider the following example

psql=# CREATE TABLE measurement (
psql(#     city_id         int not null,
psql(#     logdate         date not null,
psql(#     peaktemp        int,
psql(#     unitsales       int
psql(# );
CREATE TABLE
psql=# CREATE TABLE measurement_y2006m02 (
psql(#     CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
psql(# ) INHERITS (measurement);
CREATE TABLE
psql=# CREATE TABLE measurement_y2006m03 (
psql(#     CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
psql(# ) INHERITS (measurement);
CREATE TABLE
psql=# 
psql=# 
psql=# ALTER TABLE measurement
ADD CONSTRAINT con1 PRIMARY KEY (city_id);
ALTER TABLE
psql=# 
psql=# 

-- Don't have primary key on child table
psql=# desc measurement_y2006m02
         Table "public.measurement_y2006m02"
  Column   |            Type             | Modifiers 
-----------+-----------------------------+-----------
 city_id   | integer                     | not null
 logdate   | timestamp without time zone | not null
 peaktemp  | integer                     | 
 unitsales | integer                     | 
Check constraints:
    "measurement_y2006m02_logdate_check" CHECK (logdate >= '01-FEB-06 00:00:00'::timestamp without time zone AND logdate < '01-MAR-06 00:00:00'::timestamp without time zone)
Inherits: measurement

-- Primary key on parent table
psql=# desc measurement
             Table "public.measurement"
  Column   |            Type             | Modifiers 
-----------+-----------------------------+-----------
 city_id   | integer                     | not null
 logdate   | timestamp without time zone | not null
 peaktemp  | integer                     | 
 unitsales | integer                     | 
Indexes:
    "con1" PRIMARY KEY, btree (city_id)
Number of child tables: 2 (Use \d+ to list them.)

psql=# ALTER TABLE measurement
DROP CONSTRAINT con1;
ERROR:  constraint "con1" of relation "measurement_y2006m02" does not exist

I am not sure whether PRIMARY KEY not getting route is a expected behavior or not, but if its expected behavior
then obviously DROP CONSTRAINT should not complain about constraint doesn't exists on child table.

Inputs/Comments ?

Thanks,
Rushabh Lathia

Re: Primary Key Constraint on inheritance table not getting route to child tables

От
Alvaro Herrera
Дата:
Excerpts from Rushabh Lathia's message of lun ago 20 02:50:52 -0400 2012:
> Hi,
>
> ALTER TABLE ADD Constraints PRIMARY KEY on inheritance table not getting
> route to child table.
>
> But when we do ALTER TABLE DROP Constraint on the same, it complains about
> constraint does not
> exists on child table.

This is a known 9.2 bug, fixed a month in this commit:

Author: Alvaro Herrera <alvherre@alvh.no-ip.org>
Branch: master [f5bcd398a] 2012-07-20 14:08:07 -0400
Branch: REL9_2_STABLE [d721f208a] 2012-07-20 14:07:09 -0400
   connoinherit may be true only for CHECK constraints      The code was setting it true for other constraints, which
is  bogus.  Doing so caused bogus catalog entries for such constraints, and   in particular caused an error to be
raisedwhen trying to drop a   constraint of types other than CHECK from a table that has children,   such as reported
inbug #6712.      In 9.2, additionally ignore connoinherit=true for other constraint   types, to avoid having to force
initdb;existing databases might already   contain bogus catalog entries.      Includes a catversion bump (in HEAD
only).     Bug report from Miroslav Šulc   Analysis from Amit Kapila and Noah Misch; Amit also contributed the patch. 


I cannot reproduce it in 9.2 HEAD or master HEAD.  I assume you were
testing with something older than the above commit; the 9.1 branch does
not contain the bug.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Re: Primary Key Constraint on inheritance table not getting route to child tables

От
Tom Lane
Дата:
Rushabh Lathia <rushabh.lathia@gmail.com> writes:
> ALTER TABLE ADD Constraints PRIMARY KEY on inheritance table not getting
> route to child table.

Right.

> But when we do ALTER TABLE DROP Constraint on the same, it complains about
> constraint does not exists on child table.

Works for me in HEAD.  What version are you testing?  This seems related
to some recent bug fixes ...
        regards, tom lane

Re: Primary Key Constraint on inheritance table not getting route to child tables

От
Rushabh Lathia
Дата:


On Mon, Aug 20, 2012 at 9:28 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Rushabh Lathia <rushabh.lathia@gmail.com> writes:
> ALTER TABLE ADD Constraints PRIMARY KEY on inheritance table not getting
> route to child table.

Right.

> But when we do ALTER TABLE DROP Constraint on the same, it complains about
> constraint does not exists on child table.

Works for me in HEAD.  What version are you testing?  This seems related
to some recent bug fixes ...

Oh ok. 

Sorry for wrong noise, I was checking this on old version.

Thanks,


                        regards, tom lane



--
--
Rushabh Lathia
Technical Architect
EnterpriseDB Corporation
The Enterprise Postgres Company

Phone: +91-20-30589494

Website: http://www.enterprisedb.com

EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb