Re: Why so few built-in range types?

One thing that bothered me while looking at the range types patch is
that it seemed you'd been mighty conservative about creating built-in
range types. In particular, I don't understand why there's not a
standard float8range type; that seems like a pretty common case.
I'd have also expected to see a standard textrange type. What was
the rationale for leaving these out?

regards, tom lane

On Tue, 2011-11-29 at 12:01 -0500, Tom Lane wrote:
> One thing that bothered me while looking at the range types patch is
> that it seemed you'd been mighty conservative about creating built-in
> range types.

During development, I didn't want to juggle the OIDs for too many range
types. That was really the only reason.

> In particular, I don't understand why there's not a
> standard float8range type; that seems like a pretty common case.
> I'd have also expected to see a standard textrange type. What was
> the rationale for leaving these out?

A built-in textrange type would have to have collation "C", right? Do
you think that would be useful to enough people?

One that I'd like to see is an IP address type, but that's complicated
because inet and cidr support netmasks.

Regards,
Jeff Davis

Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> On Tue, 2011-11-29 at 12:01 -0500, Tom Lane wrote:
>> In particular, I don't understand why there's not a
>> standard float8range type; that seems like a pretty common case.
>> I'd have also expected to see a standard textrange type. What was
>> the rationale for leaving these out?

> A built-in textrange type would have to have collation "C", right? Do
> you think that would be useful to enough people?

No, its collation could be set to "default", which would match the
database's LC_COLLATE setting. Probably the more interesting
implementation problem is to come up with a subtype_diff function ...

> One that I'd like to see is an IP address type, but that's complicated
> because inet and cidr support netmasks.

Yeah, it's not clear what if anything to do with the netmask.

regards, tom lane

On Wed, Nov 30, 2011 at 1:08 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> One that I'd like to see is an IP address type, but that's complicated
> because inet and cidr support netmasks.

A CIDR address defines a range all by itself, without packing any
other type on top. It just needs GIST support, and an indexable
operator for "contains or is contained by"; then, you can define an
exclusion constraint over a CIDR column to enforce a
no-duplicate-or-overlapping-IP-ranges rule. I started working on that
at one point, but I didn't have as much enthusiasm as the task needed
so I gave up before accomplishing anything particularly useful.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> A CIDR address defines a range all by itself, without packing any
> other type on top. It just needs GIST support, and an indexable
> operator for "contains or is contained by"; then, you can define an
> exclusion constraint over a CIDR column to enforce a
> no-duplicate-or-overlapping-IP-ranges rule. I started working on that
> at one point, but I didn't have as much enthusiasm as the task needed
> so I gave up before accomplishing anything particularly useful.

Erm, isn't there a contrib type that already does all that for you..?
ip4r or whatever? Just saying, if you're looking for that capability..

I do think it'd be kind of interesting to offer both that and a
straight-up 'ip_address' type w/ range types..

Thanks,

Stephen

On Wed, Nov 30, 2011 at 3:58 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> Erm, isn't there a contrib type that already does all that for you..?
> ip4r or whatever?  Just saying, if you're looking for that capability..

Oh, huh, good to know. Still, I'm not sure why you need to load a
separate type to get this... there's no reason why the built-in CIDR
type couldn't support it.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> On Wed, Nov 30, 2011 at 3:58 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > Erm, isn't there a contrib type that already does all that for you..?
> > ip4r or whatever?  Just saying, if you're looking for that capability..
>
> Oh, huh, good to know. Still, I'm not sure why you need to load a
> separate type to get this... there's no reason why the built-in CIDR
> type couldn't support it.

The semantics of that type aren't what people actually want and there's
been push-back about changing it due to backwards compatibility, etc.
That's my recollection of the situation, anyway. I'm sure there's all
kinds of fun talk in the archives about it.

Thanks,

Stephen

On ons, 2011-11-30 at 17:56 -0500, Robert Haas wrote:
> On Wed, Nov 30, 2011 at 3:58 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > Erm, isn't there a contrib type that already does all that for you..?
> > ip4r or whatever? Just saying, if you're looking for that capability..
>
> Oh, huh, good to know. Still, I'm not sure why you need to load a
> separate type to get this... there's no reason why the built-in CIDR
> type couldn't support it.

A couple of reasons:

- ip4 is fixed-length, so it's much faster. (Obviously, this is living
on borrowed time. Who knows.)

