Bug with foreign keys and importing from a pg_dump file?

From: Michael Davis <mdavis(at)sevainc(dot)com>
To: "'pgsql-bugs(at)postgresql(dot)org'" <pgsql-bugs(at)postgresql(dot)org>
Subject: Bug with foreign keys and importing from a pg_dump file?
Date: 2001-01-26 17:50:17
Message-ID: 01C08785.C933CFF0.mdavis@sevainc.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Your name : Michael Davis
Your email address : mdavis(at)sevainc(dot)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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Paul M. Aoki 2001-01-26 19:12:44 Re: [JDBC] Re: no way in LargeObject API to detect short read?
Previous Message Alex Krohn 2001-01-26 17:19:05 Re: select fails on indexed varchars.