Cross-named & compound foreign key constraints fail

/* $Id$
+--------------------------------------------------------------------
| No Copyright. Public Domain.
+--------------------------------------------------------------------
|
| bug.sql Cross named and compound foreign key constraint bug test
|
| Description: Run this script on an empty database to generate
| the following error (reformatted into multiple lines):
| psql:bug.sql:54: ERROR:
| constraint <unnamed>:
| table al_addresses_data does not have
| an attribute address_press_id
|
| Problem: The failing command is ALTER TABLE
| al_presses ADD CONSTRAINT ... FOREIGN KEY
| ... *REFERENCES* al_addresses_data
| (record_id, *press_id*).
|
| PostgreSQL\'s error message indicates that
| the DB is looking for an al_addresses_data
| attribute with the *same* name as the
| foreign key column in source table (i.e.
| al_presses.address_press_id). It *appears*
| that the REFERENCES list is being ignored
| (with respect to at least the second element
| of the REFERENCES list).
| Problem: If the tables are left empty, the schema
| creates without error even though a latent
| error exists! Comment out the two insert
| statements to witness error free schema
| construction. This indicates that some
| DDL/schema-creation time validation is
| being deferred. It would be far more
| helpful to detect all schema errors
| during schema construction. A small point.
| Problem: The ALTER TABLE ... ADD CONSTRAINT command
| produces an error message about an <unknown>
| constraint. The failed constraint *DOES*
| have a given name. A small point - but a
| source of confusion.
| Problem: Trying to work around this bug with an
| al_addresses_data view that maps record_id
| to address_id and press_id to address_press_id
| also fails. This is a very minor feature/function
| issue once the direct al_addresses_data
| constraint works. Still, it would seem that
| a clean implementation of views might hide the
| table versus view distinction from the
| referential integrity logic ... and thus
| make this work be default. At present,
| this failing view work-around produces the
| following error when the ALTER TABLE is attempted:
| ERROR: system column oid not available - al_addresses is a view
| Of course, I'm way over my head on this point
| since I know nothing of PostgreSQL's internals ...
| 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.
|
| 11/27/2000 SC Hulegaard Created and sent t pgsql-bugs(at)postgresql(dot)org
+ ------------------------------------------------------------------- */

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 ) ;

INSERT INTO al_presses (record_id, name, name_sort) VALUES (1, 'Foo', 'foo') ;

/* Most entities have a compound internal/logical identifer ...
The local server farm/cluster identifier and the server farm/cluster id */
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_addresses_data (
fictional BOOL NOT NULL DEFAULT FALSE,
verified BOOL NOT NULL DEFAULT FALSE,
street_number VARCHAR(16) NOT NULL DEFAULT '',
street_directional VARCHAR(2) NOT NULL DEFAULT '',
street_name VARCHAR(32) NOT NULL DEFAULT '',
street_suffix VARCHAR(12) NOT NULL DEFAULT '' )
INHERITS ( al_ids ) ;

INSERT INTO al_addresses_data (record_id, press_id) VALUES ( 3, 3 ) ;

ALTER TABLE al_presses ADD
CONSTRAINT al_presses_address_data_fk
FOREIGN KEY (address_id, address_press_id)
REFERENCES al_addresses_data (record_id, press_id)
MATCH FULL
ON DELETE RESTRICT ON UPDATE RESTRICT
DEFERRABLE INITIALLY DEFERRED ;

DROP TABLE al_addresses_data ;

DROP TABLE al_presses ;

DROP TABLE al_ids ;

DROP TABLE al_descs ;