Re: Summary: what to do about INET/CIDR

After reviewing a number of past threads about the INET/CIDR mess,
I have concluded that we should adopt the following behavior:

1. A data value like '10.1.2.3/16' is a legal INET value (it implies
the host 10.1.2.3 in the network 10.1/16) but not a legal CIDR value.
Hence, cidr_in should reject such a value. Up to now it hasn't.

2. We do not have a datatype corresponding strictly to a host address
alone --- to store a plain address, use INET and let the mask width
default to 32. inet_out suppresses display of a "/32" netmask (whereas
cidr_out does not).

3. Given that CIDRs never have invalid bits set, we can use the same
ordering rules for both datatypes: sort by address part, then by
number of bits. This is compatible with what 7.0 did when sorting.
It is *not* quite the same as what current sources do, but I will revert
that change.

I didn't see anyone objecting to this scheme in past discussions, but
I also didn't see any clear statement that all the interested parties
had agreed to it. Last chance to complain...

regards, tom lane

* Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> [001026 18:46]:
> After reviewing a number of past threads about the INET/CIDR mess,
> I have concluded that we should adopt the following behavior:
>
> 1. A data value like '10.1.2.3/16' is a legal INET value (it implies
> the host 10.1.2.3 in the network 10.1/16) but not a legal CIDR value.
> Hence, cidr_in should reject such a value. Up to now it hasn't.
>
> 2. We do not have a datatype corresponding strictly to a host address
> alone --- to store a plain address, use INET and let the mask width
> default to 32. inet_out suppresses display of a "/32" netmask (whereas
> cidr_out does not).
>
> 3. Given that CIDRs never have invalid bits set, we can use the same
> ordering rules for both datatypes: sort by address part, then by
> number of bits. This is compatible with what 7.0 did when sorting.
> It is *not* quite the same as what current sources do, but I will revert
> that change.
>
> I didn't see anyone objecting to this scheme in past discussions, but
> I also didn't see any clear statement that all the interested parties
> had agreed to it. Last chance to complain...
I'd like to see a way to get all 4 octets of a CIDR printed out...

Also a way to get network (.0) and broadcast (all ones) for a cidr
block out of our stuff.

