Date: Mon, 26 Sep 2005 15:33:09 -0600
From: Michael Fuhr <mike@fuhr.org>
To: Axel Rau <Axel.Rau@Chaos1.DE>
Cc: pgsql-sql@postgresql.org, daryl@brandywine.com
Subject: Re: Updating cidr column with network operator
Message-ID: <20050926213309.GA37147@winnie.fuhr.org>
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>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <f10bbbc30411862e1691fc7a905a8e5e@Chaos1.DE>

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