Re: Strange invalid constrain problem with PostgreSQL 8.3.1

ries van Twisk wrote:
> hey All,
>
> (Resend from novice)
>
> I if a problem that apparently I can insert a record into my table with
> a constrain while in fact the reference doesn't exist:

Well, a test case is what's really needed, but while you're busy with
that the obvious points are:

1. Your constraint looks ok in so far as I can tell.
2. You've said you have not-null on both columns, so that can be ruled out.

That leaves:

3. Bug in 8.3.1 - might be worth checking the release notes in the manuals.
4. Corrupted database - in particular one of the indexes that might be
used to check the constraint. That seems most likely given that a
dump/restore fails where you'd expect it to.

You could try a REINDEX on both tables and see if that corrects the
situation. Then, check your hardware and make sure you're not running
with fsync off or have a disk-cache lying to you about syncing.

--
  Richard Huxton
  Archonet Ltd