Re: Updating cidr column with network operator
От | Axel Rau |
---|---|
Тема | Re: Updating cidr column with network operator |
Дата | |
Msg-id | 4a95b442a12d5b4f3cc508868a13aba5@Chaos1.DE обсуждение исходный текст |
Ответ на | Re: Updating cidr column with network operator (Michael Fuhr <mike@fuhr.org>) |
Ответы |
Re: Updating cidr column with network operator
(Michael Fuhr <mike@fuhr.org>)
|
Список | pgsql-sql |
-----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-----
В списке pgsql-sql по дате отправления: