Обсуждение: Adding integers ( > 8 bytes) to an inet

Hello!

I'm having some trouble with the inet data type and its
operators. Right now I'm relying on operations such as

kll=# SELECT '10.0.0.0/24'::inet + (2^(32-24))::integer;
  ?column?
-------------
 10.0.1.0/24
(1 row)

to get the "next" available /24. This works all fine and dandy
for IPv4 since I'll never go beyond what an integer has to offer.
Expanding my application to IPv6 will on the other hand cause me
some trouble since 2^128 won't fit in an integer and not in a
bigint either. I tried numeric;

kll=> SELECT '2000::/48'::inet + (2^(128-96))::numeric(100);
ERROR: operator does NOT exist: inet + numeric
LINE 1: SELECT '2000::/48'::inet + (2^(128-96))::numeric(100);
                                 ^
HINT: No operator matches the given name AND argument type(s). You might need TO ADD explicit type casts.


And poking in pg_operator / pg_type seems to confirm this:

nils=# SELECT (SELECT typname FROM pg_type WHERE typelem=oprleft), oprname, (SELECT typname FROM pg_type WHERE
typelem=oprright)FROM pg_operator WHERE oprleft=(SELECT typelem FROM pg_type WHERE typname='_inet') AND oprname='+'; 
 ?column? | oprname | ?column?
----------+---------+----------
 _inet    | +       | _int8
(1 row)

I could hack together some kluge to loop through, but it all
becomes quite ugly after a while and I would rather see some way
to add a numeric.

Am I doing it the wrong way? Bug?
What to do?

Kind regards,
   Kristian.

--
Kristian Larsson                                        KLL-RIPE
+46 704 264511                          kll@spritelink.net

Kristian

assuming the max size of 8 byte integer is
http://www.postgresql.org/docs/8.1/static/datatype.html

bigint

8 bytes

large-range integer

-9223372036854775808 to 9223372036854775807

i dont know if a IPV6 address
         999999 999999 999999 999999        would fit into
              9,223,372,036,854,775,807 boundary

i think you discovered the bug!

if you feel you have a solution try submitting a patch
http://wiki.postgresql.org/wiki/Submitting_a_Patch

please ping ping pgsql-hackers@postgresql.org

Good Catch!
Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

> Date: Tue, 8 Sep 2009 15:58:25 +0200
> From: kristian@spritelink.net
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Adding integers ( > 8 bytes) to an inet
>
> Hello!
>
> I'm having some trouble with the inet data type and its
> operators. Right now I'm relying on operations such as
>
> kll=# SELECT '10.0.0.0/24'::inet + (2^(32-24))::integer;
> ?column?
> -------------
> 10.0.1.0/24
> (1 row)
>
> to get the "next" available /24. This works all fine and dandy
> for IPv4 since I'll never go beyond what an integer has to offer.
> Expanding my application to IPv6 will on the other hand cause me
> some trouble since 2^128 won't fit in an integer and not in a
> bigint either. I tried numeric;
>
> kll=> SELECT '2000::/48'::inet + (2^(128-96))::numeric(100);
> ERROR: operator does NOT exist: inet + numeric
> LINE 1: SELECT '2000::/48'::inet + (2^(128-96))::numeric(100);
> ^
> HINT: No operator matches the given name AND argument type(s). You might need TO ADD explicit type casts.
>
>
> And poking in pg_operator / pg_type seems to confirm this:
>
> nils=# SELECT (SELECT typname FROM pg_type WHERE typelem=oprleft), oprname, (SELECT typname FROM pg_type WHERE typelem=oprright) FROM pg_operator WHERE oprleft=(SELECT typelem FROM pg_type WHERE typname='_inet') AND oprname='+';
> ?column? | oprname | ?column?
> ----------+---------+----------
> _inet | + | _int8
> (1 row)
>
> I could hack together some kluge to loop through, but it all
> becomes quite ugly after a while and I would rather see some way
> to add a numeric.
>
> Am I doing it the wrong way? Bug?
> What to do?
>
> Kind regards,
> Kristian.
>
> --
> Kristian Larsson KLL-RIPE
> +46 704 264511 kll@spritelink.net
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

---

Get back to school stuff for them and cashback for you. Try Bing now.

Kristian Larsson wrote:

> And poking in pg_operator / pg_type seems to confirm this:
>
> nils=# SELECT (SELECT typname FROM pg_type WHERE typelem=oprleft), oprname, (SELECT typname FROM pg_type WHERE
typelem=oprright)FROM pg_operator WHERE oprleft=(SELECT typelem FROM pg_type WHERE typname='_inet') AND oprname='+'; 
>  ?column? | oprname | ?column?
> ----------+---------+----------
>  _inet    | +       | _int8
> (1 row)

That query is wrong -- type _inet is actually another way to spell
inet[].  What you want is this, that also confirms your problem:

alvherre=# SELECT oprleft::regtype, oprname, oprright::regtype, oprresult::regtype FROM pg_operator WHERE
(oprleft='inet'::regtypeOR oprright = 'inet'::regtype) AND oprname='+'; 
 oprleft | oprname | oprright | oprresult
---------+---------+----------+-----------
 inet    | +       | bigint   | inet
 bigint  | +       | inet     | inet
(2 filas)


> Am I doing it the wrong way? Bug?

I'd say this is just a missing feature.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre@commandprompt.com> writes:
> I'd say this is just a missing feature.

I think the whole thing is a bit of a crock; adding integers to inet
addresses doesn't make a lot of sense logically.  Perhaps what is
really wanted is functions on CIDR net identifiers, for instance

    first_address('10/8') = 10.0.0.0
    last_address('10/8') = 10.255.255.255
    prior_address('10/8') = 9.255.255.255
    next_address('10/8') = 11.0.0.0

which would have obvious extensions to IPv6 without having to bring
numerics into the picture.

What are the actual applications for adding integers to inet addresses?
The one Kristian mentions seems to be covered by next_address(), but
are there others?

            regards, tom lane

On Tue, Sep 08, 2009 at 11:06:36AM -0400, Alvaro Herrera wrote:
> Kristian Larsson wrote:
>
> > And poking in pg_operator / pg_type seems to confirm this:
> >
> > nils=# SELECT (SELECT typname FROM pg_type WHERE typelem=oprleft), oprname, (SELECT typname FROM pg_type WHERE
typelem=oprright)FROM pg_operator WHERE oprleft=(SELECT typelem FROM pg_type WHERE typname='_inet') AND oprname='+'; 
> >  ?column? | oprname | ?column?
> > ----------+---------+----------
> >  _inet    | +       | _int8
> > (1 row)
>
> That query is wrong -- type _inet is actually another way to spell
> inet[].  What you want is this, that also confirms your problem:

Sorry, I'm not intimately familiar with pg_operator ..


> > Am I doing it the wrong way? Bug?
>
> I'd say this is just a missing feature.

Would pgsql-bugs@ be the appropriate forum for a feature request?

I looked through the FAQ and Todo on the wiki and could not find
any already existing items to fix or not fix this.

Kind regards,
    Kristian.

--
Kristian Larsson                                        KLL-RIPE
+46 704 264511                          kll@spritelink.net

On Tue, Sep 08, 2009 at 11:37:02AM -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > I'd say this is just a missing feature.
>
> I think the whole thing is a bit of a crock; adding integers to inet
> addresses doesn't make a lot of sense logically.  Perhaps what is
> really wanted is functions on CIDR net identifiers, for instance
>
>     first_address('10/8') = 10.0.0.0
>     last_address('10/8') = 10.255.255.255
>     prior_address('10/8') = 9.255.255.255
>     next_address('10/8') = 11.0.0.0
>
> which would have obvious extensions to IPv6 without having to bring
> numerics into the picture.
>
> What are the actual applications for adding integers to inet addresses?
> The one Kristian mentions seems to be covered by next_address(), but
> are there others?

My application is a IP address planning tool. The user can make a
request "give me an address from address-pool X" and a stored
procedure will go and find the next available address, it does so
by looping through a prefix, incrementing with the requested
prefix-length for each loop and returning a prefix if it does not
yet exist in the table.

