Re: URGENT: referential integrity problem

On Wed, 29 Jan 2003, pginfo wrote:

> Stephan Szabo wrote:
>
> > On Wed, 29 Jan 2003, pginfo wrote:
> >
> > > Hi,
> > >
> > > I have 2 tables, in the first one I have field that points to the table
> > > key from the second.
> > >
> > > I the forst I have ~ 1M records and in the second 100K.
> > >
> > > I start one update over the first table on anoder field ( not on the
> > > reference field).
> > >
> > > After few min. pg drops with:
> > > ERROR: <unnamed> referential integrity violation - key referenced from
> > > a_table1 not found in a_table2 !
> > >
> > > My questions:
> > >
> > > How is it possible, that pg do not check the references by inserts?
> >
> > It could be a bug, or an old bug if you've upgraded the machine (it
> > doesn't recheck the constraints in most version on dump/restore).
> >
> > Without more information though, I don't think it's possible to speculate.
> >
>
> It is possible.The project started on pg 7.2.1.
> But we executed many times dump/restore to migrate to the new versions.

If you still have the dump you last used to import to 7.3 you might want
to see if a new load of that data has integrity problems. In any case,
since most of the bugs that I know about went the other direction
(disallowing valid changes), I'm still interested in seeing if we can find
out what happened. The table definitions for both tables and the
constraint definition would be useful, and if you happen to know whether
you were likely to have changed the pk row before inserting the fk rows or
if you had the fk rows and then changed the pk row since that'd narrow
down the possible places to look.