Hi,
I would like to propose an additional feature for Postgres to enable
time-travelling inside a transaction.

I went through the source code and i found Savepoint is already saving the
necessary information. But currently it doesn't store the CommandId.

This change, if implemented would save the command id into savepoint state,
increase the command id counter for every savepoint and finally we can issue
a command like
(One more global variable to store the current state)
Select ... as of savepoint savepoint-name.

This would reduce the requirement for Serializable transactions in some
cases and adds one more feature under the umbrella of postgres.

--
Thanks,
Gokul.
CertoSQL Project,
Allied Solution Groups.
(www.alliedgroups.com)

Search Discussions

  • Martijn van Oosterhout at Nov 2, 2007 at 9:20 am

    On Fri, Nov 02, 2007 at 02:43:44PM +0530, Gokulakannan Somasundaram wrote:
    Hi,
    I would like to propose an additional feature for Postgres to enable
    time-travelling inside a transaction. <snip>
    This would reduce the requirement for Serializable transactions in some
    cases and adds one more feature under the umbrella of postgres.
    I'm still confused. Why on earth would you want to run a query against
    an old snapshot? If you want a stable view, I'd think of opening a
    cursor or using a temporary table. And where does serializable come
    into this?

    Have a nice day,
    --
    Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
    Those who make peaceful revolution impossible will make violent revolution inevitable.
    -- John F Kennedy
  • Gokulakannan Somasundaram at Nov 2, 2007 at 10:13 am

    On 11/2/07, Martijn van Oosterhout wrote:
    On Fri, Nov 02, 2007 at 02:43:44PM +0530, Gokulakannan Somasundaram wrote:
    Hi,
    I would like to propose an additional feature for Postgres to enable
    time-travelling inside a transaction. <snip>
    This would reduce the requirement for Serializable transactions in some
    cases and adds one more feature under the umbrella of postgres.
    I'm still confused. Why on earth would you want to run a query against
    an old snapshot?

    This is a interesting question. But if situation demands, you have to.
    Suppose i need to make a decision and in some cases, i may need the data as
    of old snapshot. Currently that can be done by caching the old data,
    irrespective of whether you need it/not at the later time. If we have this
    feature, we can put that extra load on the database selectively.

    Current Scenario
    eg:
    BEGIN

    current inventory1 = select ..

    some DML operations

    get current inventory2 = select..

    if current inventory2 is < fixed size
    make some decision to purchase based on current inventory 1.
    END

    If the proposal is implemented
    BEGIN

    savepoint s1;

    some DML operations

    get current inventory2 = select ...

    if current inventory2 is < fixed size
    current inventory1 = select .. as of savepoint s1;
    END

    Do you see the difference?



    If you want a stable view, I'd think of opening a
    cursor or using a temporary table. And where does serializable come
    into this?

    You can use a stable view / temporary table, if you are going to fire a same
    query again, not for x different queries against y different tables. it is
    not advisable to take the whole database as a backup in the form of
    temporary table, since we are going to run two queries as of the same
    snapshot. Sometimes people decide on serializable transactions, since they
    need most of the select queries inside the transactions have to get fired as
    of the same snapshot and they have very few DMLs. i have run into such a
    situation personally. In those kind of situations, this extra flexibility
    helps.


    Have a nice day,
    --
    Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
    Those who make peaceful revolution impossible will make violent
    revolution inevitable.
    -- John F Kennedy
    -----BEGIN PGP SIGNATURE-----
    Version: GnuPG v1.4.1 (GNU/Linux)

    iD8DBQFHKuu9IB7bNG8LQkwRAkQUAJwP9ShvfqxzHpTU2XHxRy5/TpVtVQCgik7k
    cwDUu99nfIpydxlQHihnKXE=
    =4lO3
    -----END PGP SIGNATURE-----

    --
    Thanks,
    Gokul.
    CertoSQL Project,
    Allied Solution Group.
    (www.alliedgroups.com)
  • Jonah H. Harris at Nov 2, 2007 at 11:54 am

    On 11/2/07, Gokulakannan Somasundaram wrote:
    If the proposal is implemented
    BEGIN

    savepoint s1;

    some DML operations

    get current inventory2 = select ...

    if current inventory2 is < fixed size
    current inventory1 = select .. as of savepoint s1;
    END

    Do you see the difference?
    Yes, a completely non-standard and somewhat unusual feature. What I
    don't see is why you're wasting time pushing this frankly obscure idea
    for time-travel, "only within a transaction". Why not just go all out
    and suggest re-adding time-travel completely.

    --
    Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
    EnterpriseDB Corporation | fax: 732.331.1301
    499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
    Edison, NJ 08837 | http://www.enterprisedb.com/
  • Gokulakannan Somasundaram at Nov 2, 2007 at 12:21 pm

    On 11/2/07, Jonah H. Harris wrote:
    On 11/2/07, Gokulakannan Somasundaram wrote:
    If the proposal is implemented
    BEGIN

    savepoint s1;

    some DML operations

    get current inventory2 = select ...

    if current inventory2 is < fixed size
    current inventory1 = select .. as of savepoint s1;
    END

    Do you see the difference?
    Yes, a completely non-standard and somewhat unusual feature. What I
    don't see is why you're wasting time pushing this frankly obscure idea
    for time-travel, "only within a transaction". Why not just go all out
    and suggest re-adding time-travel completely.

    I think Simon Riggs is already working on that idea. This one is fairly easy
    to implement. I think these are some of the features only a time-stamp based
    database can implement. I think database standards were formed during the
    time, when the data consistency was provided with Lock based mechanisms. And
    moreover i have already committed on the indexes with snapshot and i am
    still waiting for its approval from hackers. If that does go through, then i
    need to work on the reverse mapping hash tables, which is really a long
    task. So i may not be able to take up time-travel now.

    --
    Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
    EnterpriseDB Corporation | fax: 732.331.1301
    499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
    Edison, NJ 08837 | http://www.enterprisedb.com/


    --
    Thanks,
    Gokul.
    CertoSQL Project,
    Allied Solution Group.
    (www.alliedgroups.com)
  • Hans-Jürgen Schönig at Nov 2, 2007 at 12:40 pm

    I think Simon Riggs is already working on that idea. This one is
    fairly easy to implement. I think these are some of the features
    only a time-stamp based database can implement. I think database
    standards were formed during the time, when the data consistency
    was provided with Lock based mechanisms. And moreover i have
    already committed on the indexes with snapshot and i am still
    waiting for its approval from hackers. If that does go through,
    then i need to work on the reverse mapping hash tables, which is
    really a long task. So i may not be able to take up time-travel now.

    if i remember my last talk with Simon correctly the idea is to have
    timetravel across transactions.
    having this feature inside a transaction will not make it into CVS as
    it is basically of no practical use.
    i would suggest to put some effort into making it work across
    transactions. just saving the snapshot is not enough
    here - there are a couple of other things which have to be taken into
    consideration (transaction wraparound, etc.)

    if you want to work on timetravel my team and i can provide some
    assistance as we wanted to help in this area anyway.

    best regards,

    hans


    --
    Cybertec Schönig & Schönig GmbH
    Gröhrmühlgasse 26, 2700 Wiener Neustadt
    Tel: +43/1/205 10 35 / 340
    www.postgresql.at, www.cybertec.at
  • Gokulakannan Somasundaram at Nov 2, 2007 at 4:23 pm

    On 11/2/07, Hans-Juergen Schoenig wrote:

    I think Simon Riggs is already working on that idea. This one is fairly
    easy to implement. I think these are some of the features only a time-stamp
    based database can implement. I think database standards were formed during
    the time, when the data consistency was provided with Lock based mechanisms.
    And moreover i have already committed on the indexes with snapshot and i am
    still waiting for its approval from hackers. If that does go through, then
    i need to work on the reverse mapping hash tables, which is really a long
    task. So i may not be able to take up time-travel now.



    if i remember my last talk with Simon correctly the idea is to have
    timetravel across transactions.
    having this feature inside a transaction will not make it into CVS as it
    is basically of no practical use.
    I am just reminding my days of working with Oracle. The Flashback feature
    was allowed only for DBAs, and they never let the developers access that
    feature, unless there is a strong reason for it. It was more thought of as a
    feature for recovery and they never let deveopers use that in the
    application. Also it was designed as a optional feature. If its switched
    off, it cannot be used. If someone comes up with the time travel feature
    across transactions and if it is designed as non-optional feature and if it
    happens to be a feature, which DBA can let the developers use freely, then
    this feature should be rolled back. The feature i am talking about is very
    simple and it won't even add 100 lines of code into the Postgres source code
    base.

    i would suggest to put some effort into making it work across transactions.
    just saving the snapshot is not enough
    here - there are a couple of other things which have to be taken into
    consideration (transaction wraparound, etc.)
    When i think about it, Timetravel always look big for me and i don't have
    the bandwidth to take that up.


    if you want to work on timetravel my team and i can provide some assistance
    as we wanted to help in this area anyway.
    Thanks. Please send me your findings.




    --
    Thanks,
    Gokul.
    CertoSQL Project,
    Allied Solution Group.
    (www.alliedgroups.com)
  • Tom Lane at Nov 2, 2007 at 4:29 pm

    "Gokulakannan Somasundaram" <gokul007@gmail.com> writes:
    The feature i am talking about is very simple and it won't even add
    100 lines of code into the Postgres source code base.
    [ splorfff... ] The grammar support alone will cost ten times that.

    You should probably reflect on the fact that not one single person
    besides you thinks this is a good idea.

    regards, tom lane
  • Gokulakannan Somasundaram at Nov 2, 2007 at 5:03 pm

    On 11/2/07, Tom Lane wrote:
    "Gokulakannan Somasundaram" <gokul007@gmail.com> writes:
    The feature i am talking about is very simple and it won't even add
    100 lines of code into the Postgres source code base.
    [ splorfff... ] The grammar support alone will cost ten times that.

    You should probably reflect on the fact that not one single person
    besides you thinks this is a good idea.

    regards, tom lane


    Tom,
    If you have made this comment, when i requested for the comment, i
    would have dropped this idea there itself. :). But please let me know your
    comments on why you feel this is useless.
    But i guess(I may be wrong), you may be wrong about the grammar support
    part.

    --
    Thanks,
    Gokul.
    CertoSQL Project,
    Allied Solution Group.
    (www.alliedgroups.com)
  • Joshua D. Drake at Nov 2, 2007 at 5:05 pm

    On Fri, 2 Nov 2007 22:33:16 +0530 "Gokulakannan Somasundaram" wrote:



    Tom,
    If you have made this comment, when i requested for the
    comment, i would have dropped this idea there itself. :). But please
    let me know your comments on why you feel this is useless.
    But i guess(I may be wrong), you may be wrong about the grammar
    support part.
    Wow, you are new here aren't you? I assure you, Tom is the last person
    that is going to be wrong about grammar support.

    Joshua D. Drake



    - --

    === The PostgreSQL Company: Command Prompt, Inc. ===
    Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
    PostgreSQL solutions since 1997 http://www.commandprompt.com/
    UNIQUE NOT NULL
    Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
    PostgreSQL Replication: http://www.commandprompt.com/products/
  • Tom Lane at Nov 2, 2007 at 6:19 pm

    "Gokulakannan Somasundaram" <gokul007@gmail.com> writes:
    On 11/2/07, Tom Lane wrote:
    [ splorfff... ] The grammar support alone will cost ten times that.
    But i guess(I may be wrong), you may be wrong about the grammar support
    part.
    Well, a crude estimate is that SELECT ... AS OF Savepoint would take
    about the same amount of grammar and nodetree infrastructure as SELECT
    ... FOR UPDATE does. Try grepping for all the code that deals with
    LockingClause (the raw-grammar representation of FOR UPDATE) and
    RowMarkClause (the transformed representation). Hits on the list fields
    SelectStmt.lockingClause and Query.rowMarks would be relevant too
    (code touching these lists wouldn't necessarily mention the list member
    node type anyplace).

    1000 lines may be an overestimate, but it's probably nearer the mark
    than 100 is.

    regards, tom lane
  • Jonah H. Harris at Nov 2, 2007 at 7:09 pm

    On 11/2/07, Tom Lane wrote:
    "Gokulakannan Somasundaram" <gokul007@gmail.com> writes:
    The feature i am talking about is very simple and it won't even add
    100 lines of code into the Postgres source code base.
    [ splorfff... ] The grammar support alone will cost ten times that.

    You should probably reflect on the fact that not one single person
    besides you thinks this is a good idea.
    Well said.

    --
    Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
    EnterpriseDB Corporation | fax: 732.331.1301
    499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
    Edison, NJ 08837 | http://www.enterprisedb.com/
  • Decibel! at Nov 12, 2007 at 3:16 pm

    On Nov 2, 2007, at 11:29 AM, Tom Lane wrote:
    [ splorfff... ] The grammar support alone will cost ten times that.
    When next we meet, expect me to ask you how that's pronounced. ;)
    --
    Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
    Give your computer some brain candy! www.distributed.net Team #1828
  • Gregory Stark at Nov 12, 2007 at 3:44 pm

    "Decibel!" <decibel@decibel.org> writes:
    On Nov 2, 2007, at 11:29 AM, Tom Lane wrote:
    [ splorfff... ] The grammar support alone will cost ten times that.
    When next we meet, expect me to ask you how that's pronounced. ;)
    I think it can only be properly pronounced with a mug of coffee

    --
    Gregory Stark
    EnterpriseDB http://www.enterprisedb.com
    Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!
  • Alvaro Herrera at Nov 12, 2007 at 7:30 pm

    Gregory Stark escribió:
    "Decibel!" <decibel@decibel.org> writes:
    On Nov 2, 2007, at 11:29 AM, Tom Lane wrote:
    [ splorfff... ] The grammar support alone will cost ten times that.
    When next we meet, expect me to ask you how that's pronounced. ;)
    I think it can only be properly pronounced with a mug of coffee
    Where is the content of the mug expected to be, by the time you reach
    the ellipsis?

    --
    Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1", W 73º 13' 56.4"
    "I love the Postgres community. It's all about doing things _properly_. :-)"
    (David Garamond)
  • Kevin Grittner at Nov 12, 2007 at 7:59 pm
    On Mon, Nov 12, 2007 at 1:16 PM, in message
    <20071112191606.GA22963@alvh.no-ip.org>, Alvaro Herrera
    wrote:
    Gregory Stark escribió:
    "Decibel!" <decibel@decibel.org> writes:
    On Nov 2, 2007, at 11:29 AM, Tom Lane wrote:
    [ splorfff... ] The grammar support alone will cost ten times
    that.
    When next we meet, expect me to ask you how that's pronounced. ;)
    I think it can only be properly pronounced with a mug of coffee
    Where is the content of the mug expected to be, by the time you reach
    the ellipsis?
    I believe that about one mouthful of the contents of the mug are
    expected to be distributed across the desktop, keyboard, and
    monitor at that point.
  • Andrew Dunstan at Nov 12, 2007 at 8:30 pm

    Alvaro Herrera wrote:
    Gregory Stark escribió:
    "Decibel!" <decibel@decibel.org> writes:

    On Nov 2, 2007, at 11:29 AM, Tom Lane wrote:

    [ splorfff... ] The grammar support alone will cost ten times that.
    When next we meet, expect me to ask you how that's pronounced. ;)
    I think it can only be properly pronounced with a mug of coffee
    Where is the content of the mug expected to be, by the time you reach
    the ellipsis?
    exiting the nose?


    cheers

    andrew
  • Simon Riggs at Nov 4, 2007 at 9:22 am

    On Fri, 2007-11-02 at 13:40 +0100, Hans-Juergen Schoenig wrote:

    I think Simon Riggs is already working on that idea. This one is
    fairly easy to implement. I think these are some of the features
    only a time-stamp based database can implement. I think database
    standards were formed during the time, when the data consistency was
    provided with Lock based mechanisms. And moreover i have already
    committed on the indexes with snapshot and i am still waiting for
    its approval from hackers. If that does go through, then i need to
    work on the reverse mapping hash tables, which is really a long
    task. So i may not be able to take up time-travel now.



    if i remember my last talk with Simon correctly the idea is to have
    timetravel across transactions.
    having this feature inside a transaction will not make it into CVS as
    it is basically of no practical use.
    i would suggest to put some effort into making it work across
    transactions. just saving the snapshot is not enough
    here - there are a couple of other things which have to be taken into
    consideration (transaction wraparound, etc.)


    if you want to work on timetravel my team and i can provide some
    assistance as we wanted to help in this area anyway.
    Yeh, I'd want to do that for recovery purposes though, not for general
    access.

    The idea was to write a syncpoint every N seconds where we record the
    time and a snapshot of what's in progress. The syncpoints would need to
    be visible in the system like prepared transactions. A superuser could
    reconnect to one of the syncpoints and see data as it was at the
    previous time. Difficulties being dropped objects and the negative
    effects on vacuuming, both of which are surmountable, but are big
    current blockers.

    I'm not working on this currently, maybe an 8.5+ feature.

    --
    Simon Riggs
    2ndQuadrant http://www.2ndQuadrant.com
  • Gokulakannan Somasundaram at Nov 5, 2007 at 6:28 am

    On 11/4/07, Simon Riggs wrote:
    On Fri, 2007-11-02 at 13:40 +0100, Hans-Juergen Schoenig wrote:

    I think Simon Riggs is already working on that idea. This one is
    fairly easy to implement. I think these are some of the features
    only a time-stamp based database can implement. I think database
    standards were formed during the time, when the data consistency was
    provided with Lock based mechanisms. And moreover i have already
    committed on the indexes with snapshot and i am still waiting for
    its approval from hackers. If that does go through, then i need to
    work on the reverse mapping hash tables, which is really a long
    task. So i may not be able to take up time-travel now.



    if i remember my last talk with Simon correctly the idea is to have
    timetravel across transactions.
    having this feature inside a transaction will not make it into CVS as
    it is basically of no practical use.
    i would suggest to put some effort into making it work across
    transactions. just saving the snapshot is not enough
    here - there are a couple of other things which have to be taken into
    consideration (transaction wraparound, etc.)


    if you want to work on timetravel my team and i can provide some
    assistance as we wanted to help in this area anyway.


    Thanks for your inputs Simon.

    Yeh, I'd want to do that for recovery purposes though, not for general
    access.

    I guessed it.


    The idea was to write a syncpoint every N seconds where we record the
    time and a snapshot of what's in progress.

    What exactly is getting recorded here? Will the Syncpoint be similar to the
    Undo Log at distinct intervals?
    This may be a stupid question. But is it not a good idea to implement
    time-travel through the Replication server.

    The syncpoints would need to
    be visible in the system like prepared transactions. A superuser could
    reconnect to one of the syncpoints and see data as it was at the
    previous time. Difficulties being dropped objects and the negative
    effects on vacuuming, both of which are surmountable, but are big
    current blockers.

    I'm not working on this currently, maybe an 8.5+ feature.

    --
    Simon Riggs
    2ndQuadrant http://www.2ndQuadrant.com

    --
    Thanks,
    Gokul.
    CertoSQL Project,
    Allied Solution Group.
    (www.alliedgroups.com)
  • Simon Riggs at Nov 5, 2007 at 6:51 am

    On Mon, 2007-11-05 at 11:58 +0530, Gokulakannan Somasundaram wrote:

    The idea was to write a syncpoint every N seconds where we
    record the
    time and a snapshot of what's in progress.

    What exactly is getting recorded here? Will the Syncpoint be similar
    to the Undo Log at distinct intervals?
    Postgres needs to record the snapshot to allow visibility checks. There
    is no Undo log; the database records themselves represent the logical
    equivalent of an Undo log in other databases.

    But as I said, I'm not working on this, so I'm not going into detailed
    design anytime soon.

    --
    Simon Riggs
    2ndQuadrant http://www.2ndQuadrant.com

Related Discussions

People

Translate

site design / logo © 2021 Grokbase