Thread: Strange invalid constrain problem with PostgreSQL 8.3.1

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:

On the table acc_ops.tbl_part_status I have the following constrain
added:

  CONSTRAINT fk_tbl_part_status_2 FOREIGN KEY (part_num)
      REFERENCES acc_mkt.tbl_part_numbers (part_num) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION


Now I seems to have records in the table acc_ops.tbl_part_status
twhere the part_num does not exists in the table
acc_mkt.tbl_part_numbers
This is my query to test :

SELECT * FROM acc_ops.tbl_part_status  WHERE part_num NOT IN (SELECT
part_num FROM acc_mkt.tbl_part_numbers)

The above SQL returns me 2 records.

I don't allow nulls in both of my tables for the part_num field name.


I am a bit puzzled by this, or I must be blind, would the above
constraint not allow that?


Currently I am not be-able to make a test case because data is loaded
from JasperETL from a CSV file in a 18 step upload phase,
but I am working on it to start pin-pointing this.

One other 'proof' I have is that a pg_dump / pg_restore fails on the
exact same table and thus the restore of the DB fails.

Is the a option/setting in PostgreSQL that would allow such a insert
in table acc_ops.tbl_part_status that would invalidate the constrain?
(I Highly doubt that, just wondering how such a thing could happen....)

We are going to upgrade soon to 8.3.5 to see if the problem persists.

Ries

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