Re: Restore referencial integrity

I remember when I handled such situations without downtime, in 24/7 HA
setup, to avoid large transactions - You could try SELECT FROM A LEFT
JOIN B WHERE B.ID IS NULL LIMIT 10 -- and use this as a base for
DELETE statement...

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
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Wysłane z mojego urządzenia przenośnego

Filip Rembiałkowski
JID,mailto:filip(dot)rembialkowski(at)gmail(dot)com
http://filip.rembialkowski.net/