Re: pg_dump and ON DELETE CASCADE problem

On Thursday 10 December 2009 7:27:54 am CG wrote:
> 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(at)postnewspapers(dot)com(dot)au> wrote:
>
>

One thing that comes to mind is to restore the dump file to a file instead of a
database and see what is being dumped from the live database.

--
Adrian Klaver
aklaver(at)comcast(dot)net