Обсуждение: BUG #3243: foreign key constraint not working?

The following bug has been logged online:

Bug reference:      3243
Logged by:
Email address:      dueyduey@freemail.hu
PostgreSQL version: 8.1.8
Operating system:   Linux: Ubuntu 6.06 (Dapper Drake)
Description:        foreign key constraint not working?
Details:

I am experiencing a very interesting, actually very frightening thing: I
have a foreign key constraint along with rows violating it. I guess such a
thing should never happen (except for deferred constraints, which mine is
none).
Here is the definition of the constraint:

ALTER TABLE buclic.owndata ADD CONSTRAINT
rel_34 FOREIGN KEY (contacts_id)
REFERENCES public.contacts (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE;

I checked it:

select * from public.owndata o where not exists(select null from
public.contacts c where c.id = o.contacts_id);

and yes, I got a result set of 52 rows. And yes, the foreign key constraint
is on the table, and it is working (I tried to insert a new record with a
nonexistent contacts_id reference and I got the good old error message

psql:/var/tmp/mydumpfile:2818085: ERROR:  insert or update on table
"owndata" violates foreign key contraint "rel_34"
DETAIL:  Key (contacts_id)=(3008974) is not present in table "contacts".

and the insertion did not happen). And I checked all this using a new
connection (so deferred application of the constraint is impossible).

Please tell me I'm missing something (and also what)...

I cannot reproduce the problem. Dumping and restoring the whole database
will fail adding the foreign key constraint during restore, which is another
evidence of the original database being inconsistent.

thanx

"" <dueyduey@freemail.hu> writes:
> I am experiencing a very interesting, actually very frightening thing: I
> have a foreign key constraint along with rows violating it.

You have not provided enough information to let anyone reproduce or
debug the problem...

FWIW, we recently found a problem in VACUUM FULL that could result in
symptoms a bit similar to this --- do you routinely do VACUUM FULL while
the database is active?

            regards, tom lane