From: Daryl Richter <daryl@brandywine.com>
To: Axel.Rau@Chaos1.DE
Cc: mike@fuhr.org
Cc: pgsql-sql@postgresql.org
Subject: Re: Updating cidr column with network operator
Date: Tue, 27 Sep 2005 11:02:55 -0400
Message-ID: <43395F1F.4070101@brandywine.com> (raw)
In-Reply-To: <413a156ac3889b31bc079d099a110814@Chaos1.DE>
References: <413a156ac3889b31bc079d099a110814@Chaos1.DE>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: daryl@brandywine.com, Axel.Rau@Chaos1.DE, mike@fuhr.org
Subject: Re: Updating cidr column with network operator
In-Reply-To: <43395F1F.4070101@brandywine.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox