Skip site navigation (1) Skip section navigation (2)

CConstraints using inherited attributes fail

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: CConstraints using inherited attributes fail
Date: 2000-12-19 00:52:05
Message-ID: 200012190052.eBJ0q5I03436@hub.org (view raw or flat)
Thread:
Lists: pgsql-bugs
Steffen Hulegaard (9sch1(at)txl(dot)com) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
 CConstraints using inherited attributes fail

Long Description
     A previous bug entitled "Compound and cross-named foreign 
key constraints fail" led a most helpful PostgreSQL guy 
to determine that foreign key constraints will *require* a 
UNIQUE and/or PRIMARY KEY declaration for the attributes being 
referenced.  As the script attached demonstrates, I want to INHERIT() my internal ids via a rather elaborate 
generalization/specialization hierarchy - which I omit in the
script).  Unfortunately, PRIMARY KEY declarations applied to 
my INHERIT()ed internal id attributes always fail.  That means 
I cannot use foreign key constraints that reference them ... 
and since all my foreign key constraints use the efficient/internal 
ids ... I cannot use foreign key constraints at all!
     This would not be a major annoyance if I could just
ALTER TABLE in the PRIMARY KEY constraint assertion after the table 
was created.  The tables do create with the INHERIT()ed attributes.  Unfortunately, PostgreSQL v7.0.x only supports adding FOREIGN KEY constraints via ALTER TABLE.  You cannot add a UNIQUE or PRIMARY KEY 
constraint after CREATE TABLE (according to the manual) :-( 
     The manual suggests CREATE UNIQUE INDEX as a 
way to get a UNIQUE constraint.  However, Stephan Szabo's <sszabo(at)megazone23(dot)bigpanda(dot)com> kind testing with v7.1 dev sources 
found that a UNIQUE index is not sufficient for certain 
(compound and cross-named) foreign key constraints.  These 
require the CREATE/ATLER TABLE assertions of a UNIQUE and/or 
PRIMARY KEY constraint.  The presence of a UNIQUE index is 
not enough.  Certain entries need to appear in the system catalog |-(
     The only work-around is for me to go through hundreds of 
tables and remove all direct and indirect INHERITS(al_ids).  Then 
I must add them by hand to each and every table (almost all tables 
have a compound/two-part internal id in this horizontally 
partionable schema).  Then I can everywhere add the PRIMARY KEY assertions required for referential integrity.  Ouch.  
     Worse, using the implicit indices of PRIMARY KEY/UNIQUE 
*might* mean that I risk giving up forthcoming control over index 
type (UNIQUE HASH!), upcoming control over the index tablespace (spreading indexes across disk controllers), control over the 
operator class (used for the index) and so on.  While I might not really understand the pros/cons of implicit indexing, I sure DO wish that CREATE INDEX left the UNIQUE and PRIMARY KEY clasues nothing more than convenient short cuts (for implicit INDEX creation).
     At any rate, it definently seems like CONSTRAINTS ought to recognize the existence of INHERIT()ed attributes.

Thanks, Steffen.


Sample Code
/* $Id$
 +--------------------------------------------------------------------
 | No Copyright.  Public Domain.
 +--------------------------------------------------------------------
 | 
 | bug3.sql	     Constraints using inherited attributes fail 
 |
 | Description:      Run this psql script on an empty database 
 |                   to generate the following error (reformatted 
 |                   into multiple lines):
 |                   ERROR:  CREATE TABLE: 
 |                     column 'record_id' named in key does not exist
 |
 |                   Problem:  The failing CREATE TABLE command is 
 |                             attempting to declare a constraint, 
 |                             in this case a compound PRIMARY KEY 
 |                             constraint, which involves an 
 |                             inherited attribute.  The error 
 |                             message seems to indicate that 
 |                             PostgreSQL referential integrity 
 |                             logic does not realize inherited 
 |                             attributes exist.  
 |             Minor Problem:  Base table foreign key constraints 
 |                             are not inherited (reliably).  This 
 |                             forces redundant constraint 
 |                             declarations to propogate 
 |                             to all derived classes.  In a large 
 |                             schema, this is both confusing and 
 |                             time consuming. 
 | Environment ----------------------------------------------------
 | RedHat 6.2 
 | select version();
 |    PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66
 | # rpm -qi postgresql-7.0.2-2
 | Name        : postgresql                   Relocations: /usr 
 | Version     : 7.0.2                             Vendor: The Ramifordistat
 | Release     : 2                             Build Date: Mon 12 Jun 2000 02:21:35 PM PDT
 | Install date: Fri 04 Aug 2000 11:40:39 AM PDT      Build Host: utility.wgcr.org
 | Group       : Applications/Databases        Source RPM: postgresql-7.0.2-2.src.rpm
 | Size        : 7431735                          License: BSD
 | Packager    : Lamar Owen <lamar(dot)owen(at)wgcr(dot)org>
 | URL         : http://www.postgresql.org/
 | Summary     : PostgreSQL client programs and libraries.
 | 
 | 12/18/2000  SC Hulegaard  Created. 
 + -------------------------------------------------------------------  */

  CREATE TABLE al_descs ( 
  name                   VARCHAR(84)  NOT NULL, 
  name_sort              VARCHAR(84)  NOT NULL, 
  name_ts                TIMESTAMP    NOT NULL DEFAULT     CURRENT_TIMESTAMP, 
  description            VARCHAR(256) NOT NULL DEFAULT     'No description is available.', 
  explanation            TEXT         NOT NULL DEFAULT     'No explanation is available.', 
  priority               INT4         NOT NULL DEFAULT     1, 
  secondary              BOOL         NOT NULL DEFAULT     TRUE ) ;

  /*  A press is like a server farm/cluster  */
  CREATE TABLE al_presses ( 
  record_id              INT4         NOT NULL, 
  address_id             INT4         NOT NULL DEFAULT     3, 
  address_press_id       INT4         NOT NULL DEFAULT     3 )
  INHERITS ( al_descs ) ;

  /*  Most entities have a compound internal/logical identifer ... 
      The local server farm/cluster identifier and the server 
      farm/cluster id.  Since constraints do not inherit, 
      the presence of the base table constraint serves mainly 
      as documentation (the base table is not directly modified).  */
  CREATE TABLE al_ids ( 
  record_id        INT4         NOT NULL, 
  press_id         INT4         NOT NULL DEFAULT     1, 
  CONSTRAINT al_ids_presses_fk 
      FOREIGN KEY ( press_id ) 
      REFERENCES al_presses ( record_id ) 
      MATCH FULL ON DELETE RESTRICT ON UPDATE RESTRICT 
      DEFERRABLE INITIALLY DEFERRED ) ;

  CREATE TABLE al_params_no_pkey ( 
  category       VARCHAR(32)   NOT NULL DEFAULT     'General', 
  value          VARCHAR(256)  NOT NULL DEFAULT     '', 
/*  CONSTRAINT al_params_record_id_ix 
      PRIMARY KEY ( record_id, press_id ), */
/* a redundant constraint that should be inherited from al_presses */
  CONSTRAINT al_params_presses_fk 
      FOREIGN KEY ( press_id ) 
      REFERENCES al_presses ( record_id ) 
      MATCH FULL ON DELETE RESTRICT ON UPDATE RESTRICT 
      DEFERRABLE INITIALLY DEFERRED ) 
  INHERITS ( al_ids, al_descs ) ;

  CREATE TABLE al_params ( 
  category       VARCHAR(32)   NOT NULL DEFAULT     'General', 
  value          VARCHAR(256)  NOT NULL DEFAULT     '', 
  CONSTRAINT al_params_record_id_ix 
      PRIMARY KEY ( record_id, press_id ), 
/* a redundant constraint that should be inherited from al_presses */
  CONSTRAINT al_params_presses_fk 
      FOREIGN KEY ( press_id ) 
      REFERENCES al_presses ( record_id ) 
      MATCH FULL ON DELETE RESTRICT ON UPDATE RESTRICT 
      DEFERRABLE INITIALLY DEFERRED ) 
  INHERITS ( al_ids, al_descs ) ;

/*  DROP TABLE al_params ;  */

  DROP TABLE al_params_no_pkey ;

  DROP TABLE al_presses ;

  DROP TABLE al_ids ;

  DROP TABLE al_descs ;


No file was uploaded with this report


Responses

pgsql-bugs by date

Next:From: pgsql-bugsDate: 2000-12-19 19:27:57
Subject: pg_dumpall doesn't handle all it should
Previous:From: Bruno Wolff IIIDate: 2000-12-18 16:26:38
Subject: Re: Bug report

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group