Message-ID: <43395F1F.4070101@brandywine.com>
Date: Tue, 27 Sep 2005 11:02:55 -0400
From: Daryl Richter <daryl@brandywine.com>
Reply-To: daryl@brandywine.com
Organization: Brandywine Asset Management
User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716)
MIME-Version: 1.0
To: Axel.Rau@Chaos1.DE
Cc: mike@fuhr.org, pgsql-sql@postgresql.org
Subject: Re: Updating cidr column with network operator
References: <413a156ac3889b31bc079d099a110814@Chaos1.DE>
In-Reply-To: <413a156ac3889b31bc079d099a110814@Chaos1.DE>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Axel.Rau@Chaos1.DE wrote:
> 
> Am 27.09.2005 um 16:02 schrieb Daryl Richter: 
> 
> 
> 
>>>An attribute is redundant if it repeats a fact that can be learned 
>>>without it.  If one table contains IP addresses and another contains 
>>>networks, then you can associate IP addresses and networks with a 
>>>join of the two tables; indeed, this is how the "fix the network 
>>>column" update works.  Having a network column in the address table 
>>>simply repeats what could be learned through the join. 
>>>
>>>
>>
>>I agree with Michael here.  I think the fundamental problem with your
>>schema is that it is possible to have contradictory data between the
>>network and address table, always a bad situation. 
>>
>>I would replace network.id with a serial type value and make the cidr a
>>separate column, for example: 
>>
>>CREATE TABLE network ( 
>>  id      int         not null  PRIMARY KEY, 
>>  address cidr        not null, 
>>  attr1   varchar(10) null 
>>); 
>>
>>CREATE TABLE address ( 
>>  id         inet    PRIMARY KEY, 
>>  network    int     NOT NULL 
>>  REFERENCES network 
>>); 
>>
>>
> 
> I agree with Michael too, but I understand him differently: What he says is:
> 
> 	"Get rid of the redundancy", 
> which means to me: 
> 	"remove the fk from address to network completly".  
> The attribute "network" is not realy needed because we can always join 
> 	address.id << network.id 
> This reduces the necessary logic to keep things consistent. I still can have
> 
> my cascaded delete in network, have to do it with a trigger. 
> I'm currently looking at performance issues. 
> 
> Introducing a synthetic pk in network does not really make things easier. 
> Instead I introduced an insert/update trigger which prevents from overlaps 
> in network  (which is not as a matter of course for cidr columns, I have
> learnt): 
> 

Ok, I guess, but isn't tit true now that you can insert a new address 
row which doesn't belong to any valid network??

> CREATE OR REPLACE FUNCTION dd.ids_network_update_insert_check() RETURNS
> TRIGGER AS $$ 
>     BEGIN   -- check if new net overlapps with existing one 
>         PERFORM N.id FROM network N WHERE NEW.id << N.id OR NEW.id >> N.id; 
>         IF FOUND THEN 
>             RAISE EXCEPTION '?Attempt to insert overlapping network %',
> NEW.id; 
>             RETURN NULL; 
>         END IF; 
>         RETURN NEW;   
>     END;              
> $$ LANGUAGE 'plpgsql'; 
> 
> Axel 
> 
> Axel Rau, Frankfurt, Germany                           +49-69-951418-0 


-- 
Daryl

"We want great men who, when fortune frowns, will not be discouraged."
     -- Colonel Henry Knox, 1776