From: Axel Rau <Axel.Rau@Chaos1.DE>
To: Michael Fuhr <mike@fuhr.org>
Cc: daryl@brandywine.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Updating cidr column with network operator
Date: Fri, 23 Sep 2005 21:19:25 +0200
Message-ID: <4a95b442a12d5b4f3cc508868a13aba5@Chaos1.DE> (raw)
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>
-----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-----
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: Axel.Rau@Chaos1.DE, mike@fuhr.org, daryl@brandywine.com
Subject: Re: Updating cidr column with network operator
In-Reply-To: <4a95b442a12d5b4f3cc508868a13aba5@Chaos1.DE>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox