Re: Restore referencial integrity

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(dot)rembialkowski(at)gmail(dot)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(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/
>

--
Reimer
47-3347-1724 47-9183-0547 msn: carlos(dot)reimer(at)opendb(dot)com(dot)br