Larry
>
> regards, tom lane
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 (voice) Internet: ler(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

makes sense to me

On Thu, 26 Oct 2000, Tom Lane wrote:

> After reviewing a number of past threads about the INET/CIDR mess,
> I have concluded that we should adopt the following behavior:
>
> 1. A data value like '10.1.2.3/16' is a legal INET value (it implies
> the host 10.1.2.3 in the network 10.1/16) but not a legal CIDR value.
> Hence, cidr_in should reject such a value. Up to now it hasn't.
>
> 2. We do not have a datatype corresponding strictly to a host address
> alone --- to store a plain address, use INET and let the mask width
> default to 32. inet_out suppresses display of a "/32" netmask (whereas
> cidr_out does not).
>
> 3. Given that CIDRs never have invalid bits set, we can use the same
> ordering rules for both datatypes: sort by address part, then by
> number of bits. This is compatible with what 7.0 did when sorting.
> It is *not* quite the same as what current sources do, but I will revert
> that change.
>
> I didn't see anyone objecting to this scheme in past discussions, but
> I also didn't see any clear statement that all the interested parties
> had agreed to it. Last chance to complain...
>
> regards, tom lane
>
>

Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy(at)hub(dot)org secondary: scrappy(at){freebsd|postgresql}.org

Larry Rosenman <ler(at)lerctr(dot)org> writes:
> Also a way to get network (.0) and broadcast (all ones) for a cidr
> block out of our stuff.

network() and broadcast() have been there all along ...

regards, tom lane

* Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> [001027 09:49]:
> Larry Rosenman <ler(at)lerctr(dot)org> writes:
> > Also a way to get network (.0) and broadcast (all ones) for a cidr
> > block out of our stuff.
>
> network() and broadcast() have been there all along ...
but don't work on CIDR types.....

LER

>
> regards, tom lane
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 (voice) Internet: ler(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

* Larry Rosenman <ler(at)lerctr(dot)org> [001027 09:51]:
> * Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> [001027 09:49]:
> > Larry Rosenman <ler(at)lerctr(dot)org> writes:
> > > Also a way to get network (.0) and broadcast (all ones) for a cidr
> > > block out of our stuff.
> >
> > network() and broadcast() have been there all along ...
> but don't work on CIDR types.....
And I get to be wrong.

Sorry about that.

But, it would still be nice if we can force all 4 octets to be printed
for the network funcs..

LER

>
> LER
>
> >
> > regards, tom lane
> --
> Larry Rosenman http://www.lerctr.org/~ler
> Phone: +1 972-414-9812 (voice) Internet: ler(at)lerctr(dot)org
> US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 (voice) Internet: ler(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

* Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> [001027 09:49]:
> Larry Rosenman <ler(at)lerctr(dot)org> writes:
> > Also a way to get network (.0) and broadcast (all ones) for a cidr
> > block out of our stuff.
>
> network() and broadcast() have been there all along ...
OK, what I really meant was a way to coerce a CIDR entity to INET so
that host() can work with a CIDR type to print all 4 octets.

Does this help with what I want?

Currently you can't coerce a CIDR type to INET.

>
> regards, tom lane
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 (voice) Internet: ler(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

* Larry Rosenman <ler(at)lerctr(dot)org> [001027 11:08]:
> * Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> [001027 09:49]:
> > Larry Rosenman <ler(at)lerctr(dot)org> writes:
> > > Also a way to get network (.0) and broadcast (all ones) for a cidr
> > > block out of our stuff.
> >
> > network() and broadcast() have been there all along ...
> OK, what I really meant was a way to coerce a CIDR entity to INET so
> that host() can work with a CIDR type to print all 4 octets.
>
> Does this help with what I want?
>
> Currently you can't coerce a CIDR type to INET.
For example, I feel the following should work:

ler=# \d ler_test
Table "ler_test"
Attribute | Type | Modifier
-----------+------+----------
net | cidr |
host | inet |

ler=# select * from ler_test;
net | host
---------------+------------------
207.158.72/24 | 207.158.72.11/24
(1 row)

ler=# select host(net::inet) from ler_test;
ERROR: CIDR type has no host part
ERROR: CIDR type has no host part
ler=#
>
> >
> > regards, tom lane
> --
> Larry Rosenman http://www.lerctr.org/~ler
> Phone: +1 972-414-9812 (voice) Internet: ler(at)lerctr(dot)org
> US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 (voice) Internet: ler(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Tom Lane writes:

> 1. A data value like '10.1.2.3/16' is a legal INET value (it implies
> the host 10.1.2.3 in the network 10.1/16) but not a legal CIDR value.
> Hence, cidr_in should reject such a value. Up to now it hasn't.

Nod.

> 2. We do not have a datatype corresponding strictly to a host address
> alone --- to store a plain address, use INET and let the mask width
> default to 32. inet_out suppresses display of a "/32" netmask (whereas
> cidr_out does not).

Inet is supposed to be host address, with optional network specification.

I also have in my notes (some might have been fixed since):

* inet output is broken => 127.0.0.1/8
* no cast function to "text" available (what about host()?)
* equality/distinctness is broken in certain cases => select
'10.0.0.1/27'::inet='10.0.0.2/27'::inet; returns true
* operator commutators and negators are incorrect
* ouput functions apparently null-terminate their result => select
host('10.0.0.1')='10.0.0.1'; returns false
* comparing inet and cidr is not well defined
* should '127.0.0.1/24'::cidr fail?

--
Peter Eisentraut peter_e(at)gmx(dot)net http://yi.org/peter-e/

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Inet is supposed to be host address, with optional network specification.

Agreed. As such, it probably should always display all 4 octets
regardless of the maskwidth. It doesn't do that at the moment:

regression=# select '127.0.0.1/8'::inet;
?column?
----------
127.0/8
(1 row)

This is clearly bad. I will change it to produce '127.0.0.1/8',
unless someone has a better idea.

> I also have in my notes (some might have been fixed since):

> * inet output is broken => 127.0.0.1/8

See above.

> * no cast function to "text" available

I don't see much point in solving that issue on a one-datatype-at-a-time
basis. Sooner or later we should fix things so that the datatype I/O
conversion functions can be invoked safely in expressions.

> * equality/distinctness is broken in certain cases => select
> '10.0.0.1/27'::inet='10.0.0.2/27'::inet; returns true

This is now fixed.

> * operator commutators and negators are incorrect

Fixed.

> * ouput functions apparently null-terminate their result => select
> host('10.0.0.1')='10.0.0.1'; returns false

Not sure what that has to do with output functions, but I get 'true'
now.

> * comparing inet and cidr is not well defined

Perhaps not. There was a whole lot of argument about that point,
and it didn't seem to me that we came to any real agreement.

> * should '127.0.0.1/24'::cidr fail?

Looks like we've resolved that as "yes".

There are still unresolved issues about whether inet and cidr should be
considered binary-equivalent, what network_sup/sub mean when comparing
inet and cidr, whether we are missing any important functions, etc.
I'm not hoping to get these resolved for 7.1, considering we are nearly
at beta stage and don't even have a complete proposal for what to do.
I'm satisfied for the moment with having eliminated the failure to
compare all bits of the values, which led to bogus equality results
and consequent malfunction of indexes.

regards, tom lane

On Fri, 27 Oct 2000, Larry Rosenman wrote:
> ler=# select * from ler_test;
> net | host
> ---------------+------------------
> 207.158.72/24 | 207.158.72.11/24
> (1 row)
>
> ler=# select host(net::inet) from ler_test;
> ERROR: CIDR type has no host part
> ERROR: CIDR type has no host part
I agree. There should be a coercion function, but it should never be
automatic...But since now there aren't any automatic coercions, that's not
a problem ;)

Also, I agree with Larry that cidr _must_ be printed with 4 octets in
them, whether they are 0 or not. (i.e. it should print 207.158.72.0/24)

This is the standard way of specifying addresses in all network equipment.
RFC specifies that, just the library that we use doesn't (yes, it is from
Vixie, but it doesn't make it RFC-compliant)

I'll submit patches in a week or so, when I start straightening out my
network equipment tables...;)

-alex

* Alex Pilosov <alex(at)pilosoft(dot)com> [001027 14:43]:
> On Fri, 27 Oct 2000, Larry Rosenman wrote:
> > ler=# select * from ler_test;
> > net | host
> > ---------------+------------------
> > 207.158.72/24 | 207.158.72.11/24
> > (1 row)
> >
> > ler=# select host(net::inet) from ler_test;
> > ERROR: CIDR type has no host part
> > ERROR: CIDR type has no host part
> I agree. There should be a coercion function, but it should never be
> automatic...But since now there aren't any automatic coercions, that's not
> a problem ;)
>
> Also, I agree with Larry that cidr _must_ be printed with 4 octets in
> them, whether they are 0 or not. (i.e. it should print 207.158.72.0/24)
>
> This is the standard way of specifying addresses in all network equipment.
> RFC specifies that, just the library that we use doesn't (yes, it is from
> Vixie, but it doesn't make it RFC-compliant)
and network(cidr) should print ONLY the octets, not the mask...

LER

>
> I'll submit patches in a week or so, when I start straightening out my
> network equipment tables...;)
>
> -alex

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 (voice) Internet: ler(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

On Fri, 27 Oct 2000, Larry Rosenman wrote:
> and network(cidr) should print ONLY the octets, not the mask...
Agreed. There's a function to get the mask size, and the network should
just return the network. Otherwise, it is impossible to use.

-alex

Larry Rosenman <ler(at)lerctr(dot)org> writes:
> OK, what I really meant was a way to coerce a CIDR entity to INET so
> that host() can work with a CIDR type to print all 4 octets.

Hm. I don't see any really good reason why host() rejects CIDR input
in the first place. What's wrong with producing the host address
that corresponds to extending the CIDR network address with zeroes?

> Currently you can't coerce a CIDR type to INET.

Well you can, but it doesn't *do* anything. One of the peculiarities
of these two types is that the cidr-vs-inet flag is actually stored
in the data value. The type-system differentiation between CIDR and
INET is a complete no-op for everything except initial entry of a value
(ie, conversion of a text string to CIDR or INET); all the operators
that care (which is darn few ... in fact it looks like host() is the
only one!) look right at the value to see which type they've been given.
So applying a type coercion may make the type system happy, but it
doesn't do a darn thing to the bits, and thus not to the behavior of
subsequent operators either. I have not yet figured out if that's a
good thing or a bad thing ...

regards, tom lane

Alex Pilosov <alex(at)pilosoft(dot)com> writes:
> Also, I agree with Larry that cidr _must_ be printed with 4 octets in
> them, whether they are 0 or not. (i.e. it should print 207.158.72.0/24)

> This is the standard way of specifying addresses in all network equipment.
> RFC specifies that, just the library that we use doesn't (yes, it is from
> Vixie, but it doesn't make it RFC-compliant)

Somehow, I am more inclined to believe Vixie's opinion on this than
either yours or Larry's ;-)

If you think there is an RFC that demands the above behavior and not
what Vixie recommended to us, let's see chapter and verse.

FWIW, the direction we seem to be converging in is that INET will always
print all four octets. Maybe the answer for you is to use INET, rather
than to try to persuade us that you understand CIDR notation better than
Vixie does...

regards, tom lane

* Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> [001027 15:14]:
> Alex Pilosov <alex(at)pilosoft(dot)com> writes:
> > Also, I agree with Larry that cidr _must_ be printed with 4 octets in
> > them, whether they are 0 or not. (i.e. it should print 207.158.72.0/24)
>
> > This is the standard way of specifying addresses in all network equipment.
> > RFC specifies that, just the library that we use doesn't (yes, it is from
> > Vixie, but it doesn't make it RFC-compliant)
>
> Somehow, I am more inclined to believe Vixie's opinion on this than
> either yours or Larry's ;-)
>
> If you think there is an RFC that demands the above behavior and not
> what Vixie recommended to us, let's see chapter and verse.
>
> FWIW, the direction we seem to be converging in is that INET will always
> print all four octets. Maybe the answer for you is to use INET, rather
> than to try to persuade us that you understand CIDR notation better than
> Vixie does...
What I need is a way to convince PG to print all 4 octets from a CIDR
type. I *WANT* the safety of the CIDR type for blocks of addresses,
but need to be able to print all 4 octets out for NON-TECHIES.

LER
>
> regards, tom lane
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 (voice) Internet: ler(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

On Fri, 27 Oct 2000, Tom Lane wrote:

> Larry Rosenman <ler(at)lerctr(dot)org> writes:
> > OK, what I really meant was a way to coerce a CIDR entity to INET so
> > that host() can work with a CIDR type to print all 4 octets.
>
> Hm. I don't see any really good reason why host() rejects CIDR input
> in the first place. What's wrong with producing the host address
> that corresponds to extending the CIDR network address with zeroes?
_maybe_ cuz this is an invalid address. (an address cannot have all-zeros
or all-ones host part). On other hand, postgres doesn't enforce that in
inet_in, so its inconsistent to enforce it there...

> > Currently you can't coerce a CIDR type to INET.
>
> Well you can, but it doesn't *do* anything. One of the peculiarities
> of these two types is that the cidr-vs-inet flag is actually stored
> in the data value. The type-system differentiation between CIDR and
> INET is a complete no-op for everything except initial entry of a value
> (ie, conversion of a text string to CIDR or INET); all the operators
> that care (which is darn few ... in fact it looks like host() is the
> only one!) look right at the value to see which type they've been given.
> So applying a type coercion may make the type system happy, but it
> doesn't do a darn thing to the bits, and thus not to the behavior of
> subsequent operators either. I have not yet figured out if that's a
> good thing or a bad thing ...
Probably cidr_inet should make a copy instead of just "blessing" the
original value?

-alex

BTW, does it strike anyone else as peculiar that the host(),
broadcast(), network(), and netmask() functions yield results
of type text, rather than type inet? Seems like it'd be considerably
more useful if they returned values of type inet with masklen = 32
(except for network(), which would keep the original masklen while
coercing bits to its right to 0).

Given the current proposal that inet_out should always display all 4
octets, and the existing fact that inet_out suppresses display of
a /32 netmask, the textual display of SELECT host(...) etc would
remain the same as it is now. But AFAICS you could do more with
an inet-type result value, like say compare it to other inet or cidr
values ...

Comments? Why was it done this way, anyway?

regards, tom lane

* Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> [001027 17:04]:
> BTW, does it strike anyone else as peculiar that the host(),
> broadcast(), network(), and netmask() functions yield results
> of type text, rather than type inet? Seems like it'd be considerably
> more useful if they returned values of type inet with masklen = 32
> (except for network(), which would keep the original masklen while
> coercing bits to its right to 0).
>
> Given the current proposal that inet_out should always display all 4
> octets, and the existing fact that inet_out suppresses display of
> a /32 netmask, the textual display of SELECT host(...) etc would
> remain the same as it is now. But AFAICS you could do more with
> an inet-type result value, like say compare it to other inet or cidr
> values ...
>
> Comments? Why was it done this way, anyway?
It doesn't bother me, as long as there is someway for me to get from a
CIDR type to 4 octets output with no mask indicated, and print the
broadcast and netmask and bits out separately from ONE column in the
table.

I.E. for select
network('207.158.72.0/24'),broadcast('207.158.72.0/24'),netmask('207.158.72.0/24')
I get

207.158.72.0 207.158.72.255 255.255.255.0

as output.

Aside from that, I'm not picky.

Larry
>
> regards, tom lane
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 (voice) Internet: ler(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Larry Rosenman <ler(at)lerctr(dot)org> writes:
> I.E. for select network('207.158.72.0/24')
> I get
> 207.158.72.0

To my mind that should be done with host(), not network(). If you strip
the masklen information then what you have is no longer a network
specification, so expecting a function named network() to behave that
way strikes me as bizarre.

regards, tom lane

* Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> [001027 17:29]:
> Larry Rosenman <ler(at)lerctr(dot)org> writes:
> > I.E. for select network('207.158.72.0/24')
> > I get
> > 207.158.72.0
>
> To my mind that should be done with host(), not network(). If you strip
> the masklen information then what you have is no longer a network
> specification, so expecting a function named network() to behave that
> way strikes me as bizarre.
Fine, but host() rejects CIDR types right now....

LER
>
> regards, tom lane
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 (voice) Internet: ler(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Larry Rosenman <ler(at)lerctr(dot)org> writes:
> Fine, but host() rejects CIDR types right now....

What's your point? network() doesn't behave the way you want right now,
either.

regards, tom lane

On Fri, 27 Oct 2000, Tom Lane wrote:

> BTW, does it strike anyone else as peculiar that the host(),
> broadcast(), network(), and netmask() functions yield results
> of type text, rather than type inet? Seems like it'd be considerably
> more useful if they returned values of type inet with masklen = 32
> (except for network(), which would keep the original masklen while
> coercing bits to its right to 0).
I absolutely agree, except for network(), which should return cidr.
(after all, this is the network).

As I mentioned in another email, should inet datatype really care whether
host part is all-ones or all-zeros and reject that? It would make sense to
me (10.0.0.0/8::inet is not a valid address, but 10.0.0.0/8::cidr is), but
it would break some people's scripts...

I'm talking here from a perspective of a network provider with P
knowledge...I'm sure Marc can chime in here...

-alex

* Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> [001027 17:41]:
> Larry Rosenman <ler(at)lerctr(dot)org> writes:
> > Fine, but host() rejects CIDR types right now....
>
> What's your point? network() doesn't behave the way you want right now,
> either.
Fine, network() can return CIDR (207.158.72/24), but allow host(cidr)
to print all 4 octets without the mask.

Larry
>
> regards, tom lane
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 (voice) Internet: ler(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Alex Pilosov <alex(at)pilosoft(dot)com> writes:
> On Fri, 27 Oct 2000, Tom Lane wrote:
>> BTW, does it strike anyone else as peculiar that the host(),
>> broadcast(), network(), and netmask() functions yield results
>> of type text, rather than type inet?

> I absolutely agree, except for network(), which should return cidr.

We could do that, but if we did, it would print out per CIDR format
(eg, '192.1/16') whereas both you and Larry have been saying you want
a way to produce '192.1.0.0/16'. Perhaps we need two functions, one
to produce the network in CIDR notation and one to produce it in INET
notation.

For that matter, perhaps we should not change host() to accept CIDR
but instead provide a separate function that does what I proposed
host() should do with a CIDR. Not sure.

> As I mentioned in another email, should inet datatype really care whether
> host part is all-ones or all-zeros and reject that?

I'm inclined to think not, partially because that would mean that the
results of broadcast() and network() could *NOT* be considered valid
INET values.

The way I'm visualizing this, INET is a generalized type that will store
any 4-octet address plus any netmask width from 1 to 32. This includes
not only host addresses, but network specs and broadcast addresses.
CIDR is a subset type that only accepts valid network specs (ie, no
nonzero address bits to the right of the netmask). There is no subset
type that corresponds to "valid host addresses only" --- if there were,
it would be a subset of INET but would have no valid values in common
with CIDR. We could make such a type but I dunno if it's worth the
trouble.

regards, tom lane

I wrote:
> There is no subset type that corresponds to "valid host addresses
> only" --- if there were, it would be a subset of INET but would have
> no valid values in common with CIDR.

I take that back --- CIDR accepts w.x.y.z/32 for any w.x.y.z, which
would include valid host addresses. (But perhaps it should only
accept netmasks shorter than 32 bits? Not sure if "CIDR" is commonly
understood to be network specs only, or network and host specs.)

regards, tom lane

On Fri, 27 Oct 2000, Tom Lane wrote:

> Alex Pilosov <alex(at)pilosoft(dot)com> writes:
> > Also, I agree with Larry that cidr _must_ be printed with 4 octets in
> > them, whether they are 0 or not. (i.e. it should print 207.158.72.0/24)
>
> > This is the standard way of specifying addresses in all network equipment.
> > RFC specifies that, just the library that we use doesn't (yes, it is from
> > Vixie, but it doesn't make it RFC-compliant)
>
> Somehow, I am more inclined to believe Vixie's opinion on this than
> either yours or Larry's ;-)

> If you think there is an RFC that demands the above behavior and not
> what Vixie recommended to us, let's see chapter and verse.

After a long search of RFCs, I could not find any that _mandates_ one way
over the other in all situations. However, in all RFC, whenever an example
of IP addressing is used, the full (10.0.0.0/8) address is used far more
often than compacted (10/8).

I'd give you an example of BIND9, but in its inet_ntop function, it no
longer has the netmask length ;)

All networking software supports full syntax of address. Most of
networking software supports compacted syntax.

Many RFCs relating to the networking software, DO specify that full
version is required:
ftp://ftp.merit.edu/internet/documents/rfc/rfc2622.txt
ftp://ftp.merit.edu/internet/documents/rfc/rfc2673.txt verse 3.2.1

RIPE NCC (the european version of ARIN) also likes the complete version in
their standards documents (refer:
http://www.lir.garr.it/docs/ripe-121.txt across the document

ARIN in their allocation templates, also uses full version:
(again, across the document)
http://www.arin.net/regserv/templates/isptemplate.txt
http://www.arin.net/routingreg/route.html
http://www.arin.net/routingreg/route-set.html

If this doesn't persuade you, I think I'll just ask Vixie to settle this.
:)

-alex

> FWIW, the direction we seem to be converging in is that INET will always
> print all four octets. Maybe the answer for you is to use INET, rather
> than to try to persuade us that you understand CIDR notation better than
> Vixie does...

On Fri, 27 Oct 2000, Tom Lane wrote:

> The way I'm visualizing this, INET is a generalized type that will store
> any 4-octet address plus any netmask width from 1 to 32. This includes
> not only host addresses, but network specs and broadcast addresses.
> CIDR is a subset type that only accepts valid network specs (ie, no
> nonzero address bits to the right of the netmask). There is no subset

I really don't think it should. We should have as much error-checking as
possible. Broadcast address does _not_ have a netmask, i.e. 10.0.0.255/24
does not make sense as inet, it should be 10.0.0.255/32

(ie. broadcast() function must return a value with /32 mask)

> type that corresponds to "valid host addresses only" --- if there were,
> it would be a subset of INET but would have no valid values in common
> with CIDR. We could make such a type but I dunno if it's worth the
> trouble.

Alex Pilosov <alex(at)pilosoft(dot)com> writes:
> We should have as much error-checking as possible.

Only possible with a much tighter definition of what the intended use
of each type is. For example, you seem to be saying that broadcast
addresses aren't valid inet values, with which I do not agree unless
there is another type that they can be part of.

My inclination is to leave INET with the range of valid values it
currently has, and to let people apply column constraints if they
want to restrict a particular column to, say, valid host addresses,
or valid broadcast addresses, or whatever.

> Broadcast address does _not_ have a netmask, i.e. 10.0.0.255/24
> does not make sense as inet, it should be 10.0.0.255/32

How so? Without a netmask you have no way to know if it's a broadcast
address or not. 10.0.0.255/32 might be a perfectly valid host address
in, say, 10.0/16. But 10.0.0.255/24 is recognizably the broadcast
address for 10.0.0/24 (and not for any other network...)

> (ie. broadcast() function must return a value with /32 mask)

I don't disagree with that part, but that's only because I see
broadcast() as mainly a display convenience. If we had a larger and
more thoroughly worked out set of inet/cidr operators, I'd be inclined
to argue that broadcast('10.0.0.0/24') should yield 10.0.0.255/24 for
computational convenience. Then we'd need to offer a separate function
that would let you strip off the netmask for display purposes (actually
host() would do for that...)

regards, tom lane

* Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> [001027 17:54]:
> Alex Pilosov <alex(at)pilosoft(dot)com> writes:
> > On Fri, 27 Oct 2000, Tom Lane wrote:
> >> BTW, does it strike anyone else as peculiar that the host(),
> >> broadcast(), network(), and netmask() functions yield results
> >> of type text, rather than type inet?
>
> > I absolutely agree, except for network(), which should return cidr.
>
> We could do that, but if we did, it would print out per CIDR format
> (eg, '192.1/16') whereas both you and Larry have been saying you want
> a way to produce '192.1.0.0/16'. Perhaps we need two functions, one
> to produce the network in CIDR notation and one to produce it in INET
> notation.
I'd agree with this.
>
> For that matter, perhaps we should not change host() to accept CIDR
> but instead provide a separate function that does what I proposed
> host() should do with a CIDR. Not sure.
>
> > As I mentioned in another email, should inet datatype really care whether
> > host part is all-ones or all-zeros and reject that?
>
> I'm inclined to think not, partially because that would mean that the
> results of broadcast() and network() could *NOT* be considered valid
> INET values.
True.
>
> The way I'm visualizing this, INET is a generalized type that will store
> any 4-octet address plus any netmask width from 1 to 32. This includes
> not only host addresses, but network specs and broadcast addresses.
> CIDR is a subset type that only accepts valid network specs (ie, no
> nonzero address bits to the right of the netmask). There is no subset
> type that corresponds to "valid host addresses only" --- if there were,
> it would be a subset of INET but would have no valid values in common
> with CIDR. We could make such a type but I dunno if it's worth the
> trouble.
I believe this is true. Now if we could get the output stuff so there
are BOTH ways of displaying the data (we seem to need both, from the
statements we get each time this has been brought up), such that you
can freely move between the 4-octet and short-octet (for lack of a
better term) version of a CIDR network spec.

Thanks for any consideration, and if this could make 7.1, I'd be most
appreciative...

Larry

>
> regards, tom lane
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 (voice) Internet: ler(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Sigh ... I was really hoping not to get drawn into fixing these issues
for 7.1, but ...

It seems like much of the problem is that there isn't any easy way to
choose between CIDR-style display format ('127.1/16') and INET-style
format ('127.1.0.0/16'). We need to bite the bullet and add conversion
functions, so that people can pick which they want.

Picking and choosing among the ideas discussed, here's my stab at a
complete proposal:

1. CIDR-type values will be displayed in "abbreviated" format, eg
"127.1/16". Since a CIDR value is no longer allowed to have any
nonzero bits to the right of the mask, no information is lost by
abbreviation. The /n will appear even when it is 32.

2. INET-type values will always be displayed with all octets, eg
"127.1.0.0/16". The /n part will be suppressed from display
if it is 32. INET will accept any octet pattern as an address
together with any netmask length from 1 to 32.

3. We will add explicit functions cidr(inet) and inet(cidr) to force
the data type to one or the other style, thus allowing selection
of either display style. Note that cidr(inet) will raise an error
if given something with nonzeroes to the right of the netmask.

4. The function host(inet) will now return inet not text. It will
take the address octets of the given value but force the netmask to 32
and the display type to INET. So for example host('127.1/16'::cidr)
will yield '127.1.0.0/32'::inet, which if displayed will appear
as just '127.1.0.0', per item 2.

5. The function broadcast(inet) will now return inet not text. It
will take the given address octets and force the bits to the right
of the netmask to 1. The display type will be set to inet. After
more thought about my last message, I am inclined to have it return
the same masklength as the input, so for example broadcast('127.1/16')
would yield '127.1.255.255/16'::inet. If you want the broadcast
address displayed without a netmask notation, you'd need to write
host(broadcast(foo)). Alternatively, we could say that broadcast()
always returns masklen 32, but I think this loses valuable
functionality.

6. The function network(inet) will now return cidr not text. The result
has the same masklen as the input, with bits to the right of the mask
zeroed to ensure it is a valid cidr value. The display type will be
set to cidr. For example, network('127.1.2.3/16') will yield
'127.1/16'::cidr. To get this result displayed in inet format, you'd
write inet(network(foo)) --- yielding '127.1.0.0/16'. If you want it
displayed with no netmask, write host(network(foo)) --- result
'127.1.0.0'.

7. The function netmask(inet) will now return inet not text. It will
return octets with 1s in the input's netmask, 0s to the right, and
output display type and masklen set to inet and 32. For example,
netmask('127.1/16') = '255.255.0.0/32'::inet which will display as
'255.255.0.0'. (I suppose a really anal definition would keep the
input masklen, forcing you to write host(netmask(foo)) to get a
display without "/n". But I don't see any value in that for
netmasks.)

8. Because we still consider inet and cidr to be binary-equivalent types,
all of these functions will be applied to either inet or cidr columns
without any type conversion. (In other words, cidr(inet) and
inet(cidr) will only be applied if *explicitly* invoked.) I am not
convinced whether this is a good thing. In this proposal, no system
function except display will care whether its input is inet or cidr,
so the lack of conversion doesn't matter. But in the long run it
might be better to remove the binary-equivalence. Then, for example,
host(cidr) would be implemented as host(inet(cidr)), costing an extra
function call per operation. Right now I don't think we need to pay
that price, but maybe someday we will.

Thoughts?

regards, tom lane

* Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> [001027 20:45]:
> Sigh ... I was really hoping not to get drawn into fixing these issues
> for 7.1, but ...
[SNIP]
Works WELL for me. THANK YOU, Tom.

LER

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 (voice) Internet: ler(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Please read below if the whole thing with inet/cidr doesn't make you puke
yet ;) The semi-longish proposal is at the bottom.

On Fri, 27 Oct 2000, Tom Lane wrote:

> Alex Pilosov <alex(at)pilosoft(dot)com> writes:
> > We should have as much error-checking as possible.
>
> How so? Without a netmask you have no way to know if it's a broadcast
> address or not. 10.0.0.255/32 might be a perfectly valid host address
> in, say, 10.0/16. But 10.0.0.255/24 is recognizably the broadcast
> address for 10.0.0/24 (and not for any other network...)
Right, that's what I'm trying to say: It shouldn't allow you to use
10.0.0.255/24 as a host address, but it should allow you to use
10.0.0.255/16

> > (ie. broadcast() function must return a value with /32 mask)
>
> I don't disagree with that part, but that's only because I see
> broadcast() as mainly a display convenience. If we had a larger and
> more thoroughly worked out set of inet/cidr operators, I'd be inclined
> to argue that broadcast('10.0.0.0/24') should yield 10.0.0.255/24 for
> computational convenience. Then we'd need to offer a separate function
> that would let you strip off the netmask for display purposes (actually
> host() would do for that...)

Actually, now that I think longer about the whole scheme in terms of
actual IP experience, here are my ideas:
a) inet is crock. I don't know anyone who would need to _care_ about a
netmask of a host, who wouldn't have a lookup table of networks/masks.
(Think /etc/hosts, and /etc/netmasks).

Storing a netmask of a network in a inet actually violates the relational
constraints: netmask is not a property of an IP address, its a property of
a network.

99% of people who would be storing IP addresses into postgres database
really do not know nor care what is a netmask on that IP. Only people who
would care are ones who store their _internal_ addresses (read: addresses
used on networks they manage). There is usually a very limited number of
such networks (<1000).

It makes no sense to have in database both 10.0.0.1/24 and 10.0.0.2/16.
None whatsoever.

This does NOT apply to CIDR datatype, as there are real applications (such
as storing routing tables) where you would care about netmask, but won't
care about a host part.

What I am suggesting is we do the following:
a) inet will NOT have a netmask

b) all the fancy comparison functions on inet should be deleted.
(leave only > >= = <= <)

c) the only things you can do on inet is to convert it to 4 octets (of
int1), to a int8, and to retrieve its network from a table of networks.

d) have a table, 'networks' (or any other name, maybe pg_networks?) which
would have one column 'network', with type cidr.
create table networks (network cidr not null primary key)

e) have a function network(inet) which would look up the address in a
table of networks using longest-prefix-match. I.E. something similar to:

select network from networks
where $1<<network
order by network_prefix(network)
desc limit 1;

I realise that this sounds a little bit strange after all the arguments
about inet, but if you think about it, this is the only sane way to deal
with these datatypes.

Right now, the datatypes we have look and sound pretty but are pretty much
useless in reality. Yes, it is nice to be able to store a netmask with
every IP address, it is useless in reality. (Yes, please, someone tell me
if you are using inet with netmasks and you actually like it).

I'd especially like to get input of Marc on this, as he's both a core team
member and has actual networking background...Oh yeah, if Marc can comment
on whether 10/8 or 10.0.0.0/8 is a proper way to represent a network, it'd
be great too :)

* Alex Pilosov <alex(at)pilosoft(dot)com> [001027 21:20]:
> Please read below if the whole thing with inet/cidr doesn't make you puke
> yet ;) The semi-longish proposal is at the bottom.
>
> On Fri, 27 Oct 2000, Tom Lane wrote:
[snip]
> Actually, now that I think longer about the whole scheme in terms of
> actual IP experience, here are my ideas:
> a) inet is crock. I don't know anyone who would need to _care_ about a
> netmask of a host, who wouldn't have a lookup table of networks/masks.
> (Think /etc/hosts, and /etc/netmasks).
>
> Storing a netmask of a network in a inet actually violates the relational
> constraints: netmask is not a property of an IP address, its a property of
> a network.
Not necessarily, especially for novices. Some people may want to
store the netmask with the IP of a host (think ifconfig being
AUTOGEN'd).

>
> 99% of people who would be storing IP addresses into postgres database
> really do not know nor care what is a netmask on that IP. Only people who
> would care are ones who store their _internal_ addresses (read: addresses
> used on networks they manage). There is usually a very limited number of
> such networks (<1000).
I disagree. I'm an ISP, and the network engineer for same. I have a
BOATLOAD of Netblocks from ARIN and providers in a BUNCH of sizes. I
need to subnet them out to customers and for internal use. I like
Tom's latest proposal. This one LOSES functionality for ME.
>
> It makes no sense to have in database both 10.0.0.1/24 and 10.0.0.2/16.
> None whatsoever.
Not necessarily, especially with RFC1918 addresses, and reuse within
different unconnected networks of the SAME enterprise.
>
> This does NOT apply to CIDR datatype, as there are real applications (such
> as storing routing tables) where you would care about netmask, but won't
> care about a host part.
>
> What I am suggesting is we do the following:
> a) inet will NOT have a netmask
Please DONT. See above.
>
> b) all the fancy comparison functions on inet should be deleted.
> (leave only > >= = <= <)
>
Maybe. I think they should stay, but I'm one lowly network engineer.
> c) the only things you can do on inet is to convert it to 4 octets (of
> int1), to a int8, and to retrieve its network from a table of networks.
>
> d) have a table, 'networks' (or any other name, maybe pg_networks?) which
> would have one column 'network', with type cidr.
> create table networks (network cidr not null primary key)
Why?
>
> e) have a function network(inet) which would look up the address in a
> table of networks using longest-prefix-match. I.E. something similar to:
No need. Let the user do it themselves. Similar to what we did for
macaddr's back in the summer.
>
> select network from networks
> where $1<<network
> order by network_prefix(network)
> desc limit 1;
>
>
> I realise that this sounds a little bit strange after all the arguments
> about inet, but if you think about it, this is the only sane way to deal
> with these datatypes.
>
> Right now, the datatypes we have look and sound pretty but are pretty much
> useless in reality. Yes, it is nice to be able to store a netmask with
> every IP address, it is useless in reality. (Yes, please, someone tell me
> if you are using inet with netmasks and you actually like it).
>
See above.
>
> I'd especially like to get input of Marc on this, as he's both a core team
> member and has actual networking background...Oh yeah, if Marc can comment
> on whether 10/8 or 10.0.0.0/8 is a proper way to represent a network, it'd
> be great too :)
>
>

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 (voice) Internet: ler(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

On Fri, 27 Oct 2000, Larry Rosenman wrote:

> Not necessarily, especially for novices. Some people may want to
> store the netmask with the IP of a host (think ifconfig being
> AUTOGEN'd).
For a single host? Or for a network of hosts? But yes, I see your point if
a single host has x interfaces, and you are autogenerating ifconfig, with
my proposal, you'd need to insert each network into networks table.

> > 99% of people who would be storing IP addresses into postgres database
> > really do not know nor care what is a netmask on that IP. Only people who
> > would care are ones who store their _internal_ addresses (read: addresses
> > used on networks they manage). There is usually a very limited number of
> > such networks (<1000).
> I disagree. I'm an ISP, and the network engineer for same. I have a
> BOATLOAD of Netblocks from ARIN and providers in a BUNCH of sizes. I
> need to subnet them out to customers and for internal use. I like
> Tom's latest proposal. This one LOSES functionality for ME.
Explain how does it lose functionality?

> > It makes no sense to have in database both 10.0.0.1/24 and 10.0.0.2/16.
> > None whatsoever.
> Not necessarily, especially with RFC1918 addresses, and reuse within
> different unconnected networks of the SAME enterprise.
Makes no sense to have them in one table, anyway, I stand corrected.
For people in situation you describe, you can have a second table of
networks, and second function to look up networks in that table.

> > This does NOT apply to CIDR datatype, as there are real applications (such
> > as storing routing tables) where you would care about netmask, but won't
> > care about a host part.
> >
> > What I am suggesting is we do the following:
> > a) inet will NOT have a netmask
> Please DONT. See above.
> >
> > b) all the fancy comparison functions on inet should be deleted.
> > (leave only > >= = <= <)
> >
> Maybe. I think they should stay, but I'm one lowly network engineer.
> > c) the only things you can do on inet is to convert it to 4 octets (of
> > int1), to a int8, and to retrieve its network from a table of networks.
> >
> > d) have a table, 'networks' (or any other name, maybe pg_networks?) which
> > would have one column 'network', with type cidr.
> > create table networks (network cidr not null primary key)
> Why?
Because netmask is a property of a network, not of an IP address.

