1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Rep Power

    Oracle Flashback in Postgres

    hi everyone,
    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?

  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Rep Power
    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.

IMN logo majestic logo threadwatch logo seochat tools logo