In-Reply-To: <20050923173204.GA80995@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>
Mime-Version: 1.0 (Apple Message framework v622)
Content-Type: text/plain; charset=US-ASCII; format=flowed
Message-Id: <4a95b442a12d5b4f3cc508868a13aba5@Chaos1.DE>
Content-Transfer-Encoding: 7bit
Cc: daryl@brandywine.com, pgsql-sql@postgresql.org
From: Axel Rau <Axel.Rau@Chaos1.DE>
Subject: Re: Updating cidr column with network operator
Date: Fri, 23 Sep 2005 21:19:25 +0200
To: Michael Fuhr <mike@fuhr.org>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Am 23.09.2005 um 19:32 schrieb Michael Fuhr:

> 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?
I'm sure this would be the cleanest solution but remember networks 
change.
This constraind would have to update all details (addresses) of a 10/8
being splitted in a 10/9 and a 10.128/9. If this can be done with pg, 
it is above
my current knowledge level. (But feel free to send a suggestion).
The other point is performance. Inserting new addresses is a realtime 
job
while correcting network changes is a daily maintenance job.
>
>> 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?
Yes. Thank you. This was the 1st answer I'm looking for. Just too 
simple.
>  It shouldn't touch
> the records with no matching network -- what do you want to happen
> in those cases?
They will be updated to reference '0.0.0.0/32' (the UNKNOWN net). Will 
this work:
UPDATE address SET network = '0.0.0.0/32' WHERE NOT EXISTS (SELECT 
address.id << network.id);
?
>   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.
>
> 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.
I see. But I have to sort in the orphaned addresses anyway if I delete 
a net
for splitting or aggregating or even the net may be abandoned so its 
children
will have to be moved to the UNKNOWN net.

Axel

Axel Rau, Frankfurt, Germany                           +49-69-951418-0
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (Darwin)

iQEVAwUBQzRVS8Fz9+6bacTRAQKIlwgAn6G8mXkT+vODCW+n9/dUmOB/NYOJVfZL
T7/oiYpSVWz1ApcIbcQii+RvhpEZXvgpHif8i5Nd0yeV2347PKwflttGSiWJxVPt
mVUrYxjfIjAKmYhbOP25aHK/AGqgjgQRrCOosz3Kbzr5OY4kpNhF67oosGDpIVq+
DcC7nx6+QoHkFByBqL7xTlHDNBS98baVCeGDTIeaJOFEsU1u6t+29ORHloicBo6n
3QZz2qLTMVNzcX/mfS6BqV4POOMSza9zMyRApTwM5lwM+HBAOXvMJ0INiGA0hLE0
o+kVa0I0JTBD4RByxt9c66qFtFN5Y6oZFonm+pBA6nRliBIpt2/8ZA==
=fhnD
-----END PGP SIGNATURE-----