Thread
-
Both cross-named & compound foreign key constaints fail
PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2000-11-27T21:54:08Z
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 -
Re: Both cross-named & compound foreign key constaints fail
Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2000-11-29T22:02:05Z
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)
-
Re: Both cross-named & compound foreign key constaints fail
Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2000-11-30T15:56:17Z
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 >