Обсуждение: 8.4.0 bug - failure to enforce a foreign key constraint

Hello,

I have reported this yesterday via WWW as bug 4979, but I can't see it
in the -bugs archive.  Has it been lost or are the bug reports being
moderated...?

Anyway.  Pg 8.4.0 from yum.postgresql.org running on 64bit RHEL 5.3.

  radek=# \d kandydaci
             Table "public.kandydaci"
        Column       |       Type       | Modifiers
  -------------------+------------------+-----------
   id_rekordu        | bigint           | not null
   id_osoby          | integer          | not null
   id_rodzaju_adresu | smallint         |
   score             | double precision | not null
  Indexes:
      "kandydaci_pkey" PRIMARY KEY, btree (id_rekordu, id_osoby)
  Check constraints:
      "c_kandydaci_score" CHECK (score >= 0::double precision AND score <= 1::double precision)
  Foreign-key constraints:
      "kandydaci_fk_id_rekordu" FOREIGN KEY (id_rekordu) REFERENCES rekordy(id) ON DELETE CASCADE

  radek=# select count(*), sum((r.id is null)::int) as orphans from kandydaci k left join rekordy r on
r.id=k.id_rekordu;
   count | orphans
  -------+---------
    1472 |     152
  (1 row)

The "orphans" count should be 0, obviously.

This table is only inserted into, never updated.  These rows should have
been deleted by the CASCADE constraint: table "rekordy" references table
"tasks" (also with ON DELETE CASCADE), and some "tasks" were deleted.

Judging from the IDs, this has happened multiple times (at least twice).

It's a test database with very low load.  Some complex SELECT queries,
bulk inserts, 99.9% non-conflicting transactions (users work on their
own parts of the data, as defined by the task_id).  No weird stuff has
been done to this cluster.

Loaded modules: plpgsql, plperl, dblink, fuzzystrmatch, hstore-new.


Any ideas?  I have not been able to reproduce it, unfortunately.

--
Radosław Zieliński <radek@pld-linux.org>

On Thu, Aug 13, 2009 at 12:36 PM, Radoslaw Zielinski<radek@pld-linux.org> wrote:
> Hello,
>
> I have reported this yesterday via WWW as bug 4979, but I can't see it
> in the -bugs archive.  Has it been lost or are the bug reports being
> moderated...?
>
> Anyway.  Pg 8.4.0 from yum.postgresql.org running on 64bit RHEL 5.3.
>
>  radek=# \d kandydaci
>             Table "public.kandydaci"
>        Column       |       Type       | Modifiers
>  -------------------+------------------+-----------
>   id_rekordu        | bigint           | not null
>   id_osoby          | integer          | not null
>   id_rodzaju_adresu | smallint         |
>   score             | double precision | not null
>  Indexes:
>      "kandydaci_pkey" PRIMARY KEY, btree (id_rekordu, id_osoby)
>  Check constraints:
>      "c_kandydaci_score" CHECK (score >= 0::double precision AND score <= 1::double precision)
>  Foreign-key constraints:
>      "kandydaci_fk_id_rekordu" FOREIGN KEY (id_rekordu) REFERENCES rekordy(id) ON DELETE CASCADE
>
>  radek=# select count(*), sum((r.id is null)::int) as orphans from kandydaci k left join rekordy r on
r.id=k.id_rekordu;
>   count | orphans
>  -------+---------
>    1472 |     152
>  (1 row)
since you do LEFT JOIN, indeed you can get r.id to be null.


--
GJ

Grzegorz Jaśkiewicz <gryzman@gmail.com> [2009-08-13 14:23]:
> On Thu, Aug 13, 2009 at 12:36 PM, Radoslaw Zielinski<radek@pld-linux.org> wrote:
[...]
>>      "kandydaci_fk_id_rekordu" FOREIGN KEY (id_rekordu) REFERENCES rekordy(id) ON DELETE CASCADE
[...]
> since you do LEFT JOIN, indeed you can get r.id to be null.

There is a foreign key on this field, and it's the only one used in the
JOIN condition.  LEFT was only used to demonstrate the issue in a single
query.

--
Radosław Zieliński <radek@pld-linux.org>

Radoslaw Zielinski wrote:
>   radek=# \d kandydaci
>              Table "public.kandydaci"
>         Column       |       Type       | Modifiers 
>   -------------------+------------------+-----------
>    id_rekordu        | bigint           | not null
>    id_osoby          | integer          | not null
>    id_rodzaju_adresu | smallint         | 
>    score             | double precision | not null
[...]
>   Foreign-key constraints:
>       "kandydaci_fk_id_rekordu" FOREIGN KEY (id_rekordu) REFERENCES rekordy(id) ON DELETE CASCADE
> 
>   radek=# select count(*), sum((r.id is null)::int) as 
> orphans from kandydaci k left join rekordy r on r.id=k.id_rekordu;
>    count | orphans 
>   -------+---------
>     1472 |     152
>   (1 row)
> 
> The "orphans" count should be 0, obviously.

Just to make sure that there is really an inconsistency:

Could you pg_dump both tables and try to load them into
another database? If that works without errors, we must have
missed something obvious.

Yours,
Laurenz Albe

Albe Laurenz <laurenz.albe@wien.gv.at> [2009-08-14 07:34]:
> Radoslaw Zielinski wrote:
[...]
>> The "orphans" count should be 0, obviously.
> Just to make sure that there is really an inconsistency:

> Could you pg_dump both tables and try to load them into
> another database? If that works without errors, we must have
> missed something obvious.

Yes, I did that.  In fact, that's how I have noticed this -- by reading
pg_dump's output.

--
Radosław Zieliński <radek@pld-linux.org>