December 26th, 2013, 11:36 AM
Oracle Flashback in Postgres
i'm an italian university student and in my thesis I'd like to develop Oracle flashback in PostgreSQL.
I'd like to ask you if there is already something similar (I've heard about the time travel function in PostgreSQL 8 but it has been dropped) or if Postgres 9.4 is going to have something similar.
Do u have any suggestions or tips for me?
December 27th, 2013, 05:49 AM
I am not aware of any current projects for this, but there are several popular techniques to get similar functionality.
The simplest is to add a trigger to the table in question and copy the data from OLD into a history table using the HSTORE format. This is easy to setup and mainten because changes in the original table have no consequences for the trigger or the history table. The downside is that going back in time requires that you query the hstore, you cannot use the regular queries toe get "the answer that you would have gotten yesterday".
Another method is to add a date_start and date_end that signify the dat at which a record became "the current state" and when it became "outdated". Then you can add a view to supply only the records that have no date_end, which are the current records.
Going back in time then a matter of mentioning the date in question and make sure you select all your refords "WHERE target_date BETWEEN date_start AND date_end"
THis has serious implications for performance and storage, but it does allow you to get yesterday's answers.