From: | Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> |
---|---|
To: | cgg007(at)yahoo(dot)com |
Cc: | postgresql listserv <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: pg_dump and ON DELETE CASCADE problem |
Date: | 2009-12-10 02:02:30 |
Message-ID: | 4B2056B6.9090606@postnewspapers.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 10/12/2009 3:31 AM, CG wrote:
> 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.
pg_dump does all its work in a single serializable transaction to avoid
this sort of problem. It doesn't see any changes made to the database
after it starts. So, assuming you used pg_dump to dump the database as a
whole rather than invoking it separately for a bunch of separate tables,
that should not be your problem.
How do you run pg_dump? Can you supply the script or command line?
> One would
> imagine that PostgreSQL would have protections for that sort of thing...
It does, which is what makes the issue you've encountered somewhat strange.
--
Craig Ringer
From | Date | Subject | |
---|---|---|---|
Next Message | J. Greg Davidson | 2009-12-10 02:44:37 | Seeking expected return type info for SPI function |
Previous Message | DM | 2009-12-09 23:38:01 | Install compat-postgresql-libs-debuginfo on postgres 8.3.8 without using RPM |