Re: Restore referencial integrity

[Carlos Henrique Reimer <carlos.reimer@xxxxxxxxxxxxx>]

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.dma@xxxxxxxxx>

On Mon, Aug 30, 2010 at 5:30 AM, Carlos Henrique Reimer
<carlos.reimer@xxxxxxxxxxxxx> 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.reimer@xxxxxxxxxxxxx
>
>

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.dma@xxxxxxxxx

-- 
Reimer
47-3347-1724 47-9183-0547 msn: carlos.reimer@xxxxxxxxxxxxx