Range types
in [PgSql]
|
Prev: Winflex
Next: [HACKERS] Fast or immediate shutdown
From: tomas on 16 Dec 2009 08:29 -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Tue, Dec 15, 2009 at 11:49:19AM -0800, David Fetter wrote: > On Tue, Dec 15, 2009 at 11:31:05AM -0800, Scott Bailey wrote: > > Jeff Davis wrote: > > >On Tue, 2009-12-15 at 10:19 -0500, Tom Lane wrote: > > > > Would it be OK if we handled float timestamp ranges as continuous > > and int64 timestamps discrete? > > That sounds like a recipe for disaster. Whatever timestamp ranges > are, float and int64 should be treated the same way so as not to get > "surprises" due to implementation details. This alone would practically preclude discrete -- int and float would behave quite differently (float's "granules" growing towards the edges or having to choose a bigger granule for float than for int in the first place). [...] > FWIW, I think it would be a good idea to treat timestamps as > continuous in all cases. This would come as a corollary from the above Regards - -- tomás -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFLKODXBcgs9XrR2kYRAlpLAJ9nO5f0SHwX8A4CjTn6c/xyZdim1ACdGHTq Fwn5ygKvCDFGadufOYPGrfA= =ivCP -----END PGP SIGNATURE----- -- 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 09:55 Dimitri Fontaine <dfontaine(a)hi-media.com> writes: > Tom Lane <tgl(a)sss.pgh.pa.us> writes: >> foreach p2_member in unnest(p2) loop >> p1 := array(select period_except(p1_member, p2_member) >> from unnest(p1) p1_member); >> end loop; >> >> But maybe it can be done in a single SQL command. > Yeah, as soon as you have LATERAL, I think. Without it there's no way to > compose SRF in SQL, AFAIK. Hm, how would you do it with LATERAL? The problem is not so much composition as the need for a variable number of rounds of composition. 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 12:31 On Sun, 2009-12-13 at 23:49 -0800, Scott Bailey wrote: > 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. After an off-list discussion with Scott, I think there may be a solution here that works for everyone if we don't try so hard to unify the implementation of discrete and continuous ranges. The API should be very similar, of course, but the implementation doesn't need to be. Continuous ranges absolutely require the following information: start, end, and inclusivity information. But discrete ranges can instead be stored by counting the number of granules from the start point. For instance, it could be stored as: start, num_granules. That has a lot of benefits for discrete ranges of time. First of all, it allows the algebra to work reasonably well for the "days" and "months" part of the interval, so we can allow a granule of 1 day/week/month/year for a timestamp range. For output of the range, we can then just multiply the granule by the number of granules, and add that to the start time; thus avoiding the "incremental addition" problem with date math. I think this works reasonably well for timestamp/date ranges -- let me know if there is a problem here (aside from timestamptz, which I address below). Secondly, in the case of a timestamp range, we can use 7 bytes for storing the number of granules rather than another full 8-byte timestamp, leaving one byte for flags to represent NULL boundaries, infinite boundaries, etc. For timestamps that would still mean that an interval could be 2000 years long with '1 microsecond' granularity. For dates, 3 bytes is sufficient for a date range 45000 years long with granules of '1 day'. That means that we can get back down to a 16 byte representation for timestamp ranges, or 8 byte representation for date ranges. There are a few details, like infinite ranges, but those can be pretty easily solved with flags as well. There's one problem, and that's for timestamptz ranges with intervals that include days and months. Timezone adjustments are just not well-defined for that kind of granule (nor would it be particularly useful even if it magically worked), so this would have to be blocked somehow. I think that's a special case, and we could provide the user with a nice error message telling the user to use a date or timestamp range instead. So, the idea is to default to a continuous range type, but if the user supplies a granule, prior and next functions, and other necessary details, then it becomes a discrete range type. * continuous ranges can still have everything that everyone wants, including flags to indicate special values. * discrete range granule is specified explicitly, so it's not an "implementation detail" * discrete ranges can have a compact representation * discrete ranges would still have room for flags to indicate special values Comments? 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: Robert Haas on 16 Dec 2009 12:42 On Wed, Dec 16, 2009 at 12:31 PM, Jeff Davis <pgsql(a)j-davis.com> wrote: > There's one problem, and that's for timestamptz ranges with intervals > that include days and months. Timezone adjustments are just not > well-defined for that kind of granule (nor would it be particularly > useful even if it magically worked), so this would have to be blocked > somehow. I think that's a special case, and we could provide the user > with a nice error message telling the user to use a date or timestamp > range instead. This seems like a fairly special-purpose type. You'd be targeting it at people who are very concerned with storing large numbers of these (so they really care about space consumption) but for some reason don't need to mix days and months (actually, the current interval representation stores days, months, and seconds separately). I certainly think this might be useful to some people but it doesn't really sounds like a general range type facility, since it seems to involve some hacks that are fairly datatype-specific. ....Robert -- 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 12:50
Jeff Davis <pgsql(a)j-davis.com> writes: > [ hacky special-case representation for discrete timestamp ranges ] I'm still not exactly clear on what the use-case is for discrete timestamp ranges, and I wonder how many people are going to be happy with a representation that can't handle a range that's open-ended on the left. > So, the idea is to default to a continuous range type, but if the user > supplies a granule, prior and next functions, and other necessary > details, then it becomes a discrete range type. Huh? You're not going to be able to have a special case data representation for one or two data types at the same time as you have a function-based datatype-independent concept of a parameterized range type. Well, maybe you could have special code paths for just date and timestamp but it'd be horrid. More importantly, the notion of a representation granule is still 100% wishful thinking for any inexact-representation datatype, which is going to be a severe crimp in getting this accepted for timestamp, let alone defining it in a way that would allow users to try to apply it to floats. Float timestamps might not be the default case anymore but they are still supported. I think you should let go of the feeling that you have to shave bytes off the storage format. You're creating a whole lot of work for yourself and a whole lot of user-visible corner cases in return for what ultimately isn't much. 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 |