Postgres Pro
Downloads
Home   >   mailing lists

Re: pg_dump and ON DELETE CASCADE problem - Mailing list pgsql-general

From CG
Subject Re: pg_dump and ON DELETE CASCADE problem
Date
Msg-id 47579.22097.qm@web37907.mail.mud.yahoo.com
Whole thread Raw
In response to Re: pg_dump and ON DELETE CASCADE problem  (Craig Ringer <craig@postnewspapers.com.au>)
Responses Re: pg_dump and ON DELETE CASCADE problem
List pgsql-general
Tree view
The command's nothing out-of-the-ordinary:
 
#!/bin/bash
export LD_LIBRARY_PATH=/usr/local/pgsql/lib
 
#####################################################################
# Set Variables
#####################################################################
DAY_NUM=`/bin/date +"%d"`
MON_NUM=`/bin/date +"%m"`
YEAR_NUM=`/bin/date +"%Y"`
/usr/local/pgsql/bin/pg_dump -h 192.168.1.5 -Upostgres -f backup.$YEAR_NUM$MON_NUM$DAY_NUM.pga -Fc -b data
 
#END
 
Curiouser and curiouser... Last night's dump failed to restore in the same way:
 
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 7545; 2606 311883439 FK CONSTRAINT packet_search_trigram_puuid_fkey postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  insert or update on table "packet_search_trigram" violates foreign key constraint "packet_search_trigram_puuid_fkey"
DETAIL:  Key (packet_uuid)=(0ab44da9-544d-413a-9ab1-a1b442310b24) is not present in table "packet".
    Command was:
ALTER TABLE ONLY packet_search_trigram
    ADD CONSTRAINT packet_search_trigram_puuid_fkey FOREIGN KEY (packet_uuid) REFERE...
pg_restore: *** aborted because of error
pg_restore: finished item 7545 FK CONSTRAINT packet_search_trigram_puuid_fkey
pg_restore: [archiver] worker process failed: exit code 1
pg_restore: *** aborted because of error
That was the same failure I got the previous night. I go to the live database and rows with that key are /not/ in either one of those tables. They /were/ in the tables at one point. I have an ON DELETE trigger that copies deleted rows into another table, so I can see that a row with that key once existed in those tables.
 
This may not be a pg_dump problem, but some sort of MVCC irregularity where pg_dump is able to dump rows that it shouldn't. I bet a VACUUM FULL would clean this up, but I have a live problem here. If I eradicate it, who knows when we'll see it again...
 

--- On Wed, 12/9/09, Craig Ringer <craig@postnewspapers.com.au> wrote:

From: Craig Ringer <craig@postnewspapers.com.au>
Subject: Re: [GENERAL] pg_dump and ON DELETE CASCADE problem
To: cgg007@yahoo.com
Cc: "postgresql listserv" <pgsql-general@postgresql.org>
Date: Wednesday, December 9, 2009, 9:02 PM

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

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Cheapest way to poll for notifications?
Next
From: "Aycock, Jeff R."
Date:
Subject: postgresql_autodoc tool