Thread: pg_dump --> restore loses constraints/triggers?

 Greetings,

 We've seem to come into a problem with some of our referential integrity
checks, and I was wondering if anyone else had experienced this.

 To upgrade our DB from 7.1 to 7.3 I did:

 pg_dump --create dbname > backup_file.sql

 Then created users, and \i backup_file.sql from the command prompt.

 All the data seems to be there, but many foreign key constraints seem to
be broken. For example, we created this table:

CREATE TABLE ind (
ind_id INTEGER DEFAULT NEXTVAL(''"ind_id_seq"''::text),
bnr_id INTEGER PRIMARY KEY,
...(other columns)
);

and another table that makes reference to it:

CREATE TABLE grp_join_ind_clt (
grp_id INTEGER REFERENCES grp ON UPDATE CASCADE ON DELETE CASCADE,
bnr_id INTEGER REFERENCES ind ON UPDATE CASCADE ON DELETE CASCADE
);

Now, when we've restored, the first trigger above (on the table 'grp')
works, but not the one on 'ind'. Also, the contstraints were not added as
contstraints to the tables, but as CREATE CONSTRAINT TRIGGER "<unnamed>"
instead. Is this normal? Or should they be ALTER TABLE's?  We're stumped
as to what happened, and to how to fix it. Any ideas/thoughts? Any info on
this would be much appreciated.

 Thanks in advance.

Cheers,

Chris

--

Christopher Murtagh
Webmaster / Sysadmin
Web Communications Group
McGill University
Montreal, Quebec
Canada

Tel.: (514) 398-3122
Fax:  (514) 398-2017

Christopher Murtagh <christopher.murtagh@mcgill.ca> writes:
>  We've seem to come into a problem with some of our referential integrity
> checks, and I was wondering if anyone else had experienced this.

>  To upgrade our DB from 7.1 to 7.3 I did:
>  pg_dump --create dbname > backup_file.sql

Which pg_dump did you use?  I think 7.3's pg_dump would translate the
7.1 trigger-based representation correctly, but 7.1's wouldn't know
any better than to emit CREATE CONSTRAINT TRIGGER commands.

(But as far as I know, the constraint triggers ought to *work*, they
just won't look pretty.  Please define "broken".)

If you still have the 7.1 server running, you can try re-dumping with
the newer pg_dump; otherwise you'll probably want to drop all those
triggers and create foreign key constraints manually :-(.

            regards, tom lane

On Mon, 17 Feb 2003, Tom Lane wrote:
>Which pg_dump did you use?  I think 7.3's pg_dump would translate the 7.1
>trigger-based representation correctly, but 7.1's wouldn't know any
>better than to emit CREATE CONSTRAINT TRIGGER commands.
>
>(But as far as I know, the constraint triggers ought to *work*, they
>just won't look pretty.  Please define "broken".)

 Thanks for the speedy reply Tom. I used 7.1 pg_dump, and unfortunately,
the server isn't running anymore. When I said 'broken' I really meant that
they weren't there - no constraints, and neither were any corresponding
CREATE CONSTRAINT TRIGGERs.

 We use Postgres (and really *love* it) as part of our home-grown central
web system (www.mcgill.ca), and it is constantly evolving. The switchover
happened 10 days ago, and I suspect it would be more work to try to sync
the changes than to re-create foreign key constraints.

 Thanks again.

Cheers,

Chris

--

Christopher Murtagh
Webmaster / Sysadmin
Web Communications Group
McGill University
Montreal, Quebec
Canada

Tel.: (514) 398-3122
Fax:  (514) 398-2017