Re: Updating cidr column with network operator

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Axel Rau <Axel(dot)Rau(at)Chaos1(dot)DE>
Cc: pgsql-sql(at)postgresql(dot)org, daryl(at)brandywine(dot)com
Subject: Re: Updating cidr column with network operator
Date: 2005-09-26 21:33:09
Message-ID: 20050926213309.GA37147@winnie.fuhr.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Peter Eisentraut 2005-09-27 00:16:04 Re: add column if doesn't exist
Previous Message Brandon Metcalf 2005-09-26 20:38:56 add column if doesn't exist