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