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 CASCADEradek=# 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 CASCADEradek=# 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>