- Conversely, it might be considered a feature that ip4 only stores IPv4
addresses.

- ip4 really only stores a single address, not a netmask, not sometimes
a netmask, or sometimes a range, or sometimes a network and an address,
or whatever. That really seems like the most common use case, and no
matter what you do with the other types, some stupid netmask will appear
in your output when you least expect it.

- Integrates with ip4r, which has GiST support.

- Some old-school internet gurus worked out why inet and cidr have to
behave the way they do, which no one else understands, and no one dares
to discuss, whereas ip4/ip4r are simple and appear to be built for
practical use.

Really, it's all about worse is better.

* Peter Eisentraut (peter_e(at)gmx(dot)net) wrote:
> - ip4 really only stores a single address, not a netmask, not sometimes
> a netmask, or sometimes a range, or sometimes a network and an address,
> or whatever. That really seems like the most common use case, and no
> matter what you do with the other types, some stupid netmask will appear
> in your output when you least expect it.

This is definitely one of the funny complications with our built-in
types. I don't feel that's a feature either. Nor do I consider it
'worse' that we have a type that actually makes sense. :) Regardless of
who developed it, it's simply trying to do too much in one type. I'm
also not convinced that our built-in types even operate in a completely
sensible way when you consider all the interactions you could have
between the different 'types' of that 'type', but I'll admit that I
haven't got examples or illustrations of that- something better exists
and is what I use and encourage others to use.

In some ways, I would say this is akin to our built-in types vs.
PostGIS. My argument isn't about features or capabilities in either
case (though those are valuable too), it's about what's 'right' and
makes sense, to me anyway.

Thanks,

Stephen

On Fri, Dec 2, 2011 at 3:42 AM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> - ip4 is fixed-length, so it's much faster.  (Obviously, this is living
> on borrowed time.  Who knows.)

Fair point.

> - Conversely, it might be considered a feature that ip4 only stores IPv4
> addresses.

True, although this can also be enforced by application logic or a
check constraint quite easily. Of course that is likely not as fast,
going to point #1.

> - ip4 really only stores a single address, not a netmask, not sometimes
> a netmask, or sometimes a range, or sometimes a network and an address,
> or whatever.  That really seems like the most common use case, and no
> matter what you do with the other types, some stupid netmask will appear
> in your output when you least expect it.

Yes, this is mildly annoying; but at worst it is a defect of inet, not
cidr, which does exactly what I'd expect a cidr type to do.

> - Integrates with ip4r, which has GiST support.

Well, OK, so I want GiST support for cidr. That's where this all started.

> - Some old-school internet gurus worked out why inet and cidr have to
> behave the way they do, which no one else understands, and no one dares
> to discuss, whereas ip4/ip4r are simple and appear to be built for
> practical use.
>
> Really, it's all about worse is better.

Heh, OK, well, that's above my pay grade.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Hi,

I wanted to craft an answer here and Peter nailed it before I could. I
use ip4r in a bunch of different projects and environments, it's doing a
perfect job, it's simple to use and damn efficient.

The ipv6 support is on the way, parts of it are already be in the CVS at
http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/ip4r/ip4r/. It's missing
tests mainly IIRC from a chat with its author, a well known PostgreSQL
contributor, Andrew Gierth.

Really, I wouldn't even consider adding gist support for inet and cidr.
Their real future has been sketched by Tom at last developer meeting, at
least what I remember hom saying is that they should eventually get
shipped as extensions now that it's easy to do so, and removed out of
core with some more types in the same bucket.

I could be misremembering which types Tom was talking about, though.

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> - ip4 is fixed-length, so it's much faster. (Obviously, this is living
> on borrowed time. Who knows.)
>
> - Conversely, it might be considered a feature that ip4 only stores IPv4
> addresses.
>
> - ip4 really only stores a single address, not a netmask, not sometimes
> a netmask, or sometimes a range, or sometimes a network and an address,
> or whatever. That really seems like the most common use case, and no
> matter what you do with the other types, some stupid netmask will appear
> in your output when you least expect it.
>
> - Integrates with ip4r, which has GiST support.
>
> - Some old-school internet gurus worked out why inet and cidr have to
> behave the way they do, which no one else understands, and no one dares
> to discuss, whereas ip4/ip4r are simple and appear to be built for
> practical use.
>
> Really, it's all about worse is better.

--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support