Bug with foreign keys and importing from a pg_dump file?

Started by Michael Davisabout 25 years ago2 messagesbugs
Jump to latest
#1Michael Davis
mdavis@sevainc.com

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Michael Davis (#1)
Re: Bug with foreign keys and importing from a pg_dump file?

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