Re: Updating cidr column with network operator

Axel(dot)Rau(at)Chaos1(dot)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