Range types
in [PgSql]
|
Prev: Winflex
Next: [HACKERS] Fast or immediate shutdown
From: Martijn van Oosterhout on 16 Dec 2009 16:17 On Wed, Dec 16, 2009 at 03:57:44PM -0500, Tom Lane wrote: > Jeff Davis <pgsql(a)j-davis.com> writes: > > I still have not seen an answer to the problem of changing the > > representation of a continuous range. If you have the continuous range > > [5, 10], you're pretty much stuck with that representation, even if the > > application is expecting things in the form [ ). > > That is not our problem. It's the application's problem if it can't > handle the concept. You might as well be complaining that type numeric > is broken because it can represent values that will fail to fit into > float8 when some application tries to force them into that form. However, it does seem reasonable to allow people to restrict, either by typmod or a check constraint the kinds of values that can be stored in a particular column. Then an application can decide which way they want their intervals to work and have the database enforce it. (Intermediate values may become a different kind, just as long as the result being stored it the right kind.) Have a nice day, -- Martijn van Oosterhout <kleptog(a)svana.org> http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines.
From: Jeff Davis on 16 Dec 2009 16:32 On Wed, 2009-12-16 at 15:46 -0500, Tom Lane wrote: > > Huh? We're miscommunicating somewhere. > > Yeah, apparently. By open-ended I meant -infinity left bound, or null > left bound if you prefer. Not sure if there's a better term. But my proposal allowed both of those things with various flag settings (because it has a flag byte in the latest proposal). I said so explicitly. 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: Tom Lane on 16 Dec 2009 16:37 Martijn van Oosterhout <kleptog(a)svana.org> writes: > However, it does seem reasonable to allow people to restrict, either by > typmod or a check constraint the kinds of values that can be stored in > a particular column. Then an application can decide which way they want > their intervals to work and have the database enforce it. Sure --- the range datatype should absolutely provide inquiry functions that let you determine all the properties of a range, so something like "CHECK (is_open_on_right(col))" would work for that. I'm of the opinion that we must not usurp typmod for range behavior --- the right thing is to pass that through to the contained type, just as we do with arrays. (Note that a range over timestamp(0) would eliminate at least some of the platform dependencies we've been arguing about. I'm still quite dubious that "next timestamp" is anything except evidence that you've misformulated your problem, though.) 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: Jeff Davis on 16 Dec 2009 17:14 On Wed, 2009-12-16 at 15:57 -0500, Tom Lane wrote: > Making it explicit doesn't fix the fact that you can't rely on the > arithmetic to be exact. Can't rely on what arithmetic to be exact? Int64 timestamps should clearly work for granules of 1 second. If the administrator can choose a timestamp format that can't accurately represent whole seconds, that's not an argument that modeling based on whole seconds is worthless. We can restrict float timestamps for ranges as a special case, if we're that worried about people misusing them. > > I still have not seen an answer to the problem of changing the > > representation of a continuous range. If you have the continuous range > > [5, 10], you're pretty much stuck with that representation, even if the > > application is expecting things in the form [ ). > > That is not our problem. It's the application's problem if it can't > handle the concept. You might as well be complaining that type numeric > is broken because it can represent values that will fail to fit into > float8 when some application tries to force them into that form. ....except that we support float8. So if applications like to work float8 float8, we let them. You're arguing that we should not support discrete time ranges (or even allow such a type to be defined by a superuser) even though applications and users may happen to model time that way. Who are we to argue that all of those people are so wrong that we won't even support their type? Especially when they may have just finished a couple books on the subject[1][2] which told them to model it that way? > > And to further make the case for allowing user-defined discrete ranges, > > what about ip4r? > > What about it? I don't have a problem with the concept that next() is > well defined for some datatypes. Ok, so we'll allow users to specify user-defined types for discrete ranges? How should that be specified, and how will that differ from earlier proposals? Earlier you proposed that we hard-wire the set of types that are allowed to be used for discrete ranges: http://archives.postgresql.org/pgsql-hackers/2009-12/msg01278.php Regards, Jeff Davis [1] "Temporal Data and the Relational Model" by C.J. Date, et al. [2] "Developing Time-Oriented Database Applications in SQL" by Richard Snodgrass. -- 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 16 Dec 2009 17:15
Tom Lane wrote: > Martijn van Oosterhout <kleptog(a)svana.org> writes: >> However, it does seem reasonable to allow people to restrict, either by >> typmod or a check constraint the kinds of values that can be stored in >> a particular column. Then an application can decide which way they want >> their intervals to work and have the database enforce it. > > Sure --- the range datatype should absolutely provide inquiry functions > that let you determine all the properties of a range, so something like > "CHECK (is_open_on_right(col))" would work for that. I'm of the opinion > that we must not usurp typmod for range behavior --- the right thing is > to pass that through to the contained type, just as we do with arrays. > > (Note that a range over timestamp(0) would eliminate at least some of > the platform dependencies we've been arguing about. I'm still quite > dubious that "next timestamp" is anything except evidence that you've > misformulated your problem, though.) > > regards, tom lane Well our work is based on over 15 years of temporal research (not by us) and numerous books from Snodgrass, Date and Celko; as well as partial implementations in other databases. So its not like we took a blue pill this weekend and woke up with this hair-brained idea. I understand your concern. But I think the objections are based more on implementation details with float timestamp rather than conceptually. -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |