Обсуждение: Both cross-named & compound foreign key constaints fail
Steffen Hulegaard (9sch1@txl.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.owen@wgcr.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
On Mon, 27 Nov 2000 pgsql-bugs@postgresql.org wrote:
Won't have a full answer until I'm home, but figured I'd send something.
> 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.
Okay, these two say to me that it's almost certainly not the ALTER
TABLE code that's generating the message, but instead the trigger itself
(the ALTER TABLE calls the trigger for each row already in the
table). Does it fail if you do an insert after getting a successful
create in the second case, or does it work there? I'm wondering if
I'm passing in bogus arguments to the trigger function in the data
check in certain cases.
7.1 should do column name checking at constraint creation
time, but I'm not sure if the other issue is fixed. I'll
check my 7.1 and 7.0 machine with sources when I get home. :)
> 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.
This one should be fixed in 7.1, I miscopied something
from somewhere else and didn't get the constraint name to the
trigger.
> 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 ...
Unfortunately you can not currently constrain views that way.
The reason not much has been done about it is related to the
check constraints with subselects. You may need to actually constrain
all tables referenced by the view in order to make the constraint work
and that's a bigger problem, especially if the view has set value
functions, etc. (An insert to a table could cause say a count value
say to change which would orphan a row in the fk table)
Okay. On current sources, this seems to work with only a few changes. You need unique or primary key constraints on the columns being referenced (this is part of the spec but was not checked in 7.0) A couple of other things, currently constraints don't inherit very well. So, you'd probably want to have the fk constraint on al_ids on al_addresses_data as well and the unique constraints need to be on the targets of the fk constraints explicitly. > 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 >