Both cross-named & compound foreign key constaints 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
Both cross-named & compound foreign key constaints fail

Long Description
This bug report is a near clone of one emailed in. I just
discovered this web interface for bug report submission, so I am
also using it to ensure that it is logged properly.

Thanks, Steffen (P.S. PostgreSQL is looking very nice!
Keep up the awesome work! )

Description: Run the psql script below to generate
the following error:
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).
Minor 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.
Minor 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
potential source of confusion.
Aside: 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 clever implementation of views might hide the
table versus view distinction from the
referential integrity logic ... and thus
make this work by 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: TheRamifordistat
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.

Sample Code
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 ;

No file was uploaded with this report