Range types
in [PgSql]
|
Prev: Winflex
Next: [HACKERS] Fast or immediate shutdown
From: Jeff Davis on 14 Dec 2009 13:22 On Mon, 2009-12-14 at 10:00 -0800, Nathan Boley wrote: > IMHO the first question is whether, for integers, [1,2] UNION [3,5] > should be equal to [1,5]. In math this is certainly true, and defining > 'next' seems like a reasonable way to establish this in postgres. [ you say "yes" ] Agreed. > The next question is whether, for floats, [1,3-FLT_EPSILON] UNION > [3,5] should be [1,5]. [ you say "no" ] I think this should be true, because all floats between 1 and 5 are contained. I don't feel too strongly about this, so I would not complain if floats were treated as continuous. > And the next question is whether, for numeric(6,2), [1,2.99] UNION > [3,5] should be [1,5]. [ you say "yes" ] I almost agree. Unfortunately, typmod isn't really a part of the type, it just affects input/output. So, we can't really use it that way -- as Tom points out, typmod is not passed along to functions that take the value. But if it were a part of the type, then I would certainly agree. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Alvaro Herrera on 14 Dec 2009 13:31 Jeff Davis wrote: > So there are certainly some user-visible API differences between the > two, and I don't think those differences can be hidden. ISTM you are saying we should have two types of range types. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 14 Dec 2009 13:32 Jeff Davis <pgsql(a)j-davis.com> writes: > On Mon, 2009-12-14 at 11:25 -0500, Tom Lane wrote: >> This statement seems to me to demonstrate that you don't actually >> understand the concept of open and closed ranges. > Of course you can still define the obvious "contains" and "overlaps" > operators for a continuous range. But there are specific differences in > the API between a discrete range and a continuous range, which is what > Scott was talking about. Well, if the intention is to invent two different kinds of ranges, with different operators, for continuous and discrete data types, then fine. But the original post suggested no such thing, and provided (unworkable) examples suggesting that the intent was to force every type to be treated as discrete whether that makes any sense or not. The main question I would have is how to tell whether the underlying type is really discrete. If we allow people to define things like "range over float4 with 0.000001 step", then somebody will try to do it --- and file bugs when it doesn't work sanely. I don't think there is anything in our API for user-defined types that really tells you whether it's an exact or approximate type. (Also, stuff like strings simply doesn't have any sane concept of a unique next or previous value. I think the number of types that are really discrete in this sense is very small, like maybe just ints and enums.) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Scott Bailey on 14 Dec 2009 13:54 Tom Lane wrote: > Scott Bailey <artacus(a)comcast.net> writes: >> Because intervals (mathematical not SQL) can be open or closed at each >> end point we need to know what the next an previous value would be at >> the specified granularity. And while you can do some operations without >> knowing this, there are many you can't. For instance you could not tell >> whether two [] or () ranges were adjacent, or be able to coalesce an >> array of ranges. > > This statement seems to me to demonstrate that you don't actually > understand the concept of open and closed ranges. It has nothing > whatsoever to do with assuming that the data type is discrete; > these concepts are perfectly well defined for the reals, for example. > What it is about is whether the inclusion conditions are "< bound" > or "<= bound". I won't address how you draw your conclusions here. But I find it 'interesting' that you assume that I don't know what I'm talking about rather than assume you don't fully understand what I'm talking about. Anyhow. For any given range you may be 4 combinations of values. Either the first value included in the range '[' or the last value preceding the start of the range '('; and the last value included in the range ']' or the first value following the end of the range ')'. We aren't going to store all four data points so we need to normalize into the most common form, a half-open interval [) and store just those two values. The first value included in the range and the first value after the end of our range. So lets say you are using a numeric range to model the high and low values of stocks trading on a given day. Now imagine doing this with no concept of granularity. You will most likely be given a range [low, high] with inclusive end points. So how do you convert that to a closed-open interval so you can store it? Is 20.42000000000000000001 the next value after 20.42? Probably not. You are going to want to define 0.01 as the granularity for this (either type or column) so that 20.43 is. Or again are the ranges [14.0, 22.0] and [22.1, 29.0] adjacent? Maybe, maybe not. There is no way to tell w/o knowing the granularity. Perhaps the granularity is 0.000000001 and there are a billion values that are not included. Or perhaps the granularity is 0.1 and the are adjacent. Scott -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Scott Bailey on 14 Dec 2009 14:10
Tom Lane wrote: > Scott Bailey <artacus(a)comcast.net> writes: >> So basically I have an anyrange pseudo type with the functions prev, >> next, last, etc defined. So instead of hard coding range types, we would >> allow the user to define their own range types. Basically if we are able >> to determine the previous and next values of the base types we'd be able >> to define a range type. I'm envisioning in a manner much like defining >> an enum type. > > I think array types, not enums, would be a better model. I was referring to the syntax for how the user actually defined an enum not about it's implementation. Basically what I was hoping to get out of this thread was whether it was better to allow the user to define their own range types by specifying the base type and possibly the granularity and default inclusiveness of the end points, or if we should just provide the types like period and intrange? Scott -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |