pg_dump and ON DELETE CASCADE problem

From: CG <cgg007(at)yahoo(dot)com>
To: postgresql listserv <pgsql-general(at)postgresql(dot)org>
Subject: pg_dump and ON DELETE CASCADE problem
Date: 2009-12-09 19:31:08
Message-ID: 680164.33710.qm@web37904.mail.mud.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all,
 
We're using PostgreSQL 8.4 ... We do our nightly database backups with pg_dump. I was doing a test restore and I encountered some data during the reload that was in a table against the conditions of a foreign key constraint. I run my restores with the "-e" option to halt on errors, so this data halted the restore... I went to check the running database and the row in question had been deleted.
 
I had defined the foreign key to cascade on delete, and I imagine that during the dump the delete occurred on the master table. Perhaps the keyed table had already been dumped so when it came time to dump the master table, the referencing row was not there to be dumped. One would imagine that PostgreSQL would have protections for that sort of thing...
 
Can you think of how I can protect against this in the future?
 
CG

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2009-12-09 21:02:46 Re: Excessive (and slow) fsync() within single transaction
Previous Message Tom Lane 2009-12-09 18:10:07 Re: Problem with SET CONSTRAINTS some_constraint_fkey DEFERRED