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.rembialkow...@gmail.com> > 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 <http://b.id/> IS NULL LIMIT 10 -- and use this as a > base for > DELETE statement... > > 2010/8/30, George H <george....@gmail.com>: > > On Mon, Aug 30, 2010 at 5:30 AM, Carlos Henrique Reimer > > <carlos.rei...@opendb.com.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.rei...@opendb.com.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....@gmail.com > > > > -- > > Sent via pgsql-general mailing list (pgsql-general@postgresql.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.rembialkow...@gmail.com > http://filip.rembialkowski.net/ > -- Reimer 47-3347-1724 47-9183-0547 msn: carlos.rei...@opendb.com.br
