Re: pg_dump and ON DELETE CASCADE problem

[CG <cgg007@xxxxxxxxx>]

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@xxxxxxxxxxxxxxxxxxxxx> wrote:

From: Craig Ringer <craig@xxxxxxxxxxxxxxxxxxxxx>
Subject: Re: pg_dump and ON DELETE CASCADE problem
To: cgg007@xxxxxxxxx
Cc: "postgresql listserv" <pgsql-general@xxxxxxxxxxxxxx>
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