CConstraints using inherited attributes fail

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