Re: URGENT: referential integrity problem

Richard Huxton wrote:

> On Wednesday 29 Jan 2003 12:18 pm, pginfo wrote:
> > I have 2 tables, in the first one I have field that points to the table
> > key from the second.
> [snip]
> > 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?
>
> Might be a damaged index - try a REINDEX (specified in the SQL reference
> manual). I'd also run some tests against your hardware, make sure you don't
> have any memory or disk problems.

No, I do not have any hardware/memory problems and it is not damaged index.In
my forst table I can execute:
select T1.ref_field from table1 T1 where T1.ref_field = 'A1';

and it returns 10 records.
by executing

select T2.id from table2 T2 where T2.id = 'A1';
and it returns 0 rows !

Also T1.ref_field points to T2.id and T2.id is the key for T2.
Also T1.ref_field and T2.id are declared as name.

>
>
> > How can I check the db integrity for all tables at once ( I need to be
> > sure, that do not exists any problems. It is production server).
>
> Simplest way I can think of would be to pg_dump and restore to a test
> installation.

I tryed it many times and pg do not report any errors.

My version is 7.3.1 running on r.h. 7.3.
I execute :
pg_dump > myfile

Then create a new db.
And execute psql -q newdb < myfile.

regards,
ivan.

>
>
> --
> Richard Huxton