Re: URGENT: referential integrity problem

Stephan Szabo wrote:

> 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.

As I checked the problem exists also in the dump from 7.2.3 ( before it was on
7.2.1).I have this file backedup.
By importing in 7.3.1 the pg do not reported errors.
Only for example:
If I import data in oracle after importin it start integrity check and only if no
errors exeists it
commits data. And I was very supprised that pg do not check this thinks.

> 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.

I have also interest to detect and fix the problem. We plane ( and did it) to
use pg for many projects
and we need stable db.

In case you are interested I can give you access to the test server and you will
be able to see
the problem alone (It will take a little time to configure the server, but it is
not problem).

In any case I have interest first to fix the problem and second to fix the data.

regards,
ivan.