Hi Filip,
This was an excellent suggestion. I've run this join and just 2 minutes later got 1000 records to delete.
Will start the deletes for them and then repeat the processing until all are gone. With this approach I did not need to wait for the maintenance window to fix the foreign key.
I think this finishes my issue.
Thank you all!
2010/8/30 Filip Rembiałkowski <filip.rembialkowski@xxxxxxxxx>
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.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
>
> Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
> 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.rembialkowski@xxxxxxxxx
http://filip.rembialkowski.net/
--
Reimer
47-3347-1724 47-9183-0547 msn: carlos.reimer@xxxxxxxxxxxxx
