Proposal - temporal contrib module
in [PgSql]
|
Prev: [HACKERS] Proposal - temporal contrib module
Next: libpq - extending PQexecParams/PQexecPrepared to specify resultFormat for individual result columns
From: Pavel Stehule on 29 Oct 2009 03:48 2009/10/29 Scott Bailey <artacus(a)comcast.net>: > I would like to add a temporal contrib module. The most important piece > would be adding a period data type and some support functions. Jeff Davis > and I both have temporal projects on pgFoundry, and we've been collaborating > for a while. But there are some areas we'd like to get some advice on. > I thing, so it is very good idea. Temporar operation is very common task. Regards Pavel Stehule > Disk format - A period can be represented as [closed-closed], (open-open), > [closed-open) or (open-closed] intervals. Right now we convert these to the > most common form, closed-open and store as two timestamptz's. > > Nulls - A common use case for periods is for modeling valid time. Often the > end point is not known. Â For instance, you know when an employee has been > hired but the termination time typically wouldn't be known ahead of time. We > can either represent these with a null end time or with infinity. But I'm > not sure how to deal with them. Obviously we can test for containment and > overlap. But what about length or set operations? > > Non-contiguous Sets - A period defines a contiguous set of time. But many > times we need to work with non-contiguous sets (work shifts in a week, bus > schedules, etc). Â Right now, I'm using period arrays. But period arrays can > contain overlapping and adjacent periods. And we have no way to indicate > that a period array has been coalesced into a non-contiguous set. And what > indexing strategies could be used with non-contiguous sets? > > Temporal Keys - We need two types of temporal keys. A primary key, exclusion > type prevents overlap so someone isn't at two places at the same time. And a > foreign key, inclusion type so we can check that the valid time of a child > is contained with in the valid time of the parent. Jeff is working on the > former, but there is no easy way to do the latter. > > > There is actually a lot of theory out there but very few implementations. > Although not an official standard, we try to follow the TSQL2 spec pretty > closely. Further reading: > > Developing Time-Oriented Database Applications - Snodgrass > http://www.cs.arizona.edu/~rts/tdbbook.pdf > > TSQL2 spec ftp://ftp.cs.arizona.edu/tsql/tsql2/spec.pdf > > Temporal Data and the Relational Model - Date et al > http://books.google.com/books?isbn=1558608559 > > Dozens of publications > http://timecenter.cs.aau.dk/pub.htm > > > Regards, > > Scott Bailey > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- 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: Heikki Linnakangas on 29 Oct 2009 04:54 Scott Bailey wrote: > I would like to add a temporal contrib module. The most important piece > would be adding a period data type and some support functions. Jeff > Davis and I both have temporal projects on pgFoundry, and we've been > collaborating for a while. I presume you're going to need some backend support and possibly new syntax for some of the operations, right? That seems more urgent to discuss than the possible inclusion into contrib. I'm very pleased to see people working on temporal issues, BTW! I used to work on a database that did a lot of temporal operations, but the DBMS didn't have any temporal data types or operations so we had to use a lot of triggers etc. to achieve that, and it didn't perform well. > Nulls - A common use case for periods is for modeling valid time. Often > the end point is not known. For instance, you know when an employee has > been hired but the termination time typically wouldn't be known ahead of > time. We can either represent these with a null end time or with > infinity. But I'm not sure how to deal with them. Obviously we can test > for containment and overlap. But what about length or set operations? Hmm. Infinity feels like a better match. The behavior of length and set operations falls out of that naturally. For example, length of a period with an infinite beginning or end is infinite. For set operations, for example the intersection of [123, infinity] and [100, 160] would be [123, 160]. > Non-contiguous Sets - A period defines a contiguous set of time. But > many times we need to work with non-contiguous sets (work shifts in a > week, bus schedules, etc). Right now, I'm using period arrays. But > period arrays can contain overlapping and adjacent periods. And we have > no way to indicate that a period array has been coalesced into a > non-contiguous set. And what indexing strategies could be used with > non-contiguous sets? I'd stick to your current definition that a period is a contiguous set of time. A non-contiguous set consists of multiple contiguous periods, so it can be represented as multiple rows in a table. > Temporal Keys - We need two types of temporal keys. A primary key, > exclusion type prevents overlap so someone isn't at two places at the > same time. And a foreign key, inclusion type so we can check that the > valid time of a child is contained with in the valid time of the parent. > Jeff is working on the former, but there is no easy way to do the latter. I'm very excited about this. Foreign keys don't seem that hard, you'll need foreign key triggers like we have today, but check for "within" instead of "equal". > Temporal Data and the Relational Model - Date et al > http://books.google.com/books?isbn=1558608559 +1 for the approach in this book. I'm not familiar enough with the TSQL2 spec to say whether it follows it. It should also be kept in mind that although this class of problems are generally thought of as temporal issues, IOW dealing with time, the same approach works with ranges of integers or any other datatype with a well-defined sort order. It would be nice if the temporal data type would allow that too. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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: Richard Huxton on 29 Oct 2009 05:37 Heikki Linnakangas wrote: > Scott Bailey wrote: >> I would like to add a temporal contrib module. > I'm very pleased to see people working on temporal issues, BTW! Me too - common use-case and difficult to handle without the right types/operators. >> Nulls - A common use case for periods is for modeling valid time. Often >> the end point is not known. For instance, you know when an employee has >> been hired but the termination time typically wouldn't be known ahead of >> time. We can either represent these with a null end time or with >> infinity. But I'm not sure how to deal with them. Obviously we can test >> for containment and overlap. But what about length or set operations? > > Hmm. Infinity feels like a better match. The behavior of length and set > operations falls out of that naturally. For example, length of a period > with an infinite beginning or end is infinite. For set operations, for > example the intersection of [123, infinity] and [100, 160] would be > [123, 160]. There are cases where one time is genuinely unknown, and there we need a null. For the "until further notice" scenarios, infinity seems the sensible choice. Where a null is present length is clearly null, and sets I guess should propagate the nulls. [123,null] intersecting [100,160] should be [123,null]. That's assuming we've got a guarantee that from<=to for all periods. >> Temporal Keys - We need two types of temporal keys. A primary key, >> exclusion type prevents overlap so someone isn't at two places at the >> same time. You're going to upset a lot of managers if they can't do that ;-) -- Richard Huxton Archonet Ltd -- 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 29 Oct 2009 14:17 On Thu, 2009-10-29 at 00:31 -0700, Scott Bailey wrote: > Nulls - A common use case for periods is for modeling valid time. Often > the end point is not known. For instance, you know when an employee has > been hired but the termination time typically wouldn't be known ahead of > time. We can either represent these with a null end time or with > infinity. But I'm not sure how to deal with them. Obviously we can test > for containment and overlap. But what about length or set operations? I think we should allow the database designer flexibility here. For instance, there are good arguments for using separate relations for things with a known begin time and no known end, and things with a definite begin and end time. However, infinity also makes sense, particularly in cases where something that is true can never again be false. NULL support is a little stranger. We can allow it by extending the data representation, but the semantics might not match what people expect from NULL. Should it be treated like a NULL in an array, or a NULL in a record value, or what? If we allow NULL on one side of a period, that may require some backend support, depending on the semantics. My feeling right now is to not provide a way for one side of the period to be NULL, but if we come up with clear enough semantics maybe it's useful. I tend to think it would cause more confusion than anything. For any kind of built-in audit log functionality (transaction-time based), I don't see any utility for NULL. > Temporal Keys - We need two types of temporal keys. A primary key, > exclusion type prevents overlap so someone isn't at two places at the > same time. And a foreign key, inclusion type so we can check that the > valid time of a child is contained with in the valid time of the parent. > Jeff is working on the former, but there is no easy way to do the latter. I believe temporal foreign keys can be implemented the same way foreign keys are now (except with "contained by" rather than "equals"). We should provide some support to make that easier, but I don't think that's a major issue. 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 29 Oct 2009 14:25
On Thu, 2009-10-29 at 09:37 +0000, Richard Huxton wrote: > There are cases where one time is genuinely unknown, and there we need > a null. The semantics of a period with one side NULL require a more clear definition. I don't personally see a lot of utility in trying to support NULL semantics, but if we want to support it, it needs to be clearly defined. Does TSQL-2 offer any guidance here? > That's assuming we've got a guarantee > that from<=to for all periods. Of course. Except that means that a NULL on one side of a period is a little less unknown than other kinds of NULLs ;) 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 |