Search Postgresql Archives

8.4.0 bug - failure to enforce a foreign key constraint

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxx>

Attachment: pgpqFGruHOdZ3.pgp
Description: PGP signature


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux