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>
Mime-Version: 1.0 (Apple Message framework v622)
Content-Type: multipart/alternative; boundary=Apple-Mail-7--798788253
Message-Id: <413a156ac3889b31bc079d099a110814@Chaos1.DE>
Cc: Michael Fuhr <mike@fuhr.org>, pgsql-sql@postgresql.org
From: Axel Rau <Axel.Rau@Chaos1.DE>
Subject: Re: Updating cidr column with network operator
Date: Tue, 27 Sep 2005 16:44:27 +0200
To: daryl@brandywine.com


--Apple-Mail-7--798788253
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
	charset=US-ASCII;
	format=flowed


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

--Apple-Mail-7--798788253
Content-Transfer-Encoding: 7bit
Content-Type: text/enriched;
	charset=US-ASCII


Am 27.09.2005 um 16:02 schrieb Daryl Richter:


<excerpt>> 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

);

</excerpt>

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):


<fontfamily><param>Courier</param><smaller>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';

</smaller></fontfamily>

Axel


Axel Rau, Frankfurt, Germany                           +49-69-951418-0


--Apple-Mail-7--798788253--