X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 847A3D1B51A
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 30 Oct 2003 15:05:21 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 70054-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 30 Oct 2003 11:04:54 -0400 (AST)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210])
	by svr1.postgresql.org (Postfix) with ESMTP id 71A1DD1B545
	for <pgsql-sql@postgresql.org>; Thu, 30 Oct 2003 11:04:50 -0400 (AST)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id 801DF351BF; Thu, 30 Oct 2003 07:04:39 -0800 (PST)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id 7EDBC350AD; Thu, 30 Oct 2003 07:04:39 -0800 (PST)
Date: Thu, 30 Oct 2003 07:04:39 -0800 (PST)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Michele Bendazzoli <mickymouse@mickymouse.it>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Bug in Rule+Foreing key constrain?
In-Reply-To: <1067517334.2371.26.camel@mickymouse.sintel>
Message-ID: <20031030070237.W39861@megazone.bigpanda.com>
References: <1067517334.2371.26.camel@mickymouse.sintel>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200310/401
X-Sequence-Number: 15801

On Thu, 30 Oct 2003, Michele Bendazzoli wrote:

> I have found a strange behaviour that I don't know if is a bug or not.
>
> I have three tables:
> * abilitazione with a primary key of (comuneid, cassonettoid, chiaveid)
> * cassonetto with a primary key of (comuneid, cassonettoid)
> * chiave  with a primary key of (comuneid, chiaveid)
>
> and two foreign key from abilitazioni to cassonetto and chiave tables
> respectivly. Here is the picture (I hope it will be clear)
>
>          cassonetto       abilitazioni       chiave
> 	|comuneid    |--> |comuneid    |<---|comuneid|
> PK ->	|cassonettoid|--> |cassonettoid|    |        |
> 		          |chiaveid    |<---|chiaveid|
> 			  |abilitata   |
>
> Whenever I try to insert a row in abilitazione that hasn't a match row
> in cassonetto or abilitazione, an exception is raised (referential
> integrity violation : key referenced from abilitazione not found in
> cassonetto).
>
> So far, so good.
>
> Now if I Add a rule to abilitazione in order to avoid a duplication of a
> row when a user try to insert one with an existing primary key:
>
> CREATE OR REPLACE RULE abilita_ins_rl AS  ON INSERT TO abilitazione
>   WHERE (EXISTS (
>          	SELECT 1
>          	FROM abilitazione
>          	WHERE (((abilitazione.comuneid = new.comuneid )
> 	 	AND (abilitazione.cassonettoid = new.cassonettoid ))
> 	 	AND (abilitazione.chiaveid = new.chiaveid ))))
> 	 DO INSTEAD UPDATE abilitazione SET abilitata = new.abilitata
>          WHERE (((abilitazione.comuneid = new.comuneid )
> 	 AND (abilitazione.cassonettoid = new.cassonettoid ))
> 	 AND (abilitazione.chiaveid = new.chiaveid ));
>
> the constraints related to the foreign kesy disappears (i.e. I'm able to
> insert a row in abilitazioni that hasn't a key referenced in cassonetto
> or abilitazioni).
>
> Is this a bug or a feauture? If is a feature, it is possible to

Hmm, I'd say a bug, but can you send a standalone example that replicates
it?