Re: Restore referencial integrity

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(dot)dma(at)gmail(dot)com>

> On Mon, Aug 30, 2010 at 5:30 AM, Carlos Henrique Reimer
> <carlos(dot)reimer(at)opendb(dot)com(dot)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(dot)reimer(at)opendb(dot)com(dot)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(dot)dma(at)gmail(dot)com
>

--
Reimer
47-3347-1724 47-9183-0547 msn: carlos(dot)reimer(at)opendb(dot)com(dot)br