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>