> > e) have a function network(inet) which would look up the address in a
> > table of networks using longest-prefix-match. I.E. something similar to:
> No need. Let the user do it themselves. Similar to what we did for
> macaddr's back in the summer.
Yeah, it can be user-defined (or a contrib), no question about it, and for
people who have more than one table of networks, it will _have_ to be
user-defined.

Actually, that's probably what I'll end up doing on my own.

>>>> e) have a function network(inet) which would look up the address in a
>>>> table of networks using longest-prefix-match. I.E. something similar to:

>> No need. Let the user do it themselves. Similar to what we did for
>> macaddr's back in the summer.

> Yeah, it can be user-defined (or a contrib), no question about it, and for
> people who have more than one table of networks, it will _have_ to be
> user-defined.

It seems clear to me that this mapping is best left to the user.

A more interesting question is whether the system needs to provide any
assisting functions that aren't there now. The lookup function you guys
are postulating seems like it would be (in the simple cases)
create function my_network(inet) returns cidr as
'select network from my_networks where ???'
Maybe it's too late at night, but I'm having a hard time visualizing
what the ??? condition is and whether any additional system-level
functions are needed to make it simple/efficient.

regards, tom lane

* Alex Pilosov <alex(at)pilosoft(dot)com> [001027 21:36]:
> On Fri, 27 Oct 2000, Larry Rosenman wrote:
>
> > Not necessarily, especially for novices. Some people may want to
> > store the netmask with the IP of a host (think ifconfig being
> > AUTOGEN'd).
> For a single host? Or for a network of hosts? But yes, I see your point if
> a single host has x interfaces, and you are autogenerating ifconfig, with
> my proposal, you'd need to insert each network into networks table.
Or a table of Routers, listed by IP's. I want to be able to
efficently store the interface name, IP, Mask. With your proposal, I
can't store it as one row in one table. With Tom's proposal, I can.

>
> > > 99% of people who would be storing IP addresses into postgres database
> > > really do not know nor care what is a netmask on that IP. Only people who
> > > would care are ones who store their _internal_ addresses (read: addresses
> > > used on networks they manage). There is usually a very limited number of
> > > such networks (<1000).
> > I disagree. I'm an ISP, and the network engineer for same. I have a
> > BOATLOAD of Netblocks from ARIN and providers in a BUNCH of sizes. I
> > need to subnet them out to customers and for internal use. I like
> > Tom's latest proposal. This one LOSES functionality for ME.
> Explain how does it lose functionality?
I may need to list an interface in their net, with their netmask, but
not have it in my networks table. I don't think that the system
should supply a networks table, per se. I have much more than 1000's
networks in my shop. Please don't FORCE me to your model. I like
Tom's proposal, especially from the "least surprise" aspects.
>
> > > It makes no sense to have in database both 10.0.0.1/24 and 10.0.0.2/16.
> > > None whatsoever.
> > Not necessarily, especially with RFC1918 addresses, and reuse within
> > different unconnected networks of the SAME enterprise.
> Makes no sense to have them in one table, anyway, I stand corrected.
> For people in situation you describe, you can have a second table of
> networks, and second function to look up networks in that table.
See above. Please don't force me to your paradigm.
>
> > > This does NOT apply to CIDR datatype, as there are real applications (such
> > > as storing routing tables) where you would care about netmask, but won't
> > > care about a host part.
> > >
> > > What I am suggesting is we do the following:
> > > a) inet will NOT have a netmask
> > Please DONT. See above.
> > >
> > > b) all the fancy comparison functions on inet should be deleted.
> > > (leave only > >= = <= <)
> > >
> > Maybe. I think they should stay, but I'm one lowly network engineer.
> > > c) the only things you can do on inet is to convert it to 4 octets (of
> > > int1), to a int8, and to retrieve its network from a table of networks.
> > >
> > > d) have a table, 'networks' (or any other name, maybe pg_networks?) which
> > > would have one column 'network', with type cidr.
> > > create table networks (network cidr not null primary key)
> > Why?
> Because netmask is a property of a network, not of an IP address.
>
> > > e) have a function network(inet) which would look up the address in a
> > > table of networks using longest-prefix-match. I.E. something similar to:
> > No need. Let the user do it themselves. Similar to what we did for
> > macaddr's back in the summer.
> Yeah, it can be user-defined (or a contrib), no question about it, and for
> people who have more than one table of networks, it will _have_ to be
> user-defined.
>
> Actually, that's probably what I'll end up doing on my own.
>

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 (voice) Internet: ler(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

* Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> [001027 21:53]:
> >>>> e) have a function network(inet) which would look up the address in a
> >>>> table of networks using longest-prefix-match. I.E. something similar to:
>
> >> No need. Let the user do it themselves. Similar to what we did for
> >> macaddr's back in the summer.
>
> > Yeah, it can be user-defined (or a contrib), no question about it, and for
> > people who have more than one table of networks, it will _have_ to be
> > user-defined.
>
> It seems clear to me that this mapping is best left to the user.
>
> A more interesting question is whether the system needs to provide any
> assisting functions that aren't there now. The lookup function you guys
> are postulating seems like it would be (in the simple cases)
> create function my_network(inet) returns cidr as
> 'select network from my_networks where ???'
> Maybe it's too late at night, but I'm having a hard time visualizing
> what the ??? condition is and whether any additional system-level
> functions are needed to make it simple/efficient.
I don't think we need this ASAP for 7.1. Let's get the basic stuff
working from a "least surprise" standpoint, and see what the user base
comes up with. I really think your proposal from earlier tonite is
the way to go, at least from my perspective.

