Re: [HACKERS] WIP: RangeTypes
On Mon, 2011-02-07 at 20:32 +0200, Peter Eisentraut wrote: Have you considered a grammar approach like for arrays, so that you would write something like CREATE TABLE ... ( foo RANGE OF int ); instead of explicitly creating a range type for every scalar type in existence? I think that that might be easier to use in the common case. It would be nice, but the type system just isn't powerful enough to express things like that right now, as far as I can tell. That works for arrays because every type in PG has a second pg_type entry for the array type. I don't think we want to do something similar for range types -- especially if there are alternative range types for a given base type. 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] WIP: RangeTypes
On sön, 2011-01-30 at 14:52 -0800, Jeff Davis wrote: * naming issues: - period - tsrange ? - periodtz - tstzrange ? - intrange - int4range Have you considered a grammar approach like for arrays, so that you would write something like CREATE TABLE ... ( foo RANGE OF int ); instead of explicitly creating a range type for every scalar type in existence? I think that that might be easier to use in the common case. I guess the trick might be how to store and pass the operator class and some other parameters. -- 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] WIP: RangeTypes
On Sun, 2011-01-30 at 17:14 -0500, Tom Lane wrote:
Jeff Davis pg...@j-davis.com writes:
On Sun, 2011-01-30 at 02:55 +, Thom Brown wrote:
postgres=# select '[18,20]'::numrange @ 19;
ERROR: operator does not exist: numrange @ integer
LINE 1: select '[18,20]'::numrange @ 19;
^
HINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts.
It's because it doesn't know the type on the right side, and assumes
it's an int4.
Well, yeah, it is an int4. The question ought to be phrased why does
the parser fail to promote the int4 to numeric?. There might be some
excuse for an operator is not unique here, but I don't understand the
above failure --- it should be able to use an implicit coercion from
int4 to numeric.
The problem exists for arrays, as well, so I think this is just a
limitation of the type system.
Regards,
Jeff Davis
postgres=# select ARRAY[1.4,1.5,1.6]::numeric[] || 5.0;
?column?
---
{1.4,1.5,1.6,5.0}
(1 row)
postgres=# select ARRAY[1.4,1.5,1.6]::numeric[] || 5;
ERROR: operator does not exist: numeric[] || integer
LINE 1: select ARRAY[1.4,1.5,1.6]::numeric[] || 5;
^
HINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts.
--
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] WIP: RangeTypes
[ trying a third time to send this message, apparently there were infrastructure problems before ] On Sun, 2011-01-30 at 02:55 +, Thom Brown wrote: postgres=# select '[18,20]'::numrange @ 19; ERROR: operator does not exist: numrange @ integer LINE 1: select '[18,20]'::numrange @ 19; ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. It's because it doesn't know the type on the right side, and assumes it's an int4. select '[18,20]'::numrange @ 19.0; works. 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] WIP: RangeTypes
On 30 January 2011 21:33, Jeff Davis pg...@j-davis.com wrote: [ trying a third time to send this message, apparently there were infrastructure problems before ] On Sun, 2011-01-30 at 02:55 +, Thom Brown wrote: postgres=# select '[18,20]'::numrange @ 19; ERROR: operator does not exist: numrange @ integer LINE 1: select '[18,20]'::numrange @ 19; ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. It's because it doesn't know the type on the right side, and assumes it's an int4. select '[18,20]'::numrange @ 19.0; works. My misapprehension stems from the assumption that the anyrange,anynonarray entry for the @ operator, and the contains(anyrange, anynonarray) function would resolve since numrange is a subset of anyrange and int4 is a subset of anynonarray. Obviously it shouldn't work as the underlying type of the range isn't an integer, but just trying to understand how the error message came about. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 -- 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] WIP: RangeTypes
Jeff Davis pg...@j-davis.com writes: On Sun, 2011-01-30 at 02:55 +, Thom Brown wrote: postgres=# select '[18,20]'::numrange @ 19; ERROR: operator does not exist: numrange @ integer LINE 1: select '[18,20]'::numrange @ 19; ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. It's because it doesn't know the type on the right side, and assumes it's an int4. Well, yeah, it is an int4. The question ought to be phrased why does the parser fail to promote the int4 to numeric?. There might be some excuse for an operator is not unique here, but I don't understand the above failure --- it should be able to use an implicit coercion from int4 to numeric. 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] WIP: RangeTypes
Another updated patch. Improvements: * Full GiST support - Thanks to Alexander Korotkov for sending me a new picksplit algorithm for my temporal project on pgfoundry. I modified it for use with range types, including a (hopefully) intelligent way of handling empty and unbounded ranges. * Quite a few tests added, some cleanup done Open items: * naming issues: - period - tsrange ? - periodtz - tstzrange ? - intrange - int4range * add int8range * Documentation improvements - CREATE TYPE - ANYRANGE - Data Types section * Thom Brown and Tom Lane pointed out that the type inferencing should be able to promote int4 to numeric for queries like: select '[18,20]'::numrange @ 19; * Should the SQL function length(), which relies on polymorphic -, be marked immutable, stable, or volatile? * representation or alignment issues * parser should be improved to handle spaces and quoting better * Should btree_gist be pulled into core to make it easier to use exclusion constraints with range types? * Typmod (optional) Regards, Jeff Davis rangetypes-20110130.patch.gz Description: GNU Zip compressed data -- 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] WIP: RangeTypes
On Fri, 2011-01-28 at 21:52 +, Thom Brown wrote: This is not very graceful: postgres=# CREATE TYPE numrange AS RANGE (SUBTYPE=numeric, SUBTYPE_CMP=numeric_cmp); ERROR: duplicate key value violates unique constraint pg_range_rgnsubtype_index DETAIL: Key (rngsubtype)=(1700) already exists. You're right, that should be a much nicer error message. Also, if I try the same, but with a different name for the type, I get the same error. Why does that restriction exist? Can't you have types which happen to use the exact same subtype? At first, that's how I designed it. Then, I realized that the type system needs to know the range type from the element type in order for something like ANYRANGE to work. There's a workaround though: create a domain over numeric, and then create a range over mynumeric. =# create domain mynumeric as numeric; CREATE DOMAIN =# create type numrange2 as range (subtype=numeric, subtype_cmp=numeric_cmp); ERROR: duplicate key value violates unique constraint pg_range_rgnsubtype_index DETAIL: Key (rngsubtype)=(1700) already exists. =# create type numrange2 as range (subtype=mynumeric, subtype_cmp=numeric_cmp); CREATE TYPE =# select range(1.1::mynumeric,2.2::mynumeric); range -- [ 1.1, 2.2 ) (1 row) Also, how do you remove a range type which coincides with a system range type. For example: postgres=# CREATE TYPE numrange AS RANGE (SUBTYPE=interval, SUBTYPE_CMP=interval_cmp); CREATE TYPE postgres=# drop type numrange; ERROR: cannot drop type numrange because it is required by the database system Is this because I shouldn't have been able to create this type in the first place? The types are in two different schemas. It's just as though you created a table called pg_class. To drop the one you created, do: DROP TYPE public.numrange; 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] WIP: RangeTypes
On Fri, 2011-01-28 at 10:41 -0800, David E. Wheeler wrote: +1 in principal. I think we should try to avoid the user of the term period if possible, and I see definite benefits to a simple model of $typename . 'range'; Interesting, I didn't realize that PERIOD was such an undesirable type name. Is there GIN support? GIN seems to be the preferred index type for this sort of thing, no? GiST is the natural index access method if we approach ranges as a spatial type. I don't quite know what you have in mind for GIN; what keys would you extract from the value '[1.23,4.56)' ? 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] WIP: RangeTypes
On 29 January 2011 18:52, Jeff Davis pg...@j-davis.com wrote: On Fri, 2011-01-28 at 21:52 +, Thom Brown wrote: Also, how do you remove a range type which coincides with a system range type. For example: postgres=# CREATE TYPE numrange AS RANGE (SUBTYPE=interval, SUBTYPE_CMP=interval_cmp); CREATE TYPE postgres=# drop type numrange; ERROR: cannot drop type numrange because it is required by the database system Is this because I shouldn't have been able to create this type in the first place? The types are in two different schemas. It's just as though you created a table called pg_class. To drop the one you created, do: DROP TYPE public.numrange; *facepalm* Of course. :) My bad. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 -- 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] WIP: RangeTypes
On Jan 29, 2011, at 10:57 AM, Jeff Davis wrote: On Fri, 2011-01-28 at 10:41 -0800, David E. Wheeler wrote: +1 in principal. I think we should try to avoid the user of the term period if possible, and I see definite benefits to a simple model of $typename . 'range'; Interesting, I didn't realize that PERIOD was such an undesirable type name. It's not *hugely* undesirable. I just tend to think that range is more so. Is there GIN support? GIN seems to be the preferred index type for this sort of thing, no? GiST is the natural index access method if we approach ranges as a spatial type. I don't quite know what you have in mind for GIN; what keys would you extract from the value '[1.23,4.56)' ? I think I'm just revealing my ignorance of these index types and what they're good for. My impression has been that GIN was a better but less-full-featured alternative to GiST and getting better with Tom's recent fixes for its handling of NULLs. But, uh, obviously not. Best, David -- 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] WIP: RangeTypes
On Fri, 2011-01-28 at 14:15 -0500, Chris Browne wrote: Mind you, timestamptzrange seems a mite *long* to me. Right. I think we might need to compromise here an use some shorter names. tsrange/tstzrange/numrange seem reasonable to me. Making sure it's consistent with int4, int8, bigint sure seems like a good idea. OK, I'll change intrange to int4range, and add int8range. int2range doesn't seem useful, though. 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] WIP: RangeTypes
On Sat, 2011-01-29 at 11:00 -0800, David E. Wheeler wrote: I think I'm just revealing my ignorance of these index types and what they're good for. My impression has been that GIN was a better but less-full-featured alternative to GiST and getting better with Tom's recent fixes for its handling of NULLs. But, uh, obviously not. The idea of GIN is that you store multiple entries for each tuple you insert. So, inserting a tuple containing the document 'hello world' would store the keys hello and world both pointing back to that tuple. It also makes sense for arrays. But ranges are arbitrarily long, and don't have any defined step, so that means an infinite number of keys. GiST works better for that. 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] WIP: RangeTypes
Jeff Davis pg...@j-davis.com writes: On Fri, 2011-01-28 at 21:52 +, Thom Brown wrote: Also, if I try the same, but with a different name for the type, I get the same error. Why does that restriction exist? Can't you have types which happen to use the exact same subtype? At first, that's how I designed it. Then, I realized that the type system needs to know the range type from the element type in order for something like ANYRANGE to work. That seems like a fairly bad restriction. In a datatype with multiple useful sort orderings, it'd be desirable to be able to create a range type for each such ordering, no? I'd be inclined to think of a range type as being defined by element type plus a btree opfamily. Maybe it'd be okay to insist on that combination as being unique. 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] WIP: RangeTypes
On Sat, 2011-01-29 at 14:42 -0500, Tom Lane wrote: Jeff Davis pg...@j-davis.com writes: On Fri, 2011-01-28 at 21:52 +, Thom Brown wrote: Also, if I try the same, but with a different name for the type, I get the same error. Why does that restriction exist? Can't you have types which happen to use the exact same subtype? At first, that's how I designed it. Then, I realized that the type system needs to know the range type from the element type in order for something like ANYRANGE to work. That seems like a fairly bad restriction. In a datatype with multiple useful sort orderings, it'd be desirable to be able to create a range type for each such ordering, no? I'd be inclined to think of a range type as being defined by element type plus a btree opfamily. Maybe it'd be okay to insist on that combination as being unique. I couldn't find another way to make a function with a definition like: range(ANYELEMENT, ANYELEMENT) returns ANYRANGE work. And it seemed worse to live without a constructor like that. Ideas? Also, it's not based on the btree opfamily right now. It's just based on a user-supplied compare function. I think I could change it to store the opfamily instead, if you think that's a better idea. 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] WIP: RangeTypes
On 29 January 2011 19:53, Jeff Davis pg...@j-davis.com wrote: On Sat, 2011-01-29 at 14:42 -0500, Tom Lane wrote: Jeff Davis pg...@j-davis.com writes: On Fri, 2011-01-28 at 21:52 +, Thom Brown wrote: Also, if I try the same, but with a different name for the type, I get the same error. Why does that restriction exist? Can't you have types which happen to use the exact same subtype? At first, that's how I designed it. Then, I realized that the type system needs to know the range type from the element type in order for something like ANYRANGE to work. That seems like a fairly bad restriction. In a datatype with multiple useful sort orderings, it'd be desirable to be able to create a range type for each such ordering, no? I'd be inclined to think of a range type as being defined by element type plus a btree opfamily. Maybe it'd be okay to insist on that combination as being unique. I couldn't find another way to make a function with a definition like: range(ANYELEMENT, ANYELEMENT) returns ANYRANGE work. And it seemed worse to live without a constructor like that. Ideas? Also, it's not based on the btree opfamily right now. It's just based on a user-supplied compare function. I think I could change it to store the opfamily instead, if you think that's a better idea. Probably ignorance here, but why does the following not work? postgres=# select '[18,20]'::numrange @ 19; ERROR: operator does not exist: numrange @ integer LINE 1: select '[18,20]'::numrange @ 19; ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. I can see both the wiki page on range types and the pg_operator table appear to indicate this should work: postgres=# select o.oprname, tl.typname as lefttype, tr.typname as righttype from pg_operator o left join pg_type tl on o.oprleft = tl.oid left join pg_type tr on o.oprright = tr.oid where 'anyrange' in (tl.typname, tr.typname) and oprname = '@'; oprname | lefttype | righttype -+--+- @ | anyrange | anynonarray @ | anyrange | anyrange (2 rows) -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 -- 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] WIP: RangeTypes
On 30 January 2011 02:55, Thom Brown t...@linux.com wrote: On 29 January 2011 19:53, Jeff Davis pg...@j-davis.com wrote: On Sat, 2011-01-29 at 14:42 -0500, Tom Lane wrote: Jeff Davis pg...@j-davis.com writes: On Fri, 2011-01-28 at 21:52 +, Thom Brown wrote: Also, if I try the same, but with a different name for the type, I get the same error. Why does that restriction exist? Can't you have types which happen to use the exact same subtype? At first, that's how I designed it. Then, I realized that the type system needs to know the range type from the element type in order for something like ANYRANGE to work. That seems like a fairly bad restriction. In a datatype with multiple useful sort orderings, it'd be desirable to be able to create a range type for each such ordering, no? I'd be inclined to think of a range type as being defined by element type plus a btree opfamily. Maybe it'd be okay to insist on that combination as being unique. I couldn't find another way to make a function with a definition like: range(ANYELEMENT, ANYELEMENT) returns ANYRANGE work. And it seemed worse to live without a constructor like that. Ideas? Also, it's not based on the btree opfamily right now. It's just based on a user-supplied compare function. I think I could change it to store the opfamily instead, if you think that's a better idea. Probably ignorance here, but why does the following not work? postgres=# select '[18,20]'::numrange @ 19; ERROR: operator does not exist: numrange @ integer LINE 1: select '[18,20]'::numrange @ 19; ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. I can see both the wiki page on range types and the pg_operator table appear to indicate this should work: postgres=# select o.oprname, tl.typname as lefttype, tr.typname as righttype from pg_operator o left join pg_type tl on o.oprleft = tl.oid left join pg_type tr on o.oprright = tr.oid where 'anyrange' in (tl.typname, tr.typname) and oprname = '@'; oprname | lefttype | righttype -+--+- @ | anyrange | anynonarray @ | anyrange | anyrange (2 rows) As for docs, anyrange will need mentioning as part of the information about polymorphic types: http://developer.postgresql.org/pgdocs/postgres/extend-type-system.html And on the pseudo-types page: http://developer.postgresql.org/pgdocs/postgres/datatype-pseudo.html -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 -- 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] WIP: RangeTypes
On Thu, Jan 27, 2011 at 11:45:30PM -0800, Jeff Davis wrote: Updated patch. Changes: * Documentation for operators/functions * a comprehensive set of operators and functions * BTree opclass Yay! * Hash opclass * built-in range types: - PERIOD (timestamp) - PERIODTZ (timestamptz) For consistency, and in order not to continue our atrocious naming tradition, I'd like to propose that the above be named timestamprange (tsrange for short) and timestamptzrange (tstzrange for short). - DATERANGE (date) Yay! - INTRANGE (int4) int4range/intrange and the missing bigintrange/int8range - NUMRANGE (numeric) numericrange/numrange. Should there also be a timerange and a timetzrange? * added subtype float function to the API, which will be useful for GiST w00t! * created canonical functions for intrange and daterange, so that: '[1,5]'::intrange = '[1,6)'::intrange Excellent! * added length() function, written in SQL as: select upper($1) - lower($1) which uses polymorphic - operator to avoid the need to give the subtype subtract function and return type to the generic API Open items: * More documentation work * Settle any representation/alignment concerns * Should the new length() function be marked as immutable, stable, or volatile? It uses the polymorphic - operator, and I suppose someone could define a non-immutable version of that before calling length(). Then again, it is likely to be inlined anyway, right? * GiST - docs - catalog work - implementation * typmod support (optional) This is nearing completion. GiST is by far the most amount of effort remaining that I'm aware of. Comments about the API, naming, representation, interface, funcationality, grammar, etc. are welcome. Regards, Jeff Davis I'd offer to help, but personal matters press this weekend :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] WIP: RangeTypes
On Fri, 2011-01-28 at 09:17 -0800, David Fetter wrote: For consistency, and in order not to continue our atrocious naming tradition, I'd like to propose that the above be named timestamprange (tsrange for short) and timestamptzrange (tstzrange for short). No real objection, but I'd like to see if someone else will second it. Also, I don't think aliases are very easy to define. They appear to all be special cases in the backend code, without catalog support. Should I use domains? If not, I think we'll have to stick to one name. - INTRANGE (int4) int4range/intrange and the missing bigintrange/int8range I thought about adding int8range, and the first time around that's what I tried. But then I realized that the literal 4 is interpreted as an int4, meaning that range(1,10) would be interpreted as int4range, so int8range was slightly annoying to use because you have to cast the literals. Also, the storage is not particularly efficient right now anyway, so if you need int8range, you could probably use numrange instead. I don't mind either way. If you think someone will use it, I'll add it. Should there also be a timerange and a timetzrange? I thought about it, and I realized that I've never seen the time type used. Again, I'll add it if someone will use it. Keep in mind that it's fairly easy for people to add their own range types. The most difficult part is defining the canonical function if it is applicable, and the subtype_float function which is necessary for GiST. 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] WIP: RangeTypes
Jeff Davis pg...@j-davis.com writes: On Fri, 2011-01-28 at 09:17 -0800, David Fetter wrote: For consistency, and in order not to continue our atrocious naming tradition, I'd like to propose that the above be named timestamprange (tsrange for short) and timestamptzrange (tstzrange for short). No real objection, but I'd like to see if someone else will second it. Also, I don't think aliases are very easy to define. They are not, and should be avoided. I don't think we have *any* typename aliases except for cases required by SQL standard. Should there also be a timerange and a timetzrange? I thought about it, and I realized that I've never seen the time type used. Again, I'll add it if someone will use it. I have no idea what the semantics of timetzrange would be. Even timerange would be a bit funny --- is 11PM before or after 1AM? 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] WIP: RangeTypes
On Jan 28, 2011, at 9:48 AM, Jeff Davis wrote: On Fri, 2011-01-28 at 09:17 -0800, David Fetter wrote: For consistency, and in order not to continue our atrocious naming tradition, I'd like to propose that the above be named timestamprange (tsrange for short) and timestamptzrange (tstzrange for short). No real objection, but I'd like to see if someone else will second it. +1 in principal. I think we should try to avoid the user of the term period if possible, and I see definite benefits to a simple model of $typename . 'range'; Keep in mind that it's fairly easy for people to add their own range types. The most difficult part is defining the canonical function if it is applicable, and the subtype_float function which is necessary for GiST. Is there GIN support? GIN seems to be the preferred index type for this sort of thing, no? Best, David -- 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] WIP: RangeTypes
pg...@j-davis.com (Jeff Davis) writes:
On Fri, 2011-01-28 at 09:17 -0800, David Fetter wrote:
For consistency, and in order not to continue our atrocious naming
tradition, I'd like to propose that the above be named timestamprange
(tsrange for short) and timestamptzrange (tstzrange for short).
No real objection, but I'd like to see if someone else will second it.
Also, I don't think aliases are very easy to define. They appear to all
be special cases in the backend code, without catalog support. Should I
use domains? If not, I think we'll have to stick to one name.
Somehow, rangets, rangetstz seem better to me, but that's not a deep
issue. I'm not certain of the basis for *truly* preferring an ordering
of the components (ts/timestamp, tz, range). As long as it's rational,
and not too terribly inconsistent with other prefix/suffix handlings,
I'm fine with it.
Mind you, timestamptzrange seems a mite *long* to me.
- INTRANGE (int4)
int4range/intrange and the missing bigintrange/int8range
I thought about adding int8range, and the first time around that's what
I tried. But then I realized that the literal 4 is interpreted as an
int4, meaning that range(1,10) would be interpreted as int4range, so
int8range was slightly annoying to use because you have to cast the
literals.
Also, the storage is not particularly efficient right now anyway, so if
you need int8range, you could probably use numrange instead.
I don't mind either way. If you think someone will use it, I'll add it.
Making sure it's consistent with int4, int8, bigint sure seems like a
good idea.
Should there also be a timerange and a timetzrange?
I thought about it, and I realized that I've never seen the time type
used. Again, I'll add it if someone will use it.
Keep in mind that it's fairly easy for people to add their own range
types. The most difficult part is defining the canonical function if
it is applicable, and the subtype_float function which is necessary
for GiST.
I don't see much use for time; it is *so* likely that you'll need date
overlaps that it's difficult for it to be useful without making it
extremely magical (e.g. - stowing a lot of logic inside that adds in
date information behind the scenes).
FYI, it's compiling and testing fine for me. This one strikes me as an
exciting change, once GIST is in place. Well, actually, even without it :-).
postgres@localhost- insert into foo (dr) values ('[2010-01-01,2011-12-31)');
INSERT 0 1
postgres@localhost- select * from foo;
id | dr
+
1 | [ 2010-01-01, 2011-12-31 )
(1 row)
--
let name=cbbrowne and tld=gmail.com in String.concat @ [name;tld];;
http://linuxfinances.info/info/rdbms.html
If vegetarians eat vegetables, what do humanitarians eat?
--
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] WIP: RangeTypes
On 28 January 2011 07:45, Jeff Davis pg...@j-davis.com wrote: Updated patch. Changes: * Documentation for operators/functions * a comprehensive set of operators and functions * BTree opclass * Hash opclass * built-in range types: - PERIOD (timestamp) - PERIODTZ (timestamptz) - DATERANGE (date) - INTRANGE (int4) - NUMRANGE (numeric) * added subtype float function to the API, which will be useful for GiST * created canonical functions for intrange and daterange, so that: '[1,5]'::intrange = '[1,6)'::intrange * added length() function, written in SQL as: select upper($1) - lower($1) which uses polymorphic - operator to avoid the need to give the subtype subtract function and return type to the generic API Open items: * More documentation work * Settle any representation/alignment concerns * Should the new length() function be marked as immutable, stable, or volatile? It uses the polymorphic - operator, and I suppose someone could define a non-immutable version of that before calling length(). Then again, it is likely to be inlined anyway, right? * GiST - docs - catalog work - implementation * typmod support (optional) This is nearing completion. GiST is by far the most amount of effort remaining that I'm aware of. Comments about the API, naming, representation, interface, funcationality, grammar, etc. are welcome. Regards, Jeff Davis Very nice work Jeff! This is not very graceful: postgres=# CREATE TYPE numrange AS RANGE (SUBTYPE=numeric, SUBTYPE_CMP=numeric_cmp); ERROR: duplicate key value violates unique constraint pg_range_rgnsubtype_index DETAIL: Key (rngsubtype)=(1700) already exists. Also, if I try the same, but with a different name for the type, I get the same error. Why does that restriction exist? Can't you have types which happen to use the exact same subtype? -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 -- 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] WIP: RangeTypes
On 28 January 2011 20:28, Thom Brown t...@linux.com wrote: On 28 January 2011 07:45, Jeff Davis pg...@j-davis.com wrote: Updated patch. Changes: * Documentation for operators/functions * a comprehensive set of operators and functions * BTree opclass * Hash opclass * built-in range types: - PERIOD (timestamp) - PERIODTZ (timestamptz) - DATERANGE (date) - INTRANGE (int4) - NUMRANGE (numeric) * added subtype float function to the API, which will be useful for GiST * created canonical functions for intrange and daterange, so that: '[1,5]'::intrange = '[1,6)'::intrange * added length() function, written in SQL as: select upper($1) - lower($1) which uses polymorphic - operator to avoid the need to give the subtype subtract function and return type to the generic API Open items: * More documentation work * Settle any representation/alignment concerns * Should the new length() function be marked as immutable, stable, or volatile? It uses the polymorphic - operator, and I suppose someone could define a non-immutable version of that before calling length(). Then again, it is likely to be inlined anyway, right? * GiST - docs - catalog work - implementation * typmod support (optional) This is nearing completion. GiST is by far the most amount of effort remaining that I'm aware of. Comments about the API, naming, representation, interface, funcationality, grammar, etc. are welcome. Regards, Jeff Davis Very nice work Jeff! This is not very graceful: postgres=# CREATE TYPE numrange AS RANGE (SUBTYPE=numeric, SUBTYPE_CMP=numeric_cmp); ERROR: duplicate key value violates unique constraint pg_range_rgnsubtype_index DETAIL: Key (rngsubtype)=(1700) already exists. Also, if I try the same, but with a different name for the type, I get the same error. Why does that restriction exist? Can't you have types which happen to use the exact same subtype? Also, how do you remove a range type which coincides with a system range type. For example: postgres=# CREATE TYPE numrange AS RANGE (SUBTYPE=interval, SUBTYPE_CMP=interval_cmp); CREATE TYPE postgres=# drop type numrange; ERROR: cannot drop type numrange because it is required by the database system Is this because I shouldn't have been able to create this type in the first place? -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 -- 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] WIP: RangeTypes
Updated patch. Changes: * Documentation for operators/functions * a comprehensive set of operators and functions * BTree opclass * Hash opclass * built-in range types: - PERIOD (timestamp) - PERIODTZ (timestamptz) - DATERANGE (date) - INTRANGE (int4) - NUMRANGE (numeric) * added subtype float function to the API, which will be useful for GiST * created canonical functions for intrange and daterange, so that: '[1,5]'::intrange = '[1,6)'::intrange * added length() function, written in SQL as: select upper($1) - lower($1) which uses polymorphic - operator to avoid the need to give the subtype subtract function and return type to the generic API Open items: * More documentation work * Settle any representation/alignment concerns * Should the new length() function be marked as immutable, stable, or volatile? It uses the polymorphic - operator, and I suppose someone could define a non-immutable version of that before calling length(). Then again, it is likely to be inlined anyway, right? * GiST - docs - catalog work - implementation * typmod support (optional) This is nearing completion. GiST is by far the most amount of effort remaining that I'm aware of. Comments about the API, naming, representation, interface, funcationality, grammar, etc. are welcome. Regards, Jeff Davis rangetypes-20110127.patch.gz Description: GNU Zip compressed data -- 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] WIP: RangeTypes
On Thu, Jan 20, 2011 at 4:29 AM, Jeff Davis pg...@j-davis.com wrote: New patch. I added a lot of generic range functions, and a lot of operators. There is still more work to do, this is just an updated patch. The latest can be seen on the git repository, as well: So is this 9.2 material at this point? -- 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] WIP: RangeTypes
On Fri, 2011-01-21 at 12:31 -0500, Robert Haas wrote: On Thu, Jan 20, 2011 at 4:29 AM, Jeff Davis pg...@j-davis.com wrote: New patch. I added a lot of generic range functions, and a lot of operators. There is still more work to do, this is just an updated patch. The latest can be seen on the git repository, as well: So is this 9.2 material at this point? Regardless of whether it's eligible to be in 9.1, I plan to keep working on it. I would appreciate some overall feedback during this commitfest. Much of the code is there, so it would be helpful if we could settle issues like representation, functionality, interface, catalog, API, grammar, and naming. Otherwise, those issues will just be a reason to bounce it from commitfest-next, as well. 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] WIP: RangeTypes
On Fri, Jan 21, 2011 at 2:30 PM, Jeff Davis pg...@j-davis.com wrote: On Fri, 2011-01-21 at 12:31 -0500, Robert Haas wrote: On Thu, Jan 20, 2011 at 4:29 AM, Jeff Davis pg...@j-davis.com wrote: New patch. I added a lot of generic range functions, and a lot of operators. There is still more work to do, this is just an updated patch. The latest can be seen on the git repository, as well: So is this 9.2 material at this point? Regardless of whether it's eligible to be in 9.1, I plan to keep working on it. I would appreciate some overall feedback during this commitfest. Much of the code is there, so it would be helpful if we could settle issues like representation, functionality, interface, catalog, API, grammar, and naming. Otherwise, those issues will just be a reason to bounce it from commitfest-next, as well. Agreed. -- 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] WIP: RangeTypes
New patch. I added a lot of generic range functions, and a lot of operators. There is still more work to do, this is just an updated patch. The latest can be seen on the git repository, as well: http://git.postgresql.org/gitweb?p=users/jdavis/postgres.git;a=log;h=refs/heads/rangetypes Regards, Jeff Davis rangetypes-20110119.gz Description: GNU Zip compressed data -- 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] WIP: RangeTypes
When defining generic range functions, there is quite a bit of extra complexity needed to handle special cases. The special cases are due to: * empty ranges * ranges with infinite boundaries * ranges with NULL boundaries * ranges with exclusive bounds (e.g. ( or )). Infinite bounds, and exclusive bounds can both be handled somewhat reasonably, and the complexity can be somewhat hidden. Empty ranges are a special case, but can be handled at the top of the generic function in a straightforward way. NULL bounds, however, have been causing me a little frustration. A reasonable interpretation of boolean operators that operate on ranges might be: true or false if we can prove it from only the inputs; else NULL. This gets a little interesting because a NULL value as a range boundary isn't 100% unknown: it's known to be on one side of the other bound (assuming that the other side is known). This is similar to how AND and OR behave for NULL. For instance, take the simple definition of contains: r1.a = r2.a AND r1.b = r2.b (where a is the lower bound and b is the upper) Consider r1: [NULL, 10], r2: [20, NULL]. Contains should return false according to our rule above, because no matter what the values of r1.a and r2.b, the ranges can't possibly overlap. So, now, more complexity needs to be added. We can be more redundant and do: r1.a = r2.a AND r1.b = r2 AND r1.a = r2.b AND r1.b = r2.a That seems a little error-prone and harder to understand. Then, when we have functions that operate on ranges and return ranges, we're not dealing with 3VL exactly, but some other intuition about what NULL should do. The semantics get a lot more complicated and hard to reason about. For instance, what about: (NULL, 5) INTERSECT (3, NULL) Should that evaluate to NULL, (NULL, NULL), or throw an error? What about: (NULL, 5) MINUS (NULL, 7) (NULL, 5) MINUS (3, NULL) I feel like I'm making this too complicated. Should I just scope out NULL range boundaries for the first cut, and leave room in the representation so that it can be added when there is a more thorough proposal for NULL range boundaries? 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] WIP: RangeTypes
On Mon, Jan 17, 2011 at 01:09:26PM -0800, Jeff Davis wrote: When defining generic range functions, there is quite a bit of extra complexity needed to handle special cases. The special cases are due to: * empty ranges * ranges with infinite boundaries * ranges with NULL boundaries * ranges with exclusive bounds (e.g. ( or )). Infinite bounds, and exclusive bounds can both be handled somewhat reasonably, and the complexity can be somewhat hidden. Empty ranges are a special case, but can be handled at the top of the generic function in a straightforward way. NULL bounds, however, have been causing me a little frustration. [Explanation and illustrations]. In that case, let's leave them out for this cut. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] WIP: RangeTypes
Jeff Davis pg...@j-davis.com writes: I feel like I'm making this too complicated. Should I just scope out NULL range boundaries for the first cut, and leave room in the representation so that it can be added when there is a more thorough proposal for NULL range boundaries? +1. I'm far from convinced that a null boundary is sane at all. If you don't know the value, how do you know it's greater/less than the other bound? 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] WIP: RangeTypes
On 1/17/11 1:09 PM, Jeff Davis wrote: I feel like I'm making this too complicated. Should I just scope out NULL range boundaries for the first cut, and leave room in the representation so that it can be added when there is a more thorough proposal for NULL range boundaries? Well, NULL range boundaries aren't usable with Temporal, and yet I wrote a whole scheduling application around it. So I think it's OK to have them as a TODO and raise an error for now. Heck, we had arrays which didn't accept NULLs for years. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- 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] WIP: RangeTypes
Updated patch. Summary of changes: * More generic functions * pg_dump support * remove typmod support until it can be done correctly * added some tests There is still quite a bit left, including (numbers match up with previous TODO list): 1. Generic functions -- still more work to do here. Handling the combination of continuous range semantics with NULLs requires quite a lot of special cases, because it's hard to share code among functions. Even something as simple as equals is not as trivial as it sounds. Perhaps I'm missing some cleaner abstractions, or perhaps I'm over-thinking the null semantics. 3. perhaps fix typmod 4. documentation 5. more tests 7. better parser Regards, Jeff Davis rangetypes-20110114.patch.gz Description: GNU Zip compressed data -- 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] WIP: RangeTypes
On Tue, 2011-01-11 at 11:13 -0800, David Fetter wrote: 3. Typmod -- There is still one annoyance about typmod remaining. I need to treat it like an array in find_typmod_coercion_function(), and then create a coercion expression. Is it worth it? Would typmod on a range be confusing, or should I just finish this item up? Probably not worth it for the first round. OK, I'll block typmods for range types for now. 4. Docs Happy to help evenings this week :) 5. Tests Same. What do you have so far? Great! I think the best tests would be around the ANYRANGE type mechanism to see if anything seems wrong or limiting. Particularly, its interaction with ANYELEMENT. 7. Right now the parse function is quite dumb. Is there some example code I should follow to make sure I get this right? KISS is a fine principle. Do you really need it smart on the first round? :) Well, it needs to be correct ;) Specifically, I think there will be a problem if there is a multibyte character following a backslash. There may be other problems, as well. I could probably get these fixed, but it might be better to follow patterns in other code. I'll look into it. 8. In order to properly support the various combinations of ANYRANGE and ANYELEMENT in a function definition (which are all important), we need to be able to determine the range type given a subtype. That means that each subtype can only have one associated range, which sounds somewhat limiting, but it can be worked around by using domains. I don't think this is a major limitation. Comments? As we get a more nuanced type system, this is one of the things that will need to get reworked, so I'd say it's better not to put too much effort into things that a refactor of the type system http://wiki.postgresql.org/wiki/Refactor_Type_System would make much better, at least right now. Sounds good. I don't think this is an actual problem, so I'll consider this a non-issue unless someone else has a comment. Also related to representation: * Right now I always align the subtypes within the range according to typalign. I could avoid that by packing the bytes tightly, and then copying them around later. Suggestions? And what should the overall alignment of the range type be? For the first cut, the simplest possible. OK. It's already about as simple as it can get, but might be fairly wasteful. * If it's a fixed-length type, we can save the varlena header byte on the overall range; but we lose the ability to save space when one of the boundaries of the range is missing (NULL or INF), and it would complicate the code a little. Thoughts? Probably not worth complicating the code at this stage. KISS again :) OK. 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] WIP: RangeTypes
On Tue, Jan 11, 2011 at 01:16:47AM -0800, Jeff Davis wrote:
Ok, I have made some progress. This is still a proof-of-concept patch,
but the important pieces are working together.
Synopsis:
CREATE TYPE numrange AS RANGE (SUBTYPE=numeric,
SUBTYPE_CMP=numeric_cmp);
SELECT range_eq('[1,2.2)'::numrange,'[1,2.2]');
SELECT range_lbound('(3.7,9]'::numrange);
SELECT range(6.7);
SELECT '-'::numrange; -- empty
SELECT '[1, NULL]'::numrange; -- ] will become )
SELECT '(INF, 3)'::numrange;
I haven't completed many of the other generic functions, because I'd
like to make sure I'm on the right track first. The important thing
about the functions above is that they show ANYRANGE working in
conjunction with ANYELEMENT in various combinations, which was a
significant part of this patch.
Here are the open items:
1. Generic functions -- most of which are fairly obvious. However, I
want to make sure I'm on the right track first.
2. GiST -- I'll need a mechanism to implement the penalty function,
and perhaps I'll also need additional support for the picksplit
function. For the penalty function, I think I'll need to require a
function to convert the subtype into a float, and I can use that to find
a distance (which can be the penalty). That should also satisfy anything
that picksplit might need.
3. Typmod -- There is still one annoyance about typmod remaining. I need
to treat it like an array in find_typmod_coercion_function(), and then
create a coercion expression. Is it worth it? Would typmod on a range be
confusing, or should I just finish this item up?
Probably not worth it for the first round.
4. Docs
Happy to help evenings this week :)
5. Tests
Same. What do you have so far?
6. pg_dump -- should be pretty easy; I just want to settle some of the
other stuff first.
7. Right now the parse function is quite dumb. Is there some example
code I should follow to make sure I get this right?
KISS is a fine principle. Do you really need it smart on the first
round? :)
8. In order to properly support the various combinations of ANYRANGE and
ANYELEMENT in a function definition (which are all important), we need
to be able to determine the range type given a subtype. That means that
each subtype can only have one associated range, which sounds somewhat
limiting, but it can be worked around by using domains. I don't think
this is a major limitation. Comments?
As we get a more nuanced type system, this is one of the things that
will need to get reworked, so I'd say it's better not to put too much
effort into things that a refactor of the type system
http://wiki.postgresql.org/wiki/Refactor_Type_System would make much
better, at least right now.
Also related to representation:
* Right now I always align the subtypes within the range according to
typalign. I could avoid that by packing the bytes tightly, and then
copying them around later. Suggestions? And what should the overall
alignment of the range type be?
For the first cut, the simplest possible.
* If it's a fixed-length type, we can save the varlena header byte on
the overall range; but we lose the ability to save space when one of the
boundaries of the range is missing (NULL or INF), and it would
complicate the code a little. Thoughts?
Probably not worth complicating the code at this stage. KISS again :)
Cheers,
David.
--
David Fetter da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
