Re: [HACKERS] Why so few built-in range types?

2011-12-03 Thread Dimitri Fontaine
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 pete...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Why so few built-in range types?

2011-12-03 Thread Dimitri Fontaine
Tom Lane t...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Why so few built-in range types?

2011-12-02 Thread Peter Eisentraut
On ons, 2011-11-30 at 17:56 -0500, Robert Haas wrote:
 On Wed, Nov 30, 2011 at 3:58 PM, Stephen Frost sfr...@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.



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Why so few built-in range types?

2011-12-02 Thread Stephen Frost
* Peter Eisentraut (pete...@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


signature.asc
Description: Digital signature


Re: [HACKERS] Why so few built-in range types?

2011-12-02 Thread Robert Haas
On Fri, Dec 2, 2011 at 3:42 AM, Peter Eisentraut pete...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Why so few built-in range types?

2011-12-02 Thread karavelov
- Цитат от Tom Lane (t...@sss.pgh.pa.us), на 02.12.2011 в 05:21 -

 Robert Haas robertmh...@gmail.com writes:
 On Thu, Dec 1, 2011 at 7:56 PM, Stephen Frost sfr...@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


I have looked at ip4r README file and my use of the extension. According to 
the README, the main reasons for ip4r to exist are:

1. No index support for buildin datatypes.
2. They are variable width datatypes, because inet/cidr supports IPv6.
3. Semantic overloading - no random ranges, you could combine IP addr and 
netmask in inet datatype.

What I have found in my experience is that the semantics of inet/cidr is what 
you need in order to model IP networks - interfaces, addresses, routing tables,
bgp sessions, LIR databases etc. In this regard the main semantic shortcommings 
of ip4r datatype are:

1. It could not represent address asignments. For example:
ip4r('10.0.0.1/24') is invalid. You sould represent it with two ip4r fields - 
ip4r('10.0.0.1')
for the address and ip4r('10.0.0.0/24') for the net. Using build-in datatypes it
could be represented as inet('10.0.0.1/24')
2. You could  have ip4r random ranges that could not exests in the IP network 
stack of
any device. Eg. you could not configure route as 10.0.0.2-10.0.0.6
3. No IPv6 support.

So, from my viewpoint the semantic overloading of inet type is what you want 
because
it represents the semantics of IP networks. 

Best regards

--
Luben Karavelov

Re: [HACKERS] Why so few built-in range types?

2011-12-01 Thread Stephen Frost
* Robert Haas (robertmh...@gmail.com) wrote:
 On Wed, Nov 30, 2011 at 3:58 PM, Stephen Frost sfr...@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


signature.asc
Description: Digital signature


Re: [HACKERS] Why so few built-in range types?

2011-12-01 Thread karavelov
- Цитат от Stephen Frost (sfr...@snowman.net), на 01.12.2011 в 15:56 -

 * Robert Haas (robertmh...@gmail.com) wrote:
 On Wed, Nov 30, 2011 at 3:58 PM, Stephen Frost sfr...@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.
 

I have reached one or two times to use build-in inet/cidr types but the lack of
indexing support for contains op was stopping me - i have used ip4r extension.

I do not think that adding index support to a datatype classifies as semantic 
change that will break backward compatibility.

Best regards
--
Luben Karavelov

Re: [HACKERS] Why so few built-in range types?

2011-12-01 Thread Robert Haas
On Thu, Dec 1, 2011 at 9:12 AM,  karave...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Why so few built-in range types?

2011-12-01 Thread Stephen Frost
* Robert Haas (robertmh...@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


signature.asc
Description: Digital signature


Re: [HACKERS] Why so few built-in range types?

2011-12-01 Thread Robert Haas
On Thu, Dec 1, 2011 at 7:56 PM, Stephen Frost sfr...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Why so few built-in range types?

2011-12-01 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes:
 On Thu, Dec 1, 2011 at 7:56 PM, Stephen Frost sfr...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Why so few built-in range types?

2011-11-30 Thread Jeff Davis
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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Why so few built-in range types?

2011-11-30 Thread Tom Lane
Jeff Davis pg...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Why so few built-in range types?

2011-11-30 Thread Robert Haas
On Wed, Nov 30, 2011 at 1:08 PM, Jeff Davis pg...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Why so few built-in range types?

2011-11-30 Thread Stephen Frost
* Robert Haas (robertmh...@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


signature.asc
Description: Digital signature


Re: [HACKERS] Why so few built-in range types?

2011-11-30 Thread Robert Haas
On Wed, Nov 30, 2011 at 3:58 PM, Stephen Frost sfr...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers