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@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.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