Thread: pg_dump and ON DELETE CASCADE problem

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

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@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
aklaver@comcast.net

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
dumpthe 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
productiondatabase and get zero rows, and I can select on that key in the restored database and find the row. When I
ignoreerrors the data is restored, but the foreign key can't be created (and that is the only error I encounter). The
presenceof the data in the dump can not be contested... :) 



--- On Thu, 12/10/09, Adrian Klaver <aklaver@comcast.net> 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@comcast.net
>

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
dumpthe 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...

----- "CG" <cgg007@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
aklaver@comcast.net

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
bethere... It defies explanation. 

--- On Thu, 12/10/09, John R Pierce <pierce@hogranch.com> wrote:

> From: John R Pierce <pierce@hogranch.com>
> Subject: Re: [GENERAL] pg_dump and ON DELETE CASCADE problem
> To: cgg007@yahoo.com, pgsql-general@postgresql.org
> Date: Thursday, December 10, 2009, 3:29 PM
> 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...
>
>
>
>
>

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.



--
Adrian Klaver
aklaver@comcast.net

On Thu, Dec 10, 2009 at 1:21 PM, CG <cgg007@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
dumpthe 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
productiondatabase and get zero rows, and I can select on that key in the restored database and find the row. When I
ignoreerrors the data is restored, but the foreign key can't be created (and that is the only error I encounter). The
presenceof 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?

--- On Fri, 12/11/09, Scott Marlowe <scott.marlowe@gmail.com> wrote:

> From: Scott Marlowe <scott.marlowe@gmail.com>
> Subject: Re: [GENERAL] pg_dump and ON DELETE CASCADE problem
> To: cgg007@yahoo.com
> Cc: pgsql-general@postgresql.org, "Adrian Klaver" <aklaver@comcast.net>, "Craig Ringer" <craig@postnewspapers.com.au>
> Date: Friday, December 11, 2009, 1:17 PM
> On Thu, Dec 10, 2009 at 1:21 PM, CG
> <cgg007@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?
>

Bingo. Showed right up. I did a reindex, and now it shows up searching via sequential scan or index scan.

So that's pretty scary to have a corrupted index. Once I reindexed, I'm able to see /a lot/ of data I couldn't before.
Thisis the first time in 9 years that I've been bitten by PostgreSQL, and this one HURT. 

PostgreSQL didn't crash, so there was no indication of failure until the demp-reload. To quote from the masters:
Althoughin theory this should never happen, in practice indexes may become corrupted due to software bugs or hardware
failures.I'm reasonably certain that the hardware for the server is sound. No crashes, no alarms... That leaves sofware
bugs. 

We're running PostgreSQL 8.4.1. I don't see any smoking gun bugfixes in 8.4.2, but we'll upgrade ASAP anyway...

What are your suggestions for how to proceed?

On Tuesday 15 December 2009 2:33:39 pm CG wrote:

>
> Bingo. Showed right up. I did a reindex, and now it shows up searching via
> sequential scan or index scan.
>
> So that's pretty scary to have a corrupted index. Once I reindexed, I'm
> able to see /a lot/ of data I couldn't before. This is the first time in 9
> years that I've been bitten by PostgreSQL, and this one HURT.
>
> PostgreSQL didn't crash, so there was no indication of failure until the
> demp-reload. To quote from the masters: Although in theory this should
> never happen, in practice indexes may become corrupted due to software bugs
> or hardware failures. I'm reasonably certain that the hardware for the
> server is sound. No crashes, no alarms... That leaves sofware bugs.
>
> We're running PostgreSQL 8.4.1. I don't see any smoking gun bugfixes in
> 8.4.2, but we'll upgrade ASAP anyway...
>
> What are your suggestions for how to proceed?

Interesting, though something is still bothering me. To quote from one of your
posts upstream;

"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."

Would seem that the rows where deleted and should not be there when the table
was reindexed. Are the 'new' rows you are seeing also in the delete table?


--
Adrian Klaver
aklaver@comcast.net

--- On Tue, 12/15/09, Adrian Klaver <aklaver@comcast.net> wrote:

> From: Adrian Klaver <aklaver@comcast.net>
> Subject: Re: [GENERAL] pg_dump and ON DELETE CASCADE problem
> To: cgg007@yahoo.com
> Cc: "postgresql listserv" <pgsql-general@postgresql.org>, "Craig Ringer" <craig@postnewspapers.com.au>, "Scott
Marlowe"<scott.marlowe@gmail.com> 
> Date: Tuesday, December 15, 2009, 6:53 PM
> On Tuesday 15 December 2009 2:33:39
> pm CG wrote:
>
> >
> > Bingo. Showed right up. I did a reindex, and now it
> shows up searching via
> > sequential scan or index scan.
> >
> > So that's pretty scary to have a corrupted index. Once
> I reindexed, I'm
> > able to see /a lot/ of data I couldn't before. This is
> the first time in 9
> > years that I've been bitten by PostgreSQL, and this
> one HURT.
> >
> > PostgreSQL didn't crash, so there was no indication of
> failure until the
> > demp-reload. To quote from the masters: Although in
> theory this should
> > never happen, in practice indexes may become corrupted
> due to software bugs
> > or hardware failures. I'm reasonably certain that the
> hardware for the
> > server is sound. No crashes, no alarms... That leaves
> sofware bugs.
> >
> > We're running PostgreSQL 8.4.1. I don't see any
> smoking gun bugfixes in
> > 8.4.2, but we'll upgrade ASAP anyway...
> >
> > What are your suggestions for how to proceed?
>
> Interesting, though something is still bothering me. To
> quote from one of your
> posts upstream;
>
> "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."
>
> Would seem that the rows where deleted and should not be
> there when the table
> was reindexed. Are the 'new' rows you are seeing also in
> the delete table?
>

select foo from bar where baz = 'key';

I was mistaken when I said that the row was not in the table. If I had an index on baz, and the index was corrupted,
thatSQL would return 0 rows leading me to believe that there were no rows in the table with that key. 

And, the reason for that row remaining in the database after its foreign keyed parent row was deleted was because the
deleteoperation was depending on the index to find the rows to delete, and that index was corrupt. 

Of course, I had no idea that the index was corrupt when I made my first post.

On the table that has the "martian" row, there is no delete storage. Since the data in the table is trigger-generated
forFTI searches, there's no reason to keep that data around. 

I'm still faced with the unpleasant and unresolved issue of why the index was corrupted in the first place.

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

> --- On Tue, 12/15/09, Adrian Klaver <aklaver@comcast.net> wrote:
>
> > From: Adrian Klaver <aklaver@comcast.net>
> > Subject: Re: [GENERAL] pg_dump and ON DELETE CASCADE problem
> > To: cgg007@yahoo.com
> > Cc: "postgresql listserv" <pgsql-general@postgresql.org>, "Craig
> Ringer" <craig@postnewspapers.com.au>, "Scott Marlowe"
> <scott.marlowe@gmail.com>
> > Date: Tuesday, December 15, 2009, 6:53 PM
> > On Tuesday 15 December 2009 2:33:39
> > pm CG wrote:
> >
> > >
> > > Bingo. Showed right up. I did a reindex, and now it
> > shows up searching via
> > > sequential scan or index scan.
> > >
> > > So that's pretty scary to have a corrupted index. Once
> > I reindexed, I'm
> > > able to see /a lot/ of data I couldn't before. This is
> > the first time in 9
> > > years that I've been bitten by PostgreSQL, and this
> > one HURT.
> > >
> > > PostgreSQL didn't crash, so there was no indication of
> > failure until the
> > > demp-reload. To quote from the masters: Although in
> > theory this should
> > > never happen, in practice indexes may become corrupted
> > due to software bugs
> > > or hardware failures. I'm reasonably certain that the
> > hardware for the
> > > server is sound. No crashes, no alarms... That leaves
> > sofware bugs.
> > >
> > > We're running PostgreSQL 8.4.1. I don't see any
> > smoking gun bugfixes in
> > > 8.4.2, but we'll upgrade ASAP anyway...
> > >
> > > What are your suggestions for how to proceed?
> >
> > Interesting, though something is still bothering me. To
> > quote from one of your
> > posts upstream;
> >
> > "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."
> >
> > Would seem that the rows where deleted and should not be
> > there when the table
> > was reindexed. Are the 'new' rows you are seeing also in
> > the delete table?
> >
>
> select foo from bar where baz = 'key';
>
> I was mistaken when I said that the row was not in the table. If I had
> an index on baz, and the index was corrupted, that SQL would return 0
> rows leading me to believe that there were no rows in the table with
> that key.
>
> And, the reason for that row remaining in the database after its
> foreign keyed parent row was deleted was because the delete operation
> was depending on the index to find the rows to delete, and that index
> was corrupt.
>
> Of course, I had no idea that the index was corrupt when I made my
> first post.
>
> On the table that has the "martian" row, there is no delete storage.
> Since the data in the table is trigger-generated for FTI searches,
> there's no reason to keep that data around.

Would it be possible to see the table schemas and indices ?

>
> I'm still faced with the unpleasant and unresolved issue of why the
> index was corrupted in the first place.
>
>

Adrian Klaver
aklaver@comcast.net

--- On Thu, 12/17/09, Adrian Klaver <aklaver@comcast.net> wrote:

>
> Would it be possible to see the table schemas and indices
> ?
>
> >

Sure (you asked for it!!) :

CREATE TABLE packet
(
  id integer NOT NULL DEFAULT nextval('packet_id_seq'::regclass),
  packet_uuid uniqueidentifier NOT NULL DEFAULT newid(),
  username character varying(50) NOT NULL DEFAULT ''::character varying,
  pgroup_uuid uniqueidentifier DEFAULT newid(),
  orig_trans_uuid uniqueidentifier,
  user_reference_id character varying(50) DEFAULT ''::character varying,
  trans_data character varying(100) NOT NULL DEFAULT ''::character varying,
  trans_type character varying(50) NOT NULL DEFAULT 'unknown'::character varying,
  trans_date timestamp with time zone DEFAULT ('now'::text)::timestamp(6) with time zone,
  processor character varying(10),
  service character varying(10),
  CONSTRAINT packet_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=TRUE
);
ALTER TABLE packet OWNER TO postgres;
GRANT ALL ON TABLE packet TO postgres;
GRANT ALL ON TABLE packet TO adduser;

CREATE INDEX packet_otuuid_idx
  ON packet
  USING btree
  (orig_trans_uuid);

CREATE INDEX packet_pgroup_uuid_idx
  ON packet
  USING btree
  (pgroup_uuid);

CREATE INDEX packet_puuid_hash_uniq
  ON packet
  USING hash
  (packet_uuid);

CREATE UNIQUE INDEX packet_puuid_idx
  ON packet
  USING btree
  (packet_uuid);

CREATE INDEX packet_trans_date_idx
  ON packet
  USING btree
  (trans_date);

CREATE INDEX packet_user_idx
  ON packet
  USING btree
  (username);

CREATE INDEX packet_user_puuid_idx
  ON packet
  USING btree
  (username, packet_uuid);

CREATE OR REPLACE RULE packet_delete_rule AS
    ON DELETE TO packet DO  INSERT INTO removed_packet (id, packet_uuid, username, pgroup_uuid, orig_trans_uuid,
user_reference_id,trans_data, trans_type, trans_date, processor, service)  SELECT packet.id, packet.packet_uuid,
packet.username,packet.pgroup_uuid, packet.orig_trans_uuid, packet.user_reference_id, packet.trans_data,
packet.trans_type,packet.trans_date, packet.processor, packet.service 
           FROM packet
          WHERE packet.id = old.id;

CREATE TRIGGER packet_count_delete_trig
  BEFORE DELETE
  ON packet
  FOR EACH ROW
  EXECUTE PROCEDURE letter_count_trig();

CREATE TRIGGER packet_count_insert_trig
  AFTER INSERT
  ON packet
  FOR EACH ROW
  EXECUTE PROCEDURE letter_count_trig();

