Re: [GENERAL] pg_dump and ON DELETE CASCADE problem

[Craig Ringer]

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

--
Sent via pgsql-general mailing list (pgsql-...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Adrian Klaver]

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 <cr...@postnewspapers.com.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
akl...@comcast.net

[John R Pierce]

CG wrote:
> Thanks for the suggestion. I'm not sure what you mean when you say I should restore to a file. Do you mean I should dump the database to an SQL file instead of the "compressed" format?
>

he meant...

pg_restore -f outputfile.sql yourdumpfile

this will convert the dumpfile to SQL...

[Adrian Klaver]

----- "CG" <cgg...@yahoo.com> wrote:

> Thanks for the suggestion. I'm not sure what you mean when you say I
> should restore to a file. Do you mean I should dump the database to an
> SQL file instead of the "compressed" format?

See Johns answer.

>
> What do you think I will find?
>
> In the database dump, it is including a row that should be marked as
> deleted. I can select on that key in the production database and get
> zero rows, and I can select on that key in the restored database and
> find the row. When I ignore errors the data is restored, but the
> foreign key can't be created (and that is the only error I encounter).
> The presence of the data in the dump can not be contested... :)
>

Well I often find what I 'know' and what is are not the same:) Basically restoring to the file replicates the database restore, with out the error hopefully. Looking at the data restored in the file might give you a clue to what is going on. Just one step in the process of resolving the problem.

Adrian Klaver
akl...@comcast.net

[Adrian Klaver]

On Friday 11 December 2009 5:59:31 am CG wrote:
> That's really nifty! I didn't know you could do that!
>
> So I expanded it, and I grepped for that UUID through the 46 gig file, and
> I found the row in the dump that shouldn't be there... It defies
> explanation.
>

Just so I am clear it always exactly the same UUID that shows up in the dump
file? Upstream in this thread you mentioned a DELETE trigger that copied
deleted items to another table. Is it possible that there is a stuck query
related to this trigger that is keeping this row visible to pg_dump? A query
against pg_stat_activity might be useful.

--

[Scott Marlowe]

On Thu, Dec 10, 2009 at 1:21 PM, CG <cgg...@yahoo.com> wrote:
>
> Thanks for the suggestion. I'm not sure what you mean when you say I should restore to a file. Do you mean I should dump the database to an SQL file instead of the "compressed" format?
>

> What do you think I will find?
>
> In the database dump, it is including a row that should be marked as deleted. I can select on that key in the production database and get zero rows, and I can select on that key in the restored database and find the row. When I ignore errors the data is restored, but the foreign key can't be created (and that is the only error I encounter). The presence of the data in the dump can not be contested... :)

This could be a corrupted index problem maybe? If you do this:

set enable_indexscan=off;
select * from table where key=value;

does it still not show up?