From: Michael Fuhr <mike@fuhr.org>
To: Axel Rau <Axel.Rau@Chaos1.DE>
Cc: pgsql-sql@postgresql.org
Cc: daryl@brandywine.com
Subject: Re: Updating cidr column with network operator
Date: Mon, 26 Sep 2005 15:33:09 -0600
Message-ID: <20050926213309.GA37147@winnie.fuhr.org> (raw)
In-Reply-To: <f10bbbc30411862e1691fc7a905a8e5e@Chaos1.DE>
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>
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
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: mike@fuhr.org, Axel.Rau@Chaos1.DE, daryl@brandywine.com
Subject: Re: Updating cidr column with network operator
In-Reply-To: <20050926213309.GA37147@winnie.fuhr.org>
* 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