Thanks again.

LER

>
> regards, tom lane
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 (voice) Internet: ler(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

On Fri, 27 Oct 2000, Tom Lane wrote:

> A more interesting question is whether the system needs to provide any
> assisting functions that aren't there now. The lookup function you guys
> are postulating seems like it would be (in the simple cases)
> create function my_network(inet) returns cidr as
> 'select network from my_networks where ???'
as in my mail:
select network from my_network where network>>$1 order by
network_prefix(network) desc limit 1;

(i.e. if many networks cover the ip address, pick the one with longest
prefix). The only hard question here, how to properly index this table.
This sounds like a perfect application of user-defined index method.
I need to look up documentation on how they work...

However, this probably won't pose a major problem in production: the
networks table will be relatively small.

> Maybe it's too late at night, but I'm having a hard time visualizing
> what the ??? condition is and whether any additional system-level
> functions are needed to make it simple/efficient.

Actually, you can scratch my proposal. I realise it could be inconvenient
for some people.

I'll be probably putting all my hosts as inet::xxx/32, have the above
lookup function to get real network, and do operations on that.

one more small request:

int8_inet(inet) and inet_int8(int8): functions to convert an inet to an
int8 and back. (not an int4, since postgres int4s are signed)

This allows me to do some additional manipulations on values. (ie. given a
host, determine its default gateway, for us, it is always first host on
that network, this could be implemented as inet_int8(int8_inet(network(x))+1),
or splitting a cidr into two halves,

-alex

On Fri, 27 Oct 2000, Tom Lane wrote:

> BTW, does it strike anyone else as peculiar that the host(),
> broadcast(), network(), and netmask() functions yield results
> of type text, rather than type inet? Seems like it'd be considerably
> more useful if they returned values of type inet with masklen = 32
> (except for network(), which would keep the original masklen while
> coercing bits to its right to 0).
Yep, absolutely.

> Given the current proposal that inet_out should always display all 4
> octets, and the existing fact that inet_out suppresses display of
> a /32 netmask, the textual display of SELECT host(...) etc would
> remain the same as it is now. But AFAICS you could do more with
> an inet-type result value, like say compare it to other inet or cidr
> values ...
> Comments? Why was it done this way, anyway?

Tom Lane writes:

> Hm. I don't see any really good reason why host() rejects CIDR input
> in the first place. What's wrong with producing the host address
> that corresponds to extending the CIDR network address with zeroes?

Because it's semantically wrong. It's just as wrong as converting DATE to
TIMESTAMP by setting the time to zero. -- And we actually do this...

--
Peter Eisentraut peter_e(at)gmx(dot)net http://yi.org/peter-e/

Tom Lane writes:

> 3. We will add explicit functions cidr(inet) and inet(cidr) to force
> the data type to one or the other style, thus allowing selection
> of either display style. Note that cidr(inet) will raise an error
> if given something with nonzeroes to the right of the netmask.

Not sure if using functions that look like a cast to control output format
is a good idea. The conversion inet => cidr seems most naturally left
with the network() function. The other conversion is not well-defined.
(You could define it in several reasonable ways, but that still doesn't
make it "well".) ISTM that you'd really need some function build_inet(a
cidr, b inet) returns inet, where b does not have a network and can
somehow be fitted into network a.

Actually, let's sign up Karel to write to_char(inet) and to_char(cidr).

> But in the long run it might be better to remove the
> binary-equivalence.

I say kill it ASAP. I don't think there was ever a good reason for this
besides implementation convenience; and the troubles it has caused are
without end.

--
Peter Eisentraut peter_e(at)gmx(dot)net http://yi.org/peter-e/

On Fri, 27 Oct 2000, Alex Pilosov wrote:

> > BTW, does it strike anyone else as peculiar that the host(),
> > broadcast(), network(), and netmask() functions yield results
> > of type text, rather than type inet? Seems like it'd be considerably
> > more useful if they returned values of type inet with masklen = 32
> > (except for network(), which would keep the original masklen while
> > coercing bits to its right to 0).
> I absolutely agree, except for network(), which should return cidr.
> (after all, this is the network).
>
> As I mentioned in another email, should inet datatype really care whether
> host part is all-ones or all-zeros and reject that? It would make sense to
> me (10.0.0.0/8::inet is not a valid address, but 10.0.0.0/8::cidr is), but
> it would break some people's scripts...

How about letting inet just be a "simple" storage type for four octets
seperated by periods. (Along the line of (0-255) . (0-255) . (0-255) . (0-255) )

The only conversion function there would be that operates on inet is
cidr(inet) - which returns the contents of the tuple, with a /32.
Alternately, for those who think we absolutely need something more
complicated (I don't think it's neeccesary), something along the lines of:

cidr('10.20.23.252' => '10.20.23.252/32'
cidr('10.20.23.0') => '10.20.23/24' (or '10.20.23.0/24')
cidr('10.20.0.0') => '10.20/16' (or '10.20.0.0/16')
cidr('10.0.0.0') => '10/8' (or '10.0.0.0/8')

Although one should put a comment/warning in the documentation that cidr()
assumes subnets to be on an octet boundary. (Which is the norm with inet
stuff - cidr was created specifically to get away from
subnet-on-octet-boundaries.)

Slight digression - on the discussion whether it's 127/8, or 127.0.0.0/8 -
both are accepted in most applications nowadays. But back to the issue
at hand...

Then let the cidr data type be for anything more advanced - like for
storing subnets on non-octet boundaries etc, and have host(),
broadcast(), network(), and netmask() functions. host() would return the
IP with a /32 mask - and inet(cidr) would just return the IP, without a
mask.

> I'm talking here from a perspective of a network provider with P
> knowledge...I'm sure Marc can chime in here...

So am I.

--
Dominic J. Eidson
"Baruk Khazad! Khazad ai-menu!" - Gimli
-------------------------------------------------------------------------------
http://www.the-infinite.org/ http://www.the-infinite.org/~dominic/

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Tom Lane writes:
>> 3. We will add explicit functions cidr(inet) and inet(cidr) to force
>> the data type to one or the other style, thus allowing selection
>> of either display style. Note that cidr(inet) will raise an error
>> if given something with nonzeroes to the right of the netmask.

> Not sure if using functions that look like a cast to control output format
> is a good idea. The conversion inet => cidr seems most naturally left
> with the network() function. The other conversion is not well-defined.
> (You could define it in several reasonable ways, but that still doesn't
> make it "well".)

Good point: cidr() is exactly the same as network() under my proposal,
so we don't need a separate function for that.

While inet() and host() as proposed may be morally impure, they're no
worse than date->timestamp and similar conversions that we have in
abundance. I do not agree that they are ill-defined --- the spec
I wrote seems perfectly clear.

Would you be happier if inet() and host() were defined to produce
textual representations --- respectively "w.x.y.z/n" and "w.x.y.z"
rather than actual INET values? That seems like it'd still solve
the demand for being able to extract these specific representations,
without opening up the quagmire of whether these values are legitimate
INET values.

regards, tom lane

* Larry Rosenman <ler(at)lerctr(dot)org> [001028 02:23]:
> I don't think we need this ASAP for 7.1. Let's get the basic stuff
> working from a "least surprise" standpoint, and see what the user base
> comes up with. I really think your proposal from earlier tonite is
> the way to go, at least from my perspective.
>
> Thanks again.
What was the final outcome? Will Tom's proposal make 7.1?

(Do I need to learn how to code backend stuff?)

LER
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 (voice) Internet: ler(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Larry Rosenman <ler(at)lerctr(dot)org> writes:
> What was the final outcome?

I don't think we'd quite agreed what to do. The proposed code changes
are not large, we just need a consensus on what the behavior ought to
be.

Since a couple of people objected to the idea of using casts to control
the output format, here is a strawman Plan C for discussion. This
differs from my last proposal in that the inet() and cidr() pseudo
cast functions are gone, and there are two functions returning text
values that can be used if you don't like the default display formats.

1. CIDR-type values will be displayed in "abbreviated" format, eg
"127.1/16". Since a CIDR value is no longer allowed to have any
nonzero bits to the right of the mask, no information is lost by
abbreviation. The /n will appear even when it is 32.

2. INET-type values will always be displayed with all octets, eg
"127.1.0.0/16". The /n part will be suppressed from display
if it is 32. INET will accept any octet pattern as an address
together with any netmask length from 1 to 32.

3. The function host(inet) will return a text representation of
just the IP part of an INET or CIDR value, eg, "127.1.0.0".
All four octets will always appear, the netmask will never appear.
(This is the same as its current behavior, I think.)

4. A new function text(inet) will return a text representation of
both the IP and netmask parts of an INET or CIDR value, eg,
"127.1.0.0/16". Unlike the default display conversions, all four
octets and the netmask length will always appear in the result.
Note that the system will consider this function to be a typecast,
so the same result can be gotten with inetval::text or
CAST(inetval AS text).

[ the rest is the same as in my last proposal: ]

5. The function broadcast(inet) will now return inet not text. It
will take the given address octets and force the bits to the right
of the netmask to 1. The display type will be set to inet. I am
inclined to have it return the same masklength as the input, so for
example broadcast('127.1/16') would yield '127.1.255.255/16'::inet.
If you want the broadcast address displayed without a netmask
notation, you'd need to write host(broadcast(foo)). Alternatively,
we could say that broadcast() always returns masklen 32, but I think
this loses valuable functionality.

6. The function network(inet) will now return cidr not text. The result
has the same masklen as the input, with bits to the right of the mask
zeroed to ensure it is a valid cidr value. The display type will be
set to cidr. For example, network('127.1.2.3/16') will yield
'127.1/16'::cidr. To get this result displayed in a different
format, write host(network(foo)) or text(network(foo)).

7. The function netmask(inet) will now return inet not text. It will
return octets with 1s in the input's netmask, 0s to the right, and
output display type and masklen set to inet and 32. For example,
netmask('127.1/16') = '255.255.0.0/32'::inet which will display as
'255.255.0.0'. (I suppose a really anal definition would keep the
input masklen, forcing you to write host(netmask(foo)) to get a
display without "/n". But I don't see any value in that for
netmasks.)

8. Because we still consider inet and cidr to be binary-equivalent types,
all of these functions can be applied to either inet or cidr columns.

Comments?

regards, tom lane

Works for me.....

LER

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 (voice) Internet: ler(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Agreed with all of it, but how about incorporating conversion from inet
to int8? (first octet*256*256*256+second octet*256*256+third
octet*256+fourth octet).

This will allow to do a lot of magic with addresses using plain math.

Also, I'd still like netmask_length, length of netmask in bits.

-alex

On Fri, 3 Nov 2000, Tom Lane wrote:

> 5. The function broadcast(inet) will now return inet not text. It
> will take the given address octets and force the bits to the right
> of the netmask to 1. The display type will be set to inet. I am
> inclined to have it return the same masklength as the input, so for
> example broadcast('127.1/16') would yield '127.1.255.255/16'::inet.
> If you want the broadcast address displayed without a netmask
> notation, you'd need to write host(broadcast(foo)). Alternatively,
> we could say that broadcast() always returns masklen 32, but I think
> this loses valuable functionality.
>
> 6. The function network(inet) will now return cidr not text. The result
> has the same masklen as the input, with bits to the right of the mask
> zeroed to ensure it is a valid cidr value. The display type will be
> set to cidr. For example, network('127.1.2.3/16') will yield
> '127.1/16'::cidr. To get this result displayed in a different
> format, write host(network(foo)) or text(network(foo)).
>
> 7. The function netmask(inet) will now return inet not text. It will
> return octets with 1s in the input's netmask, 0s to the right, and
> output display type and masklen set to inet and 32. For example,
> netmask('127.1/16') = '255.255.0.0/32'::inet which will display as
> '255.255.0.0'. (I suppose a really anal definition would keep the
> input masklen, forcing you to write host(netmask(foo)) to get a
> display without "/n". But I don't see any value in that for
> netmasks.)
>
> 8. Because we still consider inet and cidr to be binary-equivalent types,
> all of these functions can be applied to either inet or cidr columns.
>
> Comments?
>
> regards, tom lane
>
>

* Alex Pilosov <alex(at)pilosoft(dot)com> [001103 20:47]:
> Agreed with all of it, but how about incorporating conversion from inet
> to int8? (first octet*256*256*256+second octet*256*256+third
> octet*256+fourth octet).
>
> This will allow to do a lot of magic with addresses using plain math.
>
> Also, I'd still like netmask_length, length of netmask in bits.
masklen(inet) is there:
int4 | masklen | inet

from a \df.

Can we also get it to work on cidr (or allow cast from inet to cidr).

>
> -alex
>
> On Fri, 3 Nov 2000, Tom Lane wrote:
>
> > 5. The function broadcast(inet) will now return inet not text. It
> > will take the given address octets and force the bits to the right
> > of the netmask to 1. The display type will be set to inet. I am
> > inclined to have it return the same masklength as the input, so for
> > example broadcast('127.1/16') would yield '127.1.255.255/16'::inet.
> > If you want the broadcast address displayed without a netmask
> > notation, you'd need to write host(broadcast(foo)). Alternatively,
> > we could say that broadcast() always returns masklen 32, but I think
> > this loses valuable functionality.
> >
> > 6. The function network(inet) will now return cidr not text. The result
> > has the same masklen as the input, with bits to the right of the mask
> > zeroed to ensure it is a valid cidr value. The display type will be
> > set to cidr. For example, network('127.1.2.3/16') will yield
> > '127.1/16'::cidr. To get this result displayed in a different
> > format, write host(network(foo)) or text(network(foo)).
> >
> > 7. The function netmask(inet) will now return inet not text. It will
> > return octets with 1s in the input's netmask, 0s to the right, and
> > output display type and masklen set to inet and 32. For example,
> > netmask('127.1/16') = '255.255.0.0/32'::inet which will display as
> > '255.255.0.0'. (I suppose a really anal definition would keep the
> > input masklen, forcing you to write host(netmask(foo)) to get a
> > display without "/n". But I don't see any value in that for
> > netmasks.)
> >
> > 8. Because we still consider inet and cidr to be binary-equivalent types,
> > all of these functions can be applied to either inet or cidr columns.
> >
> > Comments?
> >
> > regards, tom lane
> >
> >

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 (voice) Internet: ler(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> A separate function for formatting output seems necessary, but if we don't
> reach an agreement though, it ought to work to cast CIDR to INET to get
> all four octets, no?

Uh, weren't you one of the people objecting to relying on cidr-to-inet
casts to control formatting?

> I think the typecast-to-text representation of CIDR should be visually the
> same as the normal representation.

Well, we need *some* way to extract a representation like "w.x.y.z/n".
If you don't like text() as the name of that formatting function,
suggest another name...

regards, tom lane

Tom Lane writes:

> 3. The function host(inet) will return a text representation of
> just the IP part of an INET or CIDR value, eg, "127.1.0.0".
> All four octets will always appear, the netmask will never appear.
> (This is the same as its current behavior, I think.)

I think there was definite merit in the host() function returning inet, as
you originally proposed (if only for consistency with the proposed changes
to network() and broadcast()).

A separate function for formatting output seems necessary, but if we don't
reach an agreement though, it ought to work to cast CIDR to INET to get
all four octets, no?

> 4. A new function text(inet) will return a text representation of
> both the IP and netmask parts of an INET or CIDR value, eg,
> "127.1.0.0/16". Unlike the default display conversions, all four
> octets and the netmask length will always appear in the result.
> Note that the system will consider this function to be a typecast,
> so the same result can be gotten with inetval::text or
> CAST(inetval AS text).

I think the typecast-to-text representation of CIDR should be visually the
same as the normal representation.

--
Peter Eisentraut peter_e(at)gmx(dot)net http://yi.org/peter-e/

Tom Lane writes:

> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> > A separate function for formatting output seems necessary, but if we don't
> > reach an agreement though, it ought to work to cast CIDR to INET to get
> > all four octets, no?
>
> Uh, weren't you one of the people objecting to relying on cidr-to-inet
> casts to control formatting?

I didn't like the use of the to-text casts to control formatting, but if
an existing cast would "just handle it", then why not?

> > I think the typecast-to-text representation of CIDR should be visually the
> > same as the normal representation.
>
> Well, we need *some* way to extract a representation like "w.x.y.z/n".
> If you don't like text() as the name of that formatting function,
> suggest another name...

all_octets(cidr)::text maybe?

--
Peter Eisentraut peter_e(at)gmx(dot)net http://yi.org/peter-e/

* Peter Eisentraut <peter_e(at)gmx(dot)net> [001105 07:08]:
> Tom Lane writes:
>
> > Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> > > A separate function for formatting output seems necessary, but
> > > if we don't reach an agreement though, it ought to work to cast
> > > CIDR to INET to get all four octets, no?
> >
> > Uh, weren't you one of the people objecting to relying on
> > cidr-to-inet casts to control formatting?
>
> I didn't like the use of the to-text casts to control formatting,
> but if an existing cast would "just handle it", then why not?

>
> > > I think the typecast-to-text representation of CIDR should be
> > > visually the same as the normal representation.
> >
> > Well, we need *some* way to extract a representation like
> > "w.x.y.z/n". If you don't like text() as the name of that
> > formatting function, suggest another name...
>
> all_octets(cidr)::text maybe?
Personally, I just want a way, guaranteed to work, to get all 4 octets
printed out for both CIDR and INET types. If I need to cast to INET,
that's fine. We also need to make sure that we can print all the
pieces out as well (masklen, broadcast, netmask, network).

I really would like to see this resolved for 7.1, as I have a number
of apps that need to interface with NON-techies, and we need to print
out all 4 octets, as well as netmasks, etc. PostgreSQL is the perfect
DB for the backend BECAUSE of the inet/cidr types. Yes, I could write
convoluted PHP code to print out the stuff, but why should I when the
DB has all the information in a nice compact form, and a SELECT
statement could handle it?

I do understand the philosophical problems, but we really are very
close. Can we promise that we'll get this ironed out for 7.1?

Thanks.

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 (voice) Internet: ler(at)lerctr(dot)org US Mail: 1905
Steamboat Springs Drive, Garland, TX 75044-6749

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>> Well, we need *some* way to extract a representation like "w.x.y.z/n".
>> If you don't like text() as the name of that formatting function,
>> suggest another name...

> all_octets(cidr)::text maybe?

No, because that doesn't accurately describe what it does for inet
items --- those'd be shown with all octets anyway. For inet, the
critical thing this function will do is force the netmask to be shown
even if it's /32.

Given that we are using host() for the function that shows just the
IP address part of an inet/cidr value, how about hostandmask() for
the function that always shows everything?

I still prefer text() though.

regards, tom lane

* Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> [001109 10:30]:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> >> Well, we need *some* way to extract a representation like "w.x.y.z/n".
> >> If you don't like text() as the name of that formatting function,
> >> suggest another name...
>
> > all_octets(cidr)::text maybe?
>
> No, because that doesn't accurately describe what it does for inet
> items --- those'd be shown with all octets anyway. For inet, the
> critical thing this function will do is force the netmask to be shown
> even if it's /32.
>
> Given that we are using host() for the function that shows just the
> IP address part of an inet/cidr value, how about hostandmask() for
> the function that always shows everything?
>
> I still prefer text() though.
What is the *PHILOSOPHICAL* objection to text() in this case?

It's a TEXT output?

LER
>
> regards, tom lane
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 (voice) Internet: ler(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749