Thread

1. Bug with foreign keys and importing from a pg_dump file?

   Michael J Davis <mdavis@sevainc.com> — 2001-01-26T17:50:17Z

   Your name               :       Michael Davis
   Your email address      :	mdavis@sevainc.com
   
   
   System Configuration
   ---------------------
   Architecture (example: Intel Pentium)         : Intel Pentium 233 (2 
   processors)
   
   Operating System (example: Linux 2.0.26 ELF)  : Linux (Red Hat 6.2)
   
   PostgreSQL version (example: PostgreSQL-7.1):   PostgreSQL-7.1 Beta 3
   
   Compiler used (example:  gcc 2.8.0)           : gcc from Ret Hat 6.2
   
   
   Please enter a FULL description of your problem:
   ------------------------------------------------
   
   Is there a bug with importing from a pg_dump file and foreign keys?  If I 
   create two tables where one table has a foreign key relationship to the 
   other and look at the system tables everything looks great.  If I then 
   pg_dump, dropdb, createdb, and import the dump file, then the system tables 
   are reporting the foreign key differently.
   
   
   Please describe a way to repeat the problem.   Please try to provide a
   concise reproducible example, if at all possible:
   ----------------------------------------------------------------------
   
   createdb tst
   psql tst
   
   CREATE TABLE genericfield
   (
       GenericID         int4 PRIMARY KEY,
       GenericName       varchar(32) NOT NULL,
       Note              text
   );
   
   CREATE TABLE membergenericfield
   (
       MemberGenericID   int4 PRIMARY KEY,
       GenericID         int4 NOT NULL,
       Note              text,
       CONSTRAINT MemberGenericFieldGenericID_fk
         FOREIGN KEY (GenericID) REFERENCES GenericField(GenericID)
   );
   
   select t.tgconstrname, c1.relname as TableName, c2.relname as ForiegnTable
      FROM pg_trigger t
            left join pg_class c1 on t.tgrelid = c1.relfilenode
            left join pg_class c2 on t.tgconstrrelid = c2.relfilenode
      where substr(t.tgname, 1, 3) = 'RI_' order by t.tgconstrname;
   
             tgconstrname          |     tablename      |    foriegntable
   --------------------------------+--------------------+--------------------
    membergenericfieldgenericid_fk | membergenericfield | genericfield
    membergenericfieldgenericid_fk | genericfield       | membergenericfield
    membergenericfieldgenericid_fk | genericfield       | membergenericfield
   (3 rows)
   
   \q
   
   pg_dump tst > tst.dmp
   dropdb tst
   createdb tst
   psql -a tst postgres < tst.dmp
   
   psql tst postgres
   
   select t.tgconstrname, c1.relname as TableName, c2.relname as ForiegnTable
      FROM pg_trigger t
            left join pg_class c1 on t.tgrelid = c1.relfilenode
            left join pg_class c2 on t.tgconstrrelid = c2.relfilenode
      where substr(t.tgname, 1, 3) = 'RI_' order by t.tgconstrname;
   
             tgconstrname          |     tablename      | foriegntable
   --------------------------------+--------------------+--------------
    membergenericfieldgenericid_fk | membergenericfield | pg_xactlock
    membergenericfieldgenericid_fk | genericfield       | pg_xactlock
    membergenericfieldgenericid_fk | genericfield       | pg_xactlock
   (3 rows)
   
   \q
   
   
   
   
   

2. Re: Bug with foreign keys and importing from a pg_dump file?

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-01-27T07:09:21Z

   Michael Davis <mdavis@sevainc.com> writes:
   > Is there a bug with importing from a pg_dump file and foreign keys?  If I 
   > create two tables where one table has a foreign key relationship to the 
   > other and look at the system tables everything looks great.  If I then 
   > pg_dump, dropdb, createdb, and import the dump file, then the system tables 
   > are reporting the foreign key differently.
   
   It looks like pg_dump neglects to emit a "FROM pktable" clause in its
   CREATE CONSTRAINT TRIGGER commands, and so the tgconstrrelid field of
   pg_trigger is not restored.  This is a bug I think, although the side
   effects appear to be minor...
   
   			regards, tom lane