From: Axel Rau <Axel.Rau@Chaos1.DE>
To: daryl@brandywine.com
Cc: Michael Fuhr <mike@fuhr.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Updating cidr column with network operator
Date: Tue, 27 Sep 2005 16:44:27 +0200
Message-ID: <413a156ac3889b31bc079d099a110814@Chaos1.DE> (raw)
In-Reply-To: <433950E8.2050805@brandywine.com>
References: <ffaabb4676ab075beb4b6d041ec280c4@Chaos1.DE>
<4332C38E.5040807@brandywine.com>
<72c14f47f1dca0ae99e559cef56416a3@Chaos1.DE>
<43331359.1020703@brandywine.com>
<bbbf1059080c058d91cb869bf4cf750a@Chaos1.DE>
<20050923173204.GA80995@winnie.fuhr.org>
<4a95b442a12d5b4f3cc508868a13aba5@Chaos1.DE>
<20050926000541.GA11071@winnie.fuhr.org>
<f10bbbc30411862e1691fc7a905a8e5e@Chaos1.DE>
<20050926213309.GA37147@winnie.fuhr.org>
<433950E8.2050805@brandywine.com>
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):
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
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: Axel.Rau@Chaos1.DE, daryl@brandywine.com, mike@fuhr.org
Subject: Re: Updating cidr column with network operator
In-Reply-To: <413a156ac3889b31bc079d099a110814@Chaos1.DE>
* 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