Range types
in [PgSql]
|
Prev: Winflex
Next: [HACKERS] Fast or immediate shutdown
From: Josh Berkus on 15 Dec 2009 18:51 On 12/15/09 2:40 PM, Scott Bailey wrote: >> If this were an amazingly >> short and beautiful piece of code, it might support your argument, >> but it's neither. > > Well we can't all be arrogant brainiacs. Tom, Scott, Let's keep it constructive here. Thanks! --Josh -- 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: decibel on 15 Dec 2009 19:03 On Dec 15, 2009, at 5:40 PM, Jeff Davis wrote: > If you think I'm proposing that we drop inclusivity/exclusivity before > telling the application, that's not what I'm proposing at all. I'm > proposing that, at least in some circumstances, it's important to be > able to display the same value in different formats -- e.g. [1, 3) or > [1, 2], depending on what the application expects. Similar to a timezone > adjustment. I think it would help the discussion if you could provide some real examples. I suspect you're thinking of things like scheduling apps, where it's important to be able to do things like "what's the next available time slot?". There are probably ways to make that kind of thing easier without resorting to discrete time. > [1] "Temporal Data and the Relational Model" by C.J. Date, et al., uses > discrete time throughout the entire book, aside from a brief discussion > at the beginning. I find myself wondering if they were influenced by the technology available at the time... -- Jim C. Nasby, Database Architect jim(a)nasby.net 512.569.9461 (cell) http://jim.nasby.net -- 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: decibel on 15 Dec 2009 19:06 On Dec 15, 2009, at 11:34 AM, Jeff Davis wrote: > On Tue, 2009-12-15 at 10:19 -0500, Tom Lane wrote: >> I'm not sure that anyone has argued that. I did suggest that there >> might be a small list of types for which we should provide discrete >> behavior (ie, with next/previous functions) and the rest could have >> continuous behavior (without that assumption). But I quite agree >> that we want both types of ranges. > > It seems like we're moving toward treating TIMESTAMP as continuous. > > If I'm correct, continuous ranges always need two extra bits of storage > for the exclusivity. But for timestamps, that means 16 bytes (2 x 8-byte > timestamp) turns into 17 bytes, which is really more like 20 or 24 bytes > with alignment. > > Considering that these are likely to be used for audit or history > tables, 8 bytes of waste (50%) seems excessive -- especially when > treating them as discrete seems to work pretty well, at least for the > int64 timestamps. > > Ideas? Now that varlena's don't have an enormous fixed overhead, perhaps it's worth looking at using them. Obviously some operations would be slower, but for your stated examples of auditing and history, I suspect that you're not going to notice the overhead that much. I'm not sure if the best way to do this would be to support a varlena timestamp or to take fixed-size timestamps and convert them into varlena periods. -- Jim C. Nasby, Database Architect jim(a)nasby.net 512.569.9461 (cell) http://jim.nasby.net -- 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: Christophe Pettus on 15 Dec 2009 19:08 On Dec 15, 2009, at 3:40 PM, Jeff Davis wrote: > Based on the premise that timestamps are a continuous value and the > granularity/precision is entirely an implementation detail, you're > right. But I disagree with the premise, at least in some cases that I > think are worthwhile. The argument is, in essence: DECIMAL is continuous. DECIMAL(10,3) is discrete. timestamptz in general is a continuous value (unless we're talking Planck times :) ). There is no way for us to guarantee that next(timestamptz) will have the same value across all platforms; its epsilon is platform dependent. However, if we specify a scale on timestamptz, it becomes much more useful. Just making up a syntax, if we had timestamptz(milliseconds), then it's discrete and we know what next(timestamptz(milliseconds)) is. But in the current implementation, the only way I can see making that work is if we specify a scale for timestamptz, and that strikes me as a big change to its semantics. -- -- Christophe Pettus xof(a)thebuild.com -- 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 15 Dec 2009 19:25
Tom Lane wrote: > I wrote: >> The proposed problem is certainly soluble without any assumptions >> of discreteness. > > To be concrete, I think it could be approached like this: > > Assume the datatype provides a built-in function > > period_except(p1 period, p2 period) returns setof period > > which can return zero, one, or two rows depending on the inputs: > > no rows if p1 is completely contained in p2 > > one row if p1 partially overlaps p2, for example: > > [1,4] except [3,5] returns [1,3) > [4,6] except [1,5) returns [5,6] > > two rows if p1 properly contains p2, for example > > [1,10] except [4,5] returns [1,4) and (5,10] > [1,10] except [9,10) returns [1,9) and [10,10] > > and of course just p1 if p1 and p2 don't overlap at all. > > Given such a function it's a simple matter of successively removing each > element of p2[] from the set representing the current members of p1[]. > The way that I'd find most natural to code that is a loop, along the > lines of > > 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. > > As this example makes clear, when dealing with continuous intervals you > *must* admit both open and closed intervals, else you don't have a way > to represent the results of "except". Maybe part of the failure to > communicate here arises from your desire to try to avoid supporting both > kinds of intervals. But I think you really have to do it if you want to > deal with data that hasn't got any natural granularity. > > regards, tom lane Alright well I'm going to calm down a bit and take a step back. Perhaps I'm just too close to the issue and not thinking outside of the box that I've built. Let me see if I can make everything work rather than arguing why it wont. 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 |