Обсуждение: 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@j-davis.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@j-davis.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@gmail.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@snowman.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@gmail.com) wrote:
> On Wed, Nov 30, 2011 at 3:58 PM, Stephen Frost <sfrost@snowman.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
----- Цитат от Stephen Frost (sfrost@snowman.net), на 01.12.2011 в 15:56 ----- <br /><br />> * Robert Haas (robertmhaas@gmail.com)wrote: <br />>> On Wed, Nov 30, 2011 at 3:58 PM, Stephen Frost wrote: <br />>> > Erm,isn't there a contrib type that already does all that for you..? <br />>> > ip4r or whatever? Just saying,if you're looking for that capability.. <br />>> <br />>> Oh, huh, good to know. Still, I'm not sure whyyou need to load a <br />>> separate type to get this... there's no reason why the built-in CIDR <br />>>type couldn't support it. <br />> <br />> The semantics of that type aren't what people actually want andthere's <br />> been push-back about changing it due to backwards compatibility, etc. <br />> That's my recollectionof the situation, anyway. I'm sure there's all <br />> kinds of fun talk in the archives about it. <br />><br /><br />I have reached one or two times to use build-in inet/cidr types but the lack of <br />indexing supportfor "contains op" was stopping me - i have used ip4r extension. <br /><br />I do not think that adding index supportto a datatype classifies as semantic <br />change that will break backward compatibility. <br /><br />Best regards<br />-- <br />Luben Karavelov
On Thu, Dec 1, 2011 at 9:12 AM, <karavelov@mail.bg> wrote: > I do not think that adding index support to a datatype classifies as > semantic > change that will break backward compatibility. Me neither. The ip4r type also supports ranges that aren't on CIDR-block boundaries, which probably isn't something that makes sense to incorporate into cidr. But not everyone needs that, and some people might also need support for ipv6 CIDR blocks, which ip4r doesn't support. So I don't necessarily see the existence of ip4r as a reason why cidr shouldn't have better indexing support. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
* Robert Haas (robertmhaas@gmail.com) wrote:
> Me neither. The ip4r type also supports ranges that aren't on
> CIDR-block boundaries, which probably isn't something that makes sense
> to incorporate into cidr. But not everyone needs that, and some
> people might also need support for ipv6 CIDR blocks, which ip4r
> doesn't support. So I don't necessarily see the existence of ip4r as
> a reason why cidr shouldn't have better indexing support.
Seems I wasn't clear. The semantic changes were why ip4r was *created*
(instead of just using cidr..). The fact that it's got index support is
independent from that (though, in my view, shows that people who
actually care about this data type use ip4r and don't use cidr, or we'd
hear much more complaining..).
I don't have any particular care about if cidr has indexing support or
not. I'm certainly not *against* it, except insofar as it encourages
use of a data type that really could probably be better (by being more
like ip4r..).
Thanks,
Stephen
On Thu, Dec 1, 2011 at 7:56 PM, Stephen Frost <sfrost@snowman.net> wrote: > I don't have any particular care about if cidr has indexing support or > not. I'm certainly not *against* it, except insofar as it encourages > use of a data type that really could probably be better (by being more > like ip4r..). Not that you're biased or anything! :-p -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> On Thu, Dec 1, 2011 at 7:56 PM, Stephen Frost <sfrost@snowman.net> wrote:
>> I don't have any particular care about if cidr has indexing support or
>> not. I'm certainly not *against* it, except insofar as it encourages
>> use of a data type that really could probably be better (by being more
>> like ip4r..).
> Not that you're biased or anything! :-p
IIRC, a lot of the basic behavior of the inet/cidr types was designed by
Paul Vixie (though he's not to blame for their I/O presentation).
So I'm inclined to doubt that they're as broken as Stephen claims.
regards, tom lane
On ons, 2011-11-30 at 17:56 -0500, Robert Haas wrote: > On Wed, Nov 30, 2011 at 3:58 PM, Stephen Frost <sfrost@snowman.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@gmx.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@gmx.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
----- Цитат от Tom Lane (tgl@sss.pgh.pa.us), на 02.12.2011 в 05:21 ----- <br /><br />> Robert Haas writes: <br
/>>>On Thu, Dec 1, 2011 at 7:56 PM, Stephen Frost wrote: <br />>>> I don't have any particular care
aboutif cidr has indexing support or <br />>>> not. I'm certainly not *against* it, except insofar as it
encourages<br />>>> use of a data type that really could probably be better (by being more <br />>>>
likeip4r..). <br />> <br />>> Not that you're biased or anything! :-p <br />> <br />> IIRC, a lot of the
basicbehavior of the inet/cidr types was designed by <br />> Paul Vixie (though he's not to blame for their I/O
presentation).<br />> So I'm inclined to doubt that they're as broken as Stephen claims. <br />> <br />>
regards,tom lane <br /><br /><br />I have looked at ip4r README file and my use of the extension. According to <br
/>theREADME, the main reasons for ip4r to exist are: <br /><br />1. No index support for buildin datatypes. <br />2.
Theyare variable width datatypes, because inet/cidr supports IPv6. <br />3. Semantic overloading - no random ranges,
youcould combine IP addr and <br />netmask in inet datatype. <br /><br />What I have found in my experience is that the
semanticsof inet/cidr is what <br />you need in order to model IP networks - interfaces, addresses, routing tables, <br
/>bgpsessions, LIR databases etc. In this regard the main semantic shortcommings <br />of ip4r datatype are: <br /><br
/>1.It could not represent address asignments. For example: <br />ip4r('10.0.0.1/24') is invalid. You sould represent
itwith two ip4r fields - ip4r('10.0.0.1') <br />for the address and ip4r('10.0.0.0/24') for the net. Using build-in
datatypesit <br />could be represented as inet('10.0.0.1/24') <br />2. You could have ip4r random ranges that could not
exestsin the IP network stack of <br />any device. Eg. you could not configure route as 10.0.0.2-10.0.0.6 <br />3. No
IPv6support. <br /><br />So, from my viewpoint the "semantic overloading" of inet type is what you want because <br
/>itrepresents the semantics of IP networks. <br /><br />Best regards <br /><br />-- <br />Luben Karavelov
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@gmx.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
Tom Lane <tgl@sss.pgh.pa.us> writes: > IIRC, a lot of the basic behavior of the inet/cidr types was designed by > Paul Vixie (though he's not to blame for their I/O presentation). > So I'm inclined to doubt that they're as broken as Stephen claims. The ip4r extension's main use case is range lookups. You get an ip and want to know what range it's in: GiST indexing makes that operation damn fast, and the ip4r datatype is quite flexible about what a range is. Apparently core types are solving other problems, that I never had to solve myself, so I never used them. Installing ip4r in a database is routine operation, I could accept having that by default without blinking now. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support