From: | CG <cgg007(at)yahoo(dot)com> |
---|---|
To: | postgresql listserv <pgsql-general(at)postgresql(dot)org> |
Cc: | Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> |
Subject: | Re: pg_dump and ON DELETE CASCADE problem |
Date: | 2009-12-10 15:27:54 |
Message-ID: | 47579.22097.qm@web37907.mail.mud.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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:
From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
Subject: Re: [GENERAL] pg_dump and ON DELETE CASCADE problem
To: cgg007(at)yahoo(dot)com
Cc: "postgresql listserv" <pgsql-general(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2009-12-10 15:48:24 | Re: Cheapest way to poll for notifications? |
Previous Message | Vick Khera | 2009-12-10 15:14:35 | Re: Defining permissions for tables, schema etc.. |