Re: Updating cidr column with network operator - Mailing list pgsql-sql

From Michael Fuhr
Subject Re: Updating cidr column with network operator
Date
Msg-id 20050926213309.GA37147@winnie.fuhr.org
Whole thread Raw
In response to Re: Updating cidr column with network operator  (Axel Rau <Axel.Rau@Chaos1.DE>)
Responses Re: Updating cidr column with network operator
List pgsql-sql
On Mon, Sep 26, 2005 at 12:34:59PM +0200, Axel Rau wrote:
> Am 26.09.2005 um 02:05 schrieb Michael Fuhr:
> > On Fri, Sep 23, 2005 at 09:19:25PM +0200, Axel Rau wrote:
> > > I'm sure this would be the cleanest solution but remember networks
> > > change.
> >
> > Yes, which is why it's a good idea to automatically propogate those
> > changes to tables that maintain redundant data.
>
> I would not call it redundant but normalized, because network has some
> attributes, common to all addresses in the net, 1st of all the netmask.

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.

> > > > This update also might not give the results you want if more than
> > > > one network matches.
> > >
> > > This is not possible, because the pk of network is the net cidr.
> >
> > Yes, it is possible, because the update's join condition isn't
> > equality but rather containment.  If the network table contains
> > 10.1.0.0/16 and 10.1.0.0/24, then the IP address 10.1.0.1 would
> > match both.
>
> You mean, unique does not imply none-overlapping for data-type
> network? Oh, I didn't know that.
>
> Who is responsible for this func spec? This is completly contra-
> real-world-experience. Can this be re-considered for a future release?

This isn't "completely contra-real-world-experience" -- it's just
contrary to your particular use case.  The networks 10.1.0.0/16 and
10.1.0.0/24 are in fact different, and in some applications having
both in a table's primary key column would be perfectly legitimate.
For example, a table might store network administration information,
where the administrator for 10.1.0.0/16 as a whole is Group X, and
the administrator for 10.1.0.0/24 in particular is Group Y.

-- 
Michael Fuhr


pgsql-sql by date:

Previous
From: "Brandon Metcalf"
Date:
Subject: add column if doesn't exist
Next
From: Peter Eisentraut
Date:
Subject: Re: add column if doesn't exist