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