Date: Fri, 23 Sep 2005 11:32:04 -0600
From: Michael Fuhr <mike@fuhr.org>
To: Axel Rau <Axel.Rau@Chaos1.DE>
Cc: daryl@brandywine.com, pgsql-sql@postgresql.org
Subject: Re: Updating cidr column with network operator
Message-ID: <20050923173204.GA80995@winnie.fuhr.org>
References: <ffaabb4676ab075beb4b6d041ec280c4@Chaos1.DE>
	<4332C38E.5040807@brandywine.com>
	<72c14f47f1dca0ae99e559cef56416a3@Chaos1.DE>
	<43331359.1020703@brandywine.com>
	<bbbf1059080c058d91cb869bf4cf750a@Chaos1.DE>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <bbbf1059080c058d91cb869bf4cf750a@Chaos1.DE>

On Fri, Sep 23, 2005 at 06:31:17PM +0200, Axel Rau wrote:
> Networks change during time, being diveded or aggregated or you just 
> enter wrong data during insert.

Have you considered using a CHECK constraint and/or a trigger to
ensure that the network in the network column contains the address
in the id column?  If you have and rejected the idea, what were the
reasons?

> With the UPDATE below, I want to correct the addresses to again point 
> at the right net.

Does the following statement do what you want?  It shouldn't touch
the records with no matching network -- what do you want to happen
in those cases?  This update also might not give the results you
want if more than one network matches.

UPDATE address SET network = n.id FROM network n WHERE address.id << n.id;

> While writing this, I learn that because of the pk in network,
> UPDATEs will be difficult to accomplish (you may need a temporary
> net to park all addresses of a network to be divided, make the
> change in network and use the UPDATE below to adjust A.network. I
> use net '0.0.0.0/32' as 'UNKNOWN' net or for parking.

I'm not sure I understand what you're saying, but if you're concerned
about foreign key violations then making the foreign key constraints
deferrable and deferring them during certain updates might remove
the need for a temporary "parking" network.

-- 
Michael Fuhr