Re: [GENERAL] 8.4.0 bug - failure to enforce a foreign key constraint

2009-08-14 Thread Radoslaw Zielinski *EXTERN* 
Albe Laurenz  [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 

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 8.4.0 bug - failure to enforce a foreign key constraint

2009-08-13 Thread Radoslaw Zielinski 
Grzegorz Jaśkiewicz  [2009-08-13 14:23]:
> On Thu, Aug 13, 2009 at 12:36 PM, Radoslaw Zielinski 
> 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 


pgpoKF88MTwR8.pgp
Description: PGP signature

[GENERAL] 8.4.0 bug - failure to enforce a foreign key constraint

2009-08-13 Thread Radoslaw Zielinski 
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 


pgp0BUnxKVQ9K.pgp
Description: PGP signature

Re: [GENERAL] Fwd: sql_type tinterval not supported by DBD::Pg

2008-05-23 Thread Radoslaw Zielinski 
Filip Rembiałkowski <[EMAIL PROTECTED]> [23-05-2008 10:58]:
[...]
> the same with interval type:
[...]
> calling bind_param( 6, '14 days', { 'pg_type' => 1186 } )
> Cannot bind 6, sql_type interval not supported by DBD::Pg

http://rt.cpan.org/Ticket/Display.html?id=36138

-- 
Radosław Zieliński <[EMAIL PROTECTED]>


pgp0xCl8Mnbwq.pgp
Description: PGP signature