Thread: RI and restoring dumps
Has anyone experienced referential integrity (RI) problems while restoring data from a dump? Like, if the dump doesn't restore the data in the right order, then primary keys might not be in place before the foreign keys are restored. I want to know this from people who have experience before I do use RI! Actually I did use RI in a database but had complications with it (I forget exactly what it was, something like the order of PL/SQL procedures and RI triggers firing caused some problems) but removed them all and just tried implementing all the checks I wanted in my PL/SQL triggers myself. -- -------- Robert B. Easter reaster@comptechnews.com --------- - CompTechNews Message Board http://www.comptechnews.com/ - - CompTechServ Tech Services http://www.comptechserv.com/ - ---------- http://www.comptechnews.com/~reaster/ ------------
On Wednesday 13 December 2000 17:04, Robert B. Easter wrote: > Has anyone experienced referential integrity (RI) problems while restoring > data from a dump? Like, if the dump doesn't restore the data in the right > order, then primary keys might not be in place before the foreign keys are > restored. I want to know this from people who have experience before I do > use RI! > > Actually I did use RI in a database but had complications with it (I forget > exactly what it was, something like the order of PL/SQL procedures and RI > triggers firing caused some problems) but removed them all and just tried > implementing all the checks I wanted in my PL/SQL triggers myself. On Wed, Dec 13, 2000 at 11:38:18AM -0800, Stuart Statman wrote: [ . . . ] > I would suggest, instead, to create a table that represents your hierarchy > without adding columns. For example : > > create table Category ( > CategoryID int4 not null primary key, > ParentCategoryID int4 not null REFERENCES Category (CategoryID), > CategoryName varchar(100) > ); > > Add a CategoryID with an FK reference to this table, and your work is done. > > Then adding, inserting, removing, or moving layers in the hierarchy becomes > quite simple. This also preserves hierarchical integrity, where subcategory > a of subcategory b will also remain a subcategory of category c if > subcategory b is a subcategory of subcategory c, where I'm not sure your > model will preserve or guarantee that. (Does that sentence deserve a prize?) Ok, I'm replying to my own post here, but I was reading another message (above) about a FOREIGN KEY that REFERENCES the same table as it is in. When doing a restore of this table, I guess you would have to disable those RI checks. I know it can be done (right?), but how? Or is this really automatic and not to be worried about!? :) -- -------- Robert B. Easter reaster@comptechnews.com --------- - CompTechNews Message Board http://www.comptechnews.com/ - - CompTechServ Tech Services http://www.comptechserv.com/ - ---------- http://www.comptechnews.com/~reaster/ ------------
On Wed, Dec 13, 2000 at 05:30:49PM -0500, Robert B. Easter wrote: > On Wednesday 13 December 2000 17:04, Robert B. Easter wrote: > > Has anyone experienced referential integrity (RI) problems while restoring > > data from a dump? Like, if the dump doesn't restore the data in the right > > order, then primary keys might not be in place before the foreign keys are > > restored. I want to know this from people who have experience before I do > > use RI! > > Ok, I'm replying to my own post here, but I was reading another message > (above) about a FOREIGN KEY that REFERENCES the same table as it is in. When > doing a restore of this table, I guess you would have to disable those RI > checks. I know it can be done (right?), but how? Or is this really > automatic and not to be worried about!? :) The triggers by whick FK constraints are implemented are the very last things in the dumpfile, so the data would be loaded into the database without any constraint checking. I shouldn't think it possible to have RI problems with this method of restoration assuming the table data was consistent when it was dumped. -- Matt Beauregard Information Technology Operations, DesignScape Ph: +61 2 9361 4233 Fx: +61 2 9361 4633
On Wednesday 13 December 2000 17:45, Matt Beauregard wrote: > On Wed, Dec 13, 2000 at 05:30:49PM -0500, Robert B. Easter wrote: > > On Wednesday 13 December 2000 17:04, Robert B. Easter wrote: > > > Has anyone experienced referential integrity (RI) problems while > > > restoring data from a dump? Like, if the dump doesn't restore the data > > > in the right order, then primary keys might not be in place before the > > > foreign keys are restored. I want to know this from people who have > > > experience before I do use RI! > > > > Ok, I'm replying to my own post here, but I was reading another message > > (above) about a FOREIGN KEY that REFERENCES the same table as it is in. > > When doing a restore of this table, I guess you would have to disable > > those RI checks. I know it can be done (right?), but how? Or is this > > really automatic and not to be worried about!? :) > > The triggers by whick FK constraints are implemented are the very last > things in the dumpfile, so the data would be loaded into the database > without any constraint checking. I shouldn't think it possible to > have RI problems with this method of restoration assuming the table > data was consistent when it was dumped. Ok, that makes perfect sense. Thanks. I see that pg_dump and pg_dumpall can dump just the schema (no data) or just the data (no schema). I was thinking, sometimes it would be nice to dump just the TABLE schema into one file, then all the data into a second file, and then all the functions/triggers/rules into another file. The restore would be, (1) restore table schema, (2) restore data to tables, and then (3) restore all functions/triggers/rules/views and procedural languages. pg_dump just puts the tables and functions all together when doing a schema dump. I guess it's not that hard to manually break that file into two after the last table. I have used a three-file method for my database. So, I'd use pg_dump to dump just the data into a file. Then I'd have the table schema (without functions etc) in one file, and another file containing all my PL/SQL functions etc. But, I would not use pg_dump to get the the table schema and functions, again I'd just have to leave those two hand-written files sitting around and load the data inbetween running those two files. I've done it this way so I can make small changes to the procedures or tables if I have to. I'm curious how other people handle their backup procedures, especially when they have many stored procedures and/or RI keys. -- -------- Robert B. Easter reaster@comptechnews.com --------- - CompTechNews Message Board http://www.comptechnews.com/ - - CompTechServ Tech Services http://www.comptechserv.com/ - ---------- http://www.comptechnews.com/~reaster/ ------------
> Has anyone experienced referential integrity (RI) problems while > restoring data from a dump? Like, if the dump doesn't restore the > data in the right order, then primary keys might not be in place > before the foreign keys are restored. I want to know this from people > who have experience before I do use RI! > > Actually I did use RI in a database but had complications with it (I > forget exactly what it was, something like the order of PL/SQL > procedures and RI triggers firing caused some problems) but removed > them all and just tried implementing all the checks I wanted in my > PL/SQL triggers myself. I never been unable to restore from a dump due to any RI problems. I don't have a terribly complex db--about 100 tables, about 100 FK relationships--but during development, it was dumped and restored about 40 times. (I have seen a dump that didn't restore having to do w/a VIEW that output its definition in such a way that it couldn't be reCREATEd, but, with a little help from a human, could be rewritten.) -- Joel Burton, Director of Information Systems -*- jburton@scw.org Support Center of Washington (www.scw.org)