first_address() is basically just host(network('10/8)) while
last_address() is host(broadcast('10/8')), so I see little use
for those. next_address() however, as shown above, seems to fill
my requirements.

For me, as a network engineer, adding an integer to a inet feels
quite natural. Inet is just another representation of a integer
anyway... so I'd really not have a problem with having either a
int16 or being able to add numerics to inets :)

Btw, anyone have a workaround for my issue?

Kind regards,
   Kristian.

--
Kristian Larsson                                        KLL-RIPE
+46 704 264511                          kll@spritelink.net

On Tue, Sep 08, 2009 at 05:58:00PM +0200, Kristian Larsson wrote:
> On Tue, Sep 08, 2009 at 11:37:02AM -0400, Tom Lane wrote:
> > Alvaro Herrera <alvherre@commandprompt.com> writes:
> > > I'd say this is just a missing feature.
> >
> > I think the whole thing is a bit of a crock; adding integers to inet
> > addresses doesn't make a lot of sense logically.  Perhaps what is
> > really wanted is functions on CIDR net identifiers, for instance
> >
> >     first_address('10/8') = 10.0.0.0
> >     last_address('10/8') = 10.255.255.255
> >     prior_address('10/8') = 9.255.255.255
> >     next_address('10/8') = 11.0.0.0
> >
> > which would have obvious extensions to IPv6 without having to bring
> > numerics into the picture.
> >
> > What are the actual applications for adding integers to inet addresses?
> > The one Kristian mentions seems to be covered by next_address(), but
> > are there others?
>
> My application is a IP address planning tool. The user can make a
> request "give me an address from address-pool X" and a stored
> procedure will go and find the next available address, it does so
> by looping through a prefix, incrementing with the requested
> prefix-length for each loop and returning a prefix if it does not
> yet exist in the table.
>
> first_address() is basically just host(network('10/8)) while
> last_address() is host(broadcast('10/8')), so I see little use
> for those. next_address() however, as shown above, seems to fill
> my requirements.
>
> For me, as a network engineer, adding an integer to a inet feels
> quite natural. Inet is just another representation of a integer
> anyway... so I'd really not have a problem with having either a
> int16 or being able to add numerics to inets :)
>
> Btw, anyone have a workaround for my issue?

Answering to my own post *documenting for generations to come*,
guess I could do :

kll=# SELECT broadcast('2000::/32') + 1;
  ?column?
-------------
 2000:1::/32

Not very fugly.. that'll do in the meantime :)

   -K

--
Kristian Larsson                                        KLL-RIPE
+46 704 264511                          kll@spritelink.net

On Tue, Sep 08, 2009 at 05:58:01PM +0200, Kristian Larsson wrote:
> On Tue, Sep 08, 2009 at 11:37:02AM -0400, Tom Lane wrote:
> > I think the whole thing is a bit of a crock; adding integers to inet
> > addresses doesn't make a lot of sense logically.  Perhaps what is
> > really wanted is functions on CIDR net identifiers, for instance
[...]
> For me, as a network engineer, adding an integer to a inet feels
> quite natural. Inet is just another representation of a integer
> anyway... so I'd really not have a problem with having either a
> int16 or being able to add numerics to inets :)

Indeed, it seems similar to the (somewhat arbitrary) decision that
adding an int to a date results that many days being added to it.
Timestamp INTERVALs may be more flexible, but it's a useful shortcut
that I use quite often.

Something to convert to/from a NUMERIC value and INET would seem useful
as well.

--
  Sam  http://samason.me.uk/

On Tue, Sep 08, 2009 at 05:11:02PM +0100, Sam Mason wrote:
> On Tue, Sep 08, 2009 at 05:58:01PM +0200, Kristian Larsson wrote:
> > On Tue, Sep 08, 2009 at 11:37:02AM -0400, Tom Lane wrote:
> > > I think the whole thing is a bit of a crock; adding integers to inet
> > > addresses doesn't make a lot of sense logically.  Perhaps what is
> > > really wanted is functions on CIDR net identifiers, for instance
> [...]
> > For me, as a network engineer, adding an integer to a inet feels
> > quite natural. Inet is just another representation of a integer
> > anyway... so I'd really not have a problem with having either a
> > int16 or being able to add numerics to inets :)
>
> Indeed, it seems similar to the (somewhat arbitrary) decision that
> adding an int to a date results that many days being added to it.
> Timestamp INTERVALs may be more flexible, but it's a useful shortcut
> that I use quite often.
>
> Something to convert to/from a NUMERIC value and INET would seem useful
> as well.

I'd like to reach some form of consensus on what to do about
this.

Do we
a) ignore it and let users use the workarounds?
b) add a next_address() as per Toms suggestion ?
c) add a conversation between NUMERIC and INET so one can add a
NUMERIC to an INET just as is possible today with INTEGERs?

While Tom's suggestion about next_address might be convenient in
certain scenarios I think it would be nice to be able to add a
numeric to an inet. In other database systems you typically don't
have a inet type at all so people who handle IP addresses in
databases are used to working with integers and bit shifting et
al to do all the IP calculations that one might need. Based on
thie, I vote for option C.

What say you? Yay or nay? :)

Kind regards,
   Kristian.

--
Kristian Larsson                                        KLL-RIPE
+46 704 264511                          kll@spritelink.net

Kristian Larsson <kristian@spritelink.net> writes:
> Do we
> a) ignore it and let users use the workarounds?
> b) add a next_address() as per Toms suggestion ?
> c) add a conversation between NUMERIC and INET so one can add a
> NUMERIC to an INET just as is possible today with INTEGERs?

I vote for (a).

It was already pointed out that you can build next_address and the
other related functions out of the existing operations, so
proposal (b) wouldn't buy much.

Proposal (c) is disingenuous because it ignores the fact that NUMERIC
does not have (and cannot easily implement) most of the bitwise
operations that people might think they want here.

            regards, tom lane

On Thu, Sep 10, 2009 at 10:30:49AM -0400, Tom Lane wrote:
> Kristian Larsson <kristian@spritelink.net> writes:
> > Do we
> > c) add a conversation between NUMERIC and INET so one can add a
> > NUMERIC to an INET just as is possible today with INTEGERs?
>
> Proposal (c) is disingenuous because it ignores the fact that NUMERIC
> does not have (and cannot easily implement) most of the bitwise
> operations that people might think they want here.

Huh, good point.  What you want is a finite field; which looks exactly
like what the "bit" type is for.  Why not use that?

You can't cast them to or from numeric which is a bit annoying, but
doesn't seem too hard in principle.

--
  Sam  http://samason.me.uk/