Hi, Yes, this is a good suggestion but as the table posicoes_controles has 3.71172e+008 rows it will perform 3.71172e+008 selects against table posicoes to check if the protocolo is in table.
I was think something like: explain delete from posicoes_controles where protocolo not in (select protocolo from posicoes); "Seq Scan on posicoes_controles (cost=9929689.38..1180088620108403.70 rows=189165121 width=6)" " Filter: (NOT (subplan))" " SubPlan" " -> Materialize (cost=9929689.38..15217480.18 rows=380245580 width=4)" " -> Seq Scan on posicoes (cost=0.00..8064108.80 rows=380245580 width=4)" Will this work better that a pl/pgsql as you suggested? Or is there something even betther? Thank you! 2010/8/30 George H <george....@gmail.com> > On Mon, Aug 30, 2010 at 5:30 AM, Carlos Henrique Reimer > <carlos.rei...@opendb.com.br> wrote: > > Hi, > > > > We had by mistake dropped the referencial integrety between two huge > tables > > and now I'm facing the following messages when trying to recreate the > > foreign key again: > > > > alter table posicoes_controles add > > CONSTRAINT protocolo FOREIGN KEY (protocolo) > > REFERENCES posicoes (protocolo) MATCH SIMPLE > > ON UPDATE NO ACTION ON DELETE CASCADE; > > > > ERROR: insert or update on table "posicoes_controles" violates foreign > key > > constraint "protocolo" > > DETAIL: Key (protocolo)=(338525035) is not present in table "posicoes". > > ********** Erro ********** > > ERROR: insert or update on table "posicoes_controles" violates foreign > key > > constraint "protocolo" > > SQL state: 23503 > > Detalhe: Key (protocolo)=(338525035) is not present in table "posicoes". > > As the error message tells, the table "posicoes_controles" has values in > > column "protocolo" that are not present in column "protocolo" of table > > "posicoes". This happened because some programs removed rows from table > > "posicoes" while the referencial integrity was dropped. > > > > Now I need to remove all rows from table "posicoes_controles" that has > not > > corresponding row in table "posicoes". > > > > As these are huge tables, almost 100GB each, and the server > > hardware restricted (4GB RAM) I would like a suggestion of which command > > or commands should be used from the performance perspective. > > > > Column "protocolo" is "posicoes" table primary key but is not in any > index > > colum of table "posicoes_controles". > > > > Thank you very much for any help! > > -- > > Reimer > > 47-3347-1724 47-9183-0547 msn: carlos.rei...@opendb.com.br > > > > > > Hi, > > I guess you could consider the following strategy: Halt the server or > lock the table or something so no program is allowed to delete any > rows on the affected tables. Run a PL/SQL script that will remove rows > from "posicoes_controles" whose foreign key is not present in table > "posics." Then re-issue the foreign key constraint. Then unlock the > table or whatever it is you have to do get programs to be able to use > the tables again. > > I hope this helps somewhat. > -- > George H > george....@gmail.com > -- Reimer 47-3347-1724 47-9183-0547 msn: carlos.rei...@opendb.com.br
