Hello group,

again I am stuck with a different problem. Let's say I have given a subnet

'10.4.0.0/16'::cidr

and a list of hosts

hosts
--------------
10.4.5.12
10.4.5.13
10.4.5.14

Is there a funciton/select query to yield all ip addresses in the CIDR
subnet except for the "hosts"? Something like

SELECT * FROM '10.4.0.0/15'::cidr AS ip WHERE NOT ip IN (SELECT ip FROM
hosts);

This query would be sufficient if there existed a function which could
take cidr type as input and would output all IP addresses. For example:

INPUT: '192.168.1.240/28'::cidr
OUTPUT: SELECT magicfunction('192.168.1.240/28'::cidr);
192.168.1.240
192.168.1.241
192.168.1.242
192.168.1.243
192.168.1.244
192.168.1.245
192.168.1.246
192.168.1.247
192.168.1.248
192.168.1.249
192.168.1.250
192.168.1.251
192.168.1.252
192.168.1.253
192.168.1.254
192.168.1.255

Is there such a thing in Postgres?

Thanks,
Kind regards,
Johannes

Johannes Bauer schrieb:

Yay, I solved that partly by myself and will post the solution here for
anyone looking into the same problem. First, switch to >postgres-8.1
(8.2 will do).

Then:

CREATE FUNCTION nextips_for(cidr) RETURNS SETOF inet AS
$$
SELECT sub.ip FROM (SELECT set_masklen(((generate_series(1, (2 ^ (32 -
masklen($1)))::integer - 2) + $1)::inet), 32) as ip) AS sub WHERE sub.ip
NOT IN (SELECT ip FROM hosts)
$$ LANGUAGE SQL STABLE STRICT;

Now you can issue:

SELECT nextips_for('10.4.0.0/16') LIMIT 1;

to get the next ip for the subnet.

Postgres is really *very* cool!

Kind regards,
Johannes

Johannes Bauer schrieb:

Yay, I solved that partly by myself and will post the solution here for
anyone looking into the same problem. First, switch to >postgres-8.1
(8.2 will do).

Then:

CREATE FUNCTION nextips_for(cidr) RETURNS SETOF inet AS
$$
SELECT sub.ip FROM (SELECT set_masklen(((generate_series(1, (2 ^ (32 -
masklen($1)))::integer - 2) + $1)::inet), 32) as ip) AS sub WHERE sub.ip
NOT IN (SELECT ip FROM hosts)
$$ LANGUAGE SQL STABLE STRICT;

Now you can issue:

SELECT nextips_for('10.4.0.0/16') LIMIT 1;

to get the next ip for the subnet.

Postgres is really *very* cool!

Kind regards,
Johannes

Johannes Bauer schrieb:

Yay, I solved that partly by myself and will post the solution here for
anyone looking into the same problem. First, switch to >postgres-8.1
(8.2 will do).

Then:

CREATE FUNCTION nextips_for(cidr) RETURNS SETOF inet AS
$$
SELECT sub.ip FROM (SELECT set_masklen(((generate_series(1, (2 ^ (32 -
masklen($1)))::integer - 2) + $1)::inet), 32) as ip) AS sub WHERE sub.ip
NOT IN (SELECT ip FROM hosts)
$$ LANGUAGE SQL STABLE STRICT;

Now you can issue:

SELECT nextips_for('10.4.0.0/16') LIMIT 1;

to get the next ip for the subnet.

Postgres is really *very* cool!

Kind regards,
Johannes

Johannes Bauer schrieb:

Yay, I solved that partly by myself and will post the solution here for
anyone looking into the same problem. First, switch to >postgres-8.1
(8.2 will do).

Then:

CREATE FUNCTION nextips_for(cidr) RETURNS SETOF inet AS
$$
SELECT sub.ip FROM (SELECT set_masklen(((generate_series(1, (2 ^ (32 -
masklen($1)))::integer - 2) + $1)::inet), 32) as ip) AS sub WHERE sub.ip
NOT IN (SELECT ip FROM hosts)
$$ LANGUAGE SQL STABLE STRICT;

Now you can issue:

SELECT nextips_for('10.4.0.0/16') LIMIT 1;

to get the next ip for the subnet.

Postgres is really *very* cool!

Kind regards,
Johannes

Johannes Bauer schrieb:

Yay, I solved that partly by myself and will post the solution here for
anyone looking into the same problem. First, switch to >postgres-8.1
(8.2 will do).

Then:

CREATE FUNCTION nextips_for(cidr) RETURNS SETOF inet AS
$$
SELECT sub.ip FROM (SELECT set_masklen(((generate_series(1, (2 ^ (32 -
masklen($1)))::integer - 2) + $1)::inet), 32) as ip) AS sub WHERE sub.ip
NOT IN (SELECT ip FROM hosts)
$$ LANGUAGE SQL STABLE STRICT;

Now you can issue:

SELECT nextips_for('10.4.0.0/16') LIMIT 1;

to get the next ip for the subnet.

Postgres is really *very* cool!

Kind regards,
Johannes

Johannes Bauer schrieb:

Yay, I solved that partly by myself and will post the solution here for
anyone looking into the same problem. First, switch to >postgres-8.1
(8.2 will do).

Then:

CREATE FUNCTION nextips_for(cidr) RETURNS SETOF inet AS
$$
SELECT sub.ip FROM (SELECT set_masklen(((generate_series(1, (2 ^ (32 -
masklen($1)))::integer - 2) + $1)::inet), 32) as ip) AS sub WHERE sub.ip
NOT IN (SELECT ip FROM hosts)
$$ LANGUAGE SQL STABLE STRICT;

Now you can issue:

SELECT nextips_for('10.4.0.0/16') LIMIT 1;

to get the next ip for the subnet.

Postgres is really *very* cool!

Kind regards,
Johannes

Johannes Bauer schrieb:

Yay, I solved that partly by myself and will post the solution here for
anyone looking into the same problem. First, switch to >postgres-8.1
(8.2 will do).

Then:

CREATE FUNCTION nextips_for(cidr) RETURNS SETOF inet AS
$$
SELECT sub.ip FROM (SELECT set_masklen(((generate_series(1, (2 ^ (32 -
masklen($1)))::integer - 2) + $1)::inet), 32) as ip) AS sub WHERE sub.ip
NOT IN (SELECT ip FROM hosts)
$$ LANGUAGE SQL STABLE STRICT;

Now you can issue:

SELECT nextips_for('10.4.0.0/16') LIMIT 1;

to get the next ip for the subnet.

Postgres is really *very* cool!

Kind regards,
Johannes

Johannes Bauer schrieb:

Yay, I solved that partly by myself and will post the solution here for
anyone looking into the same problem. First, switch to >postgres-8.1
(8.2 will do).

Then:

CREATE FUNCTION nextips_for(cidr) RETURNS SETOF inet AS
$$
SELECT sub.ip FROM (SELECT set_masklen(((generate_series(1, (2 ^ (32 -
masklen($1)))::integer - 2) + $1)::inet), 32) as ip) AS sub WHERE sub.ip
NOT IN (SELECT ip FROM hosts)
$$ LANGUAGE SQL STABLE STRICT;

Now you can issue:

SELECT nextips_for('10.4.0.0/16') LIMIT 1;

to get the next ip for the subnet.

Postgres is really *very* cool!

Kind regards,
Johannes

Johannes Bauer schrieb:

Very much so. But is so powerful I do not really udnerstand what it's
doing. Although my problem is solved, there is still something curious
which made me wonder. When finding the correct query, I played around (a
lot!). During my way, I found a statement which worked, but was very
slow. This does not require any extra tables so you can play around with
it immediately:

SELECT ip FROM (SELECT set_masklen(('192.0.0.0/8'::cidr + series)::inet,
32) AS ip FROM generate_series(1, (2 ^ (32 -
masklen('192.0.0.0/8'::cidr)))::integer - 2) AS series) AS sub WHERE ip
NOT IN ('192.0.0.4'::inet, '192.0.0.8'::inet) LIMIT 10;

Then, here is the blazingly fast one (which I use in the final version):

SELECT sub.ip FROM (SELECT set_masklen(((generate_series(1, (2 ^ (32 -
masklen('192.0.0.0/8'::cidr)))::integer - 2) +
'192.0.0.0/8'::cidr)::inet), 32) as ip) AS sub WHERE sub.ip NOT IN
('192.0.0.4'::inet, '192.0.0.8'::inet) LIMIT 10;

They look pretty similar, but the slow one takes about 10 seconds, the
fast one returns almost immediately. I guess this is because the slow
query first builds up the whole 16777214 addresses by generate_series(),
then starts removing the 192.0.0.4 and 192.0.0.8 and only then limits.

The fast one seems to make the LIMIT clause effective immediately, so it
is done very fast.

I would therefore guess that without the LIMIT clause both would be
(almost) equally fast. Looking at the execution plans for the slow version:

Limit (cost=0.00..0.30 rows=10 width=4)
-> Function Scan on generate_series series (cost=0.00..29.93
rows=990 width=4)
Filter: (set_masklen(('192.0.0.0/8'::inet + (series)::bigint),
32) <> ALL ('{192.0.0.4,192.0.0.8}'::inet[]))

and with the ANALYZE clause:

Limit (cost=0.00..0.30 rows=10 width=4) (actual
time=6701.258..6701.273 rows=10 loops=1)
-> Function Scan on generate_series series (cost=0.00..29.93
rows=990 width=4) (actual time=6701.257..6701.270 rows=10 loops=1)
Filter: (set_masklen(('192.0.0.0/8'::inet + (series)::bigint),
32) <> ALL ('{192.0.0.4,192.0.0.8}'::inet[]))
Total runtime: 8183.585 ms


and for the fast version:

Limit (cost=0.00..0.03 rows=1 width=32)
-> Subquery Scan sub (cost=0.00..0.03 rows=1 width=32)
Filter: (sub.ip <> ALL ('{192.0.0.4,192.0.0.8}'::inet[]))
-> Result (cost=0.00..0.02 rows=1 width=0)

with analyze:

Limit (cost=0.00..0.03 rows=1 width=32) (actual time=0.010..0.022
rows=10 loops=1)
-> Subquery Scan sub (cost=0.00..0.03 rows=1 width=32) (actual
time=0.009..0.019 rows=10 loops=1)
Filter: (sub.ip <> ALL ('{192.0.0.4,192.0.0.8}'::inet[]))
-> Result (cost=0.00..0.02 rows=1 width=0) (actual
time=0.006..0.014 rows=12 loops=1)
Total runtime: 0.039 ms

Does someone know why those two (almost identical) statements produce so
different runtimes?

Thanks in advance,
Kind regards,
Johannes