Range types
in [PgSql]
|
Prev: Winflex
Next: [HACKERS] Fast or immediate shutdown
From: Jeff Davis on 15 Dec 2009 19:29 On Tue, 2009-12-15 at 18:06 -0600, decibel wrote: > 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. For most varvarlena types, you only get stuck with the full alignment burden if you get unlucky. In this case, we're moving from 16 bytes to 17, which really means 24 bytes with alignment. Try creating two tables: create table foo(i int8, t1 timestamp, t2 timestamp); create table bar(i int8, c "char", t1 timestamp, t2 timestamp); That extra byte there costs you 8 bytes, every time (on my machine, anyway). We're at serious risk of people saying "Ah, this temporal thing is bloated. I'll try to get by with a single timestamp and save 16 bytes per record". Or maybe "Why waste the bytes? I'll just store two timestamps". 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: Jeff Davis on 15 Dec 2009 19:58 On Tue, 2009-12-15 at 18:03 -0600, decibel wrote: > 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. I did provide some examples: http://archives.postgresql.org/pgsql-hackers/2009-12/msg01385.php I think the bottom line is that we can't expect all applications to understand all 4 inclusivity permutations. Think about how many applications you've seen with "start" and "end" entry boxes, and you have to pick up from the surrounding words what inclusivity it's expecting. If you're running two such applications at once and they share data (perhaps an entry app and a reporting app), or you're migrating from one to another, you have a problem if you can't convert the intervals to the expected format. I don't see it as "resorting to discrete time". I view discrete intervals as simpler in many ways: there exists a canonical inclusivity representation, and you can take that canonical representation and display it however you want. We should at least allow the user to specify that their range is discrete (even if only by a superuser). And the user should also be able to specify that they don't want null boundaries or extra infinity boundaries, so that they get back down to the 16-byte representation. > I find myself wondering if they were influenced by the technology > available at the time... No, it's entirely at the logical level. It's also not a very old book (2002). For another author that apparently likes to deal with discrete time, how about "Developing Time-Oriented Database Applications in SQL" by Snodgrass. It's free to download online, I believe. 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: Jeff Davis on 15 Dec 2009 20:05 On Tue, 2009-12-15 at 16:08 -0800, Christophe Pettus wrote: > 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. Not unless you compile with float timestamps. Integer timestamps are microseconds since the year 2000 (positive or negative), which is platform-independent. I'm not arguing that we shouldn't support continuous time at all (clearly, enough people in this thread seem to like it), but I do want discrete time ranges. A lot of the temporal database literature is written assuming discrete time intervals. > 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. It's already useful at the microsecond precision level. Also, the granule could be defined for the range type (as Scott suggested) rather than the timestamp itself. 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: Dimitri Fontaine on 16 Dec 2009 04:36 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. Regards, -- dim -- 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: tomas on 16 Dec 2009 08:23
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Tue, Dec 15, 2009 at 04:16:28PM +0100, Nicolas Barbier wrote: [...] > <whatever> > > and > > <same whatever as before> + the character with the lowest value in > lexicographical ordering. > > I don't think it is possible to get anything in between those two strings. Yes, that was basically Andrew's argumentation. I was taken away too much by the similarity between strings and (decimal, base N) fractions, forgetting that the last have lots of unwritten zeros at the end... But hey, 'twas nice to learn that strings aren't as boring as I first thought... Regards - -- tomás -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFLKN9cBcgs9XrR2kYRAl4LAJ9rHs/mlR3+j+79YOUtNUTCY0JOEwCZAROn WsIQoT8nCbgCOaDWraH7jVk= =vjV5 -----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 |