Temporal features in PostgreSQL

Hi Vlad,

I'm also interested in this topic and work on system-time temporal
extension. Here I wrote down design of my solution few months ago
https://wiki.postgresql.org/wiki/SQL2011Temporal. The idea is basically the
same as in your solution with some minor differences. For example:
- I use after triggers to store old versions of rows into historical
table, so the row is "archived" only if modification is actualy executed.
- I don't need to deal with update conflicts, because I use
clock_timestamp() instead of current_timestamp.
- Inheritence relation between historical and current table allows to
easily select whole history of rows.

Although my solution needs changes in parser to stick with SQL 2011
standard, maybe you can find something that can help you.

Regards,
Miro

2012/12/25 Vlad Arkhipov <arhipov@dc.baikal.ru>

On 02/04/2013 07:40 PM, Miroslav Šimulčík wrote:

Hi Vlad,

I'm also interested in this topic and work on system-time temporal
extension. Here I wrote down design of my solution few months ago
https://wiki.postgresql.org/wiki/SQL2011Temporal. The idea is
basically the same as in your solution with some minor differences.
For example:
- I use after triggers to store old versions of rows into
historical table, so the row is "archived" only if modification is
actualy executed.

Then other BEFORE triggers are not able to see what time is going to be
inserted into the table. I considered using two triggers, BEFORE trigger
for setting the period and AFTER trigger for archiving rows into the
history table, but did not find any use cases when it can be better than
just a simple BEFORE trigger.

- I don't need to deal with update conflicts, because I use
clock_timestamp() instead of current_timestamp.

You can still come across a conflict even with clock_timestamp(). What
if clocks go back during the time synchronization? Even if you have
absolutely precious clocks, there are may be clock skew on different
CPUs, low system clock time resolution, etc.

Although my solution needs changes in parser to stick with SQL 2011
standard, maybe you can find something that can help you.

I believe that SQL-2011 standard temporal features are not too abstract
for PostgreSQL to be implemented as a core feature. They have only two
temporal periods: application period (which is controlled by
application/user) and system time (which is controlled by
system/database, but you cannot specify *how* the system control it),
they does not use a special type for storing periods (which is
unefficient), they are tied to DATE/TIMESTAMP types (what if you need to
store revision numbers instead of time?)

Hello,

I'm also interested in this topic.

I'm also interested in this topic and work on system-time temporal
extension. Here I wrote down design of my solution few months ago
https://wiki.postgresql.org/wiki/SQL2011Temporal. The idea is
basically the same as in your solution with some minor differences.

I've added a requirement in the system here: the table to be versioned
must have a PK (I dislike _entry_id usage but this sounds good othwise).
I then define a "EXCLUDE WITH GIST (pk with =, sys_period with &&)", thus
getting expected UNIQUEness also in the history.

Vlad, is your source code in a public versionning system (github, bucket, etc) ?
It will ease the process to participate to your extension...

--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation

Hi,

On 02/15/2013 10:46 PM, Cédric Villemain wrote:

Hello,

I'm also interested in this topic.

I'm also interested in this topic and work on system-time temporal

extension. Here I wrote down design of my solution few months ago

https://wiki.postgresql.org/wiki/SQL2011Temporal. The idea is

basically the same as in your solution with some minor differences.

I've added a requirement in the system here: the table to be versioned

must have a PK (I dislike _entry_id usage but this sounds good othwise).

I then define a "EXCLUDE WITH GIST (pk with =, sys_period with &&)", thus

getting expected UNIQUEness also in the history.

I use similar constraints for application-time period tables but not for
system versioned. Because they are automatically controlled by a
trigger, there should be no need for additional integrity checks. If you
want to speed up queries against historical data, you can create GIST
index or an exclusion constraint.

Vlad, is your source code in a public versionning system (github,
bucket, etc) ?

It will ease the process to participate to your extension...

Yes, I uploaded it on github
https://github.com/arkhipov/temporal_tables/

The extension is also available on PGXN
http://pgxn.org/dist/temporal_tables/1.0.0/

On 2/13/13 10:06 PM, Vlad Arkhipov wrote:

- I don't need to deal with update conflicts, because I use clock_timestamp() instead of current_timestamp.

You can still come across a conflict even with clock_timestamp(). What if clocks go back during the time synchronization? Even if you have absolutely precious clocks, there are may be clock skew on different CPUs, low system clock time resolution, etc.

Sorry for the late reply, catching up on email...

If you want to track the history of something, "measured time" is absolutely NOT the way to do it. I use the term "measured time" to differentiate from the real-world concept of time that is forever flowing forward from one instant to the next. The problem with measured time is that it's incredibly easy to screw up. Clocks out of sync, clocks running backwards, etc. Heck, it's not even clear what time you should actually use: transaction start, wallclock, or transaction end.

For any kind of history tracking to actually be robust you have no choice but to link one history record to another so that you can actually walk down a chain. Of course you might want to capture a timestamp as part of your history metadata, but you better be ready to deal with a history record with a timestamp that is *earlier* than the prior history record.

BTW, we've been working on a generic history implementation at work; hopefully we'll be able to release it relatively soon.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

What's the current status of this topic?
Has someone worked on temporal tables for PostgreSQL since 2012 ?

I'm giving a presentation on Fosdem later this month in Brussels, on the
topic of temporal tables, and would like to give all possibly relevant
information to the audience!

-- Peter Vanroose,
Leuven, Belgium.

--
View this message in context: http://postgresql.nabble.com/Temporal-features-in-PostgreSQL-tp5737881p5834312.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, Jan 16, 2015 at 09:58:22AM -0700, peter@vanroose.be wrote:

What's the current status of this topic?
Has someone worked on temporal tables for PostgreSQL since 2012 ?

I'm giving a presentation on Fosdem later this month in Brussels, on the
topic of temporal tables, and would like to give all possibly relevant
information to the audience!

I have not heard of anyone working on this.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers