CConstraints using inherited attributes fail - Mailing list pgsql-bugs

Steffen Hulegaard (9sch1@txl.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
addingFOREIGN 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@megazone23.bigpanda.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
overthe  
operator class (used for the index) and so on.  While I might not really understand the pros/cons of implicit indexing,
Isure DO wish that CREATE INDEX left the UNIQUE and PRIMARY KEY clasues nothing more than convenient short cuts (for
implicitINDEX 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.owen@wgcr.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