CREATE TRIGGER packet_delete_trig
  BEFORE DELETE
  ON packet
  FOR EACH ROW
  EXECUTE PROCEDURE packet_datalink_status_trig();

CREATE TRIGGER packet_insert_trig
  AFTER INSERT
  ON packet
  FOR EACH ROW
  EXECUTE PROCEDURE packet_ins_trig();

CREATE TABLE dpo.packet_search_trigram
(
  id integer NOT NULL DEFAULT nextval('packet_search_trigram_id_seq'::regclass),
  packet_uuid uniqueidentifier NOT NULL,
  trigram_vector tsvector NOT NULL,
  CONSTRAINT packet_search_trigram_id_pkey PRIMARY KEY (id),
  CONSTRAINT packet_search_trigram_puuid_fkey FOREIGN KEY (packet_uuid)
      REFERENCES dpo.packet (packet_uuid) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);
ALTER TABLE dpo.packet_search_trigram OWNER TO postgres;
GRANT ALL ON TABLE dpo.packet_search_trigram TO postgres WITH GRANT OPTION;
GRANT ALL ON TABLE dpo.packet_search_trigram TO addgroup;

CREATE INDEX packet_search_trigram_packet_uuid_idx
  ON dpo.packet_search_trigram
  USING hash
  (packet_uuid);

CREATE INDEX packet_search_trigram_trigram_vector_idx
  ON dpo.packet_search_trigram
  USING gin
  (trigram_vector);

On Thursday 17 December 2009 6:39:45 pm CG wrote:
> --- On Thu, 12/17/09, Adrian Klaver <aklaver@comcast.net> wrote:
> > Would it be possible to see the table schemas and indices
> > ?
>
> Sure (you asked for it!!) :
>

>
> CREATE TABLE dpo.packet_search_trigram
> (
>   id integer NOT NULL DEFAULT
> nextval('packet_search_trigram_id_seq'::regclass), packet_uuid
> uniqueidentifier NOT NULL,
>   trigram_vector tsvector NOT NULL,
>   CONSTRAINT packet_search_trigram_id_pkey PRIMARY KEY (id),
>   CONSTRAINT packet_search_trigram_puuid_fkey FOREIGN KEY (packet_uuid)
>       REFERENCES dpo.packet (packet_uuid) MATCH SIMPLE
>       ON UPDATE CASCADE ON DELETE CASCADE
> )
> WITH (
>   OIDS=FALSE
> );
> ALTER TABLE dpo.packet_search_trigram OWNER TO postgres;
> GRANT ALL ON TABLE dpo.packet_search_trigram TO postgres WITH GRANT OPTION;
> GRANT ALL ON TABLE dpo.packet_search_trigram TO addgroup;
>
> CREATE INDEX packet_search_trigram_packet_uuid_idx
>   ON dpo.packet_search_trigram
>   USING hash
>   (packet_uuid);
>
> CREATE INDEX packet_search_trigram_trigram_vector_idx
>   ON dpo.packet_search_trigram
>   USING gin
>   (trigram_vector);


You might want to take a look at upgrading to 8.4.2 per this from the release
notes:


"Fix hash index corruption (Tom)

The 8.4 change that made hash indexes keep entries sorted by hash value failed
to update the bucket splitting and compaction routines to preserve the
ordering. So application of either of those operations could lead to permanent
corruption of an index, in the sense that searches might fail to find entries
that are present. To deal with this, it is recommended to REINDEX any hash
indexes you may have after installing this update. "

See also:
http://archives.postgresql.org/pgsql-committers/2009-11/msg00002.php




--
Adrian Klaver
aklaver@comcast.net

Adrian Klaver <aklaver@comcast.net> writes:
> On Thursday 17 December 2009 6:39:45 pm CG wrote:
>> CREATE INDEX packet_search_trigram_packet_uuid_idx
>> ON dpo.packet_search_trigram
>> USING hash
>> (packet_uuid);

> You might want to take a look at upgrading to 8.4.2 per this from the release
> notes:

Actually, what I'd recommend is dropping that hash index and replacing
it with regular btree.  And the same for any other hash indexes you
have.  Hash indexes simply are not production grade (yet).

            regards, tom lane