Thread: SQL:2011 Valid-Time Support
Hello All,
I am sure this has been discussed somewhere but I have not found anything specific in the archives.
Has there been or is there any current effort to implement SQL:2011 valid-time support in Postgres? I understand that there has been some efforts to implement some valid-time support but now that there is a published standard I think that it would be valuable to try to obtain some level of compatibility. Is this something the community has decided against?
Ultimately I am wondering if this is something that would be worth me spending my time on.
Thanks
Paul
Paul Howells <paul.steven.howells@gmail.com> writes: > Has there been or is there any current effort to implement SQL:2011 > valid-time support in Postgres? Searching the archives, I can only find "valid-time" appearing in these threads related to temporal query processing: https://www.postgresql.org/message-id/flat/200702100020.28893.wt%40penguintechs.org https://www.postgresql.org/message-id/flat/CALNdv1h7TUP24Nro53KecvWB2kwA67p%2BPByDuP6_1GeESTFgSA%40mail.gmail.com but perhaps that's talking about something different? Neither of those threads were discussing features that are already in the standard, as far as I could see. Anyway, if it's in the standard, then at least in principle we're open to it. There'd probably be some questions about the amount of added complexity and whether the feature is worth supporting. I'd suggest trying to get some community buy-in by circulating a design document on -hackers before you write any code. regards, tom lane
On 5/8/18 11:31, Tom Lane wrote: > Paul Howells <paul.steven.howells@gmail.com> writes: >> Has there been or is there any current effort to implement SQL:2011 >> valid-time support in Postgres? > > Searching the archives, I can only find "valid-time" appearing in these > threads related to temporal query processing: > > https://www.postgresql.org/message-id/flat/200702100020.28893.wt%40penguintechs.org That looks like a pre-standardization variant of the same idea. > https://www.postgresql.org/message-id/flat/CALNdv1h7TUP24Nro53KecvWB2kwA67p%2BPByDuP6_1GeESTFgSA%40mail.gmail.com I think those are operators that work on top of having the valid-time available, but don't do anything about making that time available. > Anyway, if it's in the standard, then at least in principle we're open > to it. There'd probably be some questions about the amount of added > complexity and whether the feature is worth supporting. I'd suggest > trying to get some community buy-in by circulating a design document > on -hackers before you write any code. I think there is some interest, so it's worth proceeding like that. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Tue, May 8, 2018 at 7:13 PM, Peter Eisentraut <peter.eisentraut@2ndquadrant.com> wrote: > On 5/8/18 11:31, Tom Lane wrote: >> Paul Howells <paul.steven.howells@gmail.com> writes: >>> Has there been or is there any current effort to implement SQL:2011 >>> valid-time support in Postgres? >> >> Searching the archives, I can only find "valid-time" appearing in these >> threads related to temporal query processing: >> >> https://www.postgresql.org/message-id/flat/CALNdv1h7TUP24Nro53KecvWB2kwA67p%2BPByDuP6_1GeESTFgSA%40mail.gmail.com > > I think those are operators that work on top of having the valid-time > available, but don't do anything about making that time available. I think it is the reverse actually: these operators are more general-purpose than valid time and could be used internally to implement it, much like Postgres uses constraint triggers to implement foreign keys. You could even leave them unexposed to the user (as suggested in their papers), although personally I would love to have access to them. Anyway, I think supporting valid-time tables (one way or the other) in Postgres would be a tremendous addition. I'm traveling and can't write much more at the moment, but I'll try to reply more fully in a week or two. I'm happy to offer to help with this. My only Postgres contribution so far was a tiny thing about uuids and btree_gist, but I've spent a lot of time reading the source code to build various extensions, and temporal databases has been an interest of mine for several years. Yours, Paul
On Fri, May 11, 2018 at 4:48 AM, Paul A Jungwirth <pj@illuminatedcomputing.com> wrote: > I'm traveling and can't write much more at the moment, but I'll try to > reply more fully in a week or two. Sorry it took awhile to continue this discussion! If people are interested in implementing temporal features in Postgres, I wrote an annotated bibliography here about the research: https://illuminatedcomputing.com/posts/2017/12/temporal-databases-bibliography/ A shorter version, focused on the patches offered by Dingös et al, is here: https://www.mail-archive.com/pgsql-hackers@postgresql.org/msg324631.html There is a lot of history, and temporal features tend to be pretty drastic and far-reaching (e.g. a "temporal primary key" is more like an exclusion constraint). It's really worth reading a bit from the research. The Date/Darwen/Lorentzos book is very good, although I'd take some of it with a grain of salt (e.g. no nulls). I think SQL:2011 is so limited & over-specific, it would be great to offer it on top of some more generalized infrastructure. The Dingös work is great, so that's what I'd like to see. With their new operators, it would be very easy to build the stuff from the standard. This paper is only 10.5 pages (not counting bibliography & appendix) and easy to understand: https://files.ifi.uzh.ch/boehlen/Papers/modf174-dignoes.pdf Paul