Re: Restore referencial integrity

On Sun, Aug 29, 2010 at 11:30:57PM -0300, Carlos Henrique Reimer wrote:
> Hi,
>
> We had by mistake dropped the referencial integrety between two huge
> tables

Agora o elefante vai pegar! ;)

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

First, if pescioes_controles doesn't already have an index on
protocolo, create such an index. You can do something like

CREATE INDEX CONCURRENTLY ON pescioes_controles(protocolo);

After you have finished the indexing, you'll need to schedule some
down time, cut off all other access to the server, and then run
something like the following:

BEGIN;
DELETE FROM pescioes_controles WHERE NOT EXISTS (
SELECT 1 FROM pesicoes WHERE pesicoes.protocolo = pescioes_controles.protocolo
);
ALTER TABLE posicoes_controles add
CONSTRAINT protocolo FOREIGN KEY (protocolo)
REFERENCES posicoes (protocolo) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE;
COMMIT;

Hope this helps :)

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate