-----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-----
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
