Re: Updating cidr column with network operator

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