Thread: Clarification on a Time travel feature

Hi,<br />   I went through the mailing list and couldn't get answer to the question.<br /><br />a) Is there a proposal
inplace for going back in time within a transaction?<br /><br /><br clear="all" /><br />-- <br />Thanks,<br />Gokul.
<br/>CertoSQL Project,<br />Allied Solution Groups.<br />(<a
href="http://www.alliedgroups.com">www.alliedgroups.com</a>) 

This is the complete question.<br /><br />Hi,<br />   I went through the mailing list and couldn't get answer to the
question.<br/><br />a) Is there a proposal in place for going back in time within a transaction?<br /><br />I am
thinkingof some functionality like this. <br /><br />BEGIN<br />select ... (command id 1)<br />update ... (command id
10)<br/>..<br /><br />select ... as of command id 1.<br /><br />END;<br /><br /><br />b) I know time travel doesn't
allowto change the details of the past. But is it possible to provide an alternative to rollback <br />by means of
changingthe data with older command id.<br /><br />But i just want to clarify once more that i am restricting the scope
oftime travel within a transaction..<br /><br /><br /><br /><br /> <br />Thanks,<br />Gokul.<br />CertoSQL Project, <br
/>AlliedSolution Groups.<br />(<a href="http://www.alliedgroups.com">www.alliedgroups.com</a>)  

On 10/31/07, Gokulakannan Somasundaram <gokul007@gmail.com> wrote:
> a) Is there a proposal in place for going back in time within a transaction?

Yes, it's called a savepoint :)

> b) I know time travel doesn't allow to change the details of the past. But
> is it possible to provide an alternative to rollback
> by means of changing the data with older command id.
>
> But i just want to clarify once more that i am restricting the scope of time
> travel within a transaction..

What exactly are you trying to do that can't be accomplished with savepoints?

-- 
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/

No i am referring to time travel within a transaction, not outside.




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

There is only one condition under which this would be useful.

Current Scenario:

BEGIN

Select x into var from inventory where y=const;

update inventory..

some more DMLs.

some select into var2;

if (condition based on var2)
   use the var;

END;

here i have to fire the first select query all the 100% of the time. Say if i have a feature to time travel within a transaction, i need to fire that select query on the probability of the condition based on var2 getting true.

After that implementation

BEGIN;
store command id and increment it into cid;
some DMLS
some select into var2
if(condition based on var2)
     select into :var as of  cid;

I know this is a small use-case. But if you think about it, the implementation is very easy. It wouldn't take much time to implement it.

Thanks,
Gokul.










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

Gokulakannan Somasundaram wrote:
> a) Is there a proposal in place for going back in time within a transaction?

Within a transaction? No, can't remember one. Doesn't sound very useful.

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com

I want to unsubscrib me please,



Mensaje citado por Hans-Juergen Schoenig <postgres@cybertec.at>:

> hello ...
>
> i guess there is no formal proposal yet but there are some ideas
> around and some major challenges have been discussed already.
> i think simon riggs was planning to work on it in the future.
> the basic idea here is to have the option to create a "snapshot"
> which then stays in the database. the main challenge is that PostgreSQL
> should not keep all version of a row since the snapshot but VACUUM
> should be able to clean out all rows which are not seen by any
> snapshot or any ongoing transaction.
> this should be a quite fancy solution which is quite space efficient.
>
> internally we had the idea of tweaking VACUUM a little:
>
>     VACUUM BEFORE timestamp;
> and ...
>     SET current_snapshot TO '2007-10-10 ...';
>
> this would allow a queries to use any snapshot after the timestamp
> defined by VACUUM (if data is around).
> the downside here: you might potentially eat up more space.
> flashback data should be read only, of course.
>
>     best regards,
>
>         hans
>
>
>
> On Oct 31, 2007, at 11:31 AM, Gokulakannan Somasundaram wrote:
>
> > Hi,
> >    I went through the mailing list and couldn't get answer to the
> > question.
> >
> > a) Is there a proposal in place for going back in time within a
> > transaction?
> >
> >
> >
> > --
> > Thanks,
> > Gokul.
> > CertoSQL Project,
> > Allied Solution Groups.
> > (www.alliedgroups.com)
>
>
>
> --
> 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
>
>
>
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
>
>




----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.

On Oct 31, 2007, at 11:10 , jaimelima@al.uap.edu.ar wrote:

>
> I want to unsubscrib me please,
From the headers of every list message:

> List-Archive: <http://archives.postgresql.org/pgsql-hackers>
> List-Help: <mailto:majordomo@postgresql.org?body=help>
> List-ID: <pgsql-hackers.postgresql.org>
> List-Owner: <mailto:pgsql-hackers-owner@postgresql.org>
> List-Post: <mailto:pgsql-hackers@postgresql.org>
> List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql- 
> hackers>
> List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub% 
> 20pgsql-hackers>

And from the website:

http://archives.postgresql.org/pgsql-hackers/

Michael Glaesemann
grzm seespotcode net

look at the headers on email from the list and it tells you how to 
unsubscribe.

jaimelima@al.uap.edu.ar wrote:
> I want to unsubscrib me please,
>
>
>
> Mensaje citado por Hans-Juergen Schoenig <postgres@cybertec.at>:
>
>   
>> hello ...
>>
>> i guess there is no formal proposal yet but there are some ideas
>> around and some major challenges have been discussed already.
>> i think simon riggs was planning to work on it in the future.
>> the basic idea here is to have the option to create a "snapshot"
>> which then stays in the database. the main challenge is that PostgreSQL
>> should not keep all version of a row since the snapshot but VACUUM
>> should be able to clean out all rows which are not seen by any
>> snapshot or any ongoing transaction.
>> this should be a quite fancy solution which is quite space efficient.
>>
>> internally we had the idea of tweaking VACUUM a little:
>>
>>     VACUUM BEFORE timestamp;
>> and ...
>>     SET current_snapshot TO '2007-10-10 ...';
>>
>> this would allow a queries to use any snapshot after the timestamp
>> defined by VACUUM (if data is around).
>> the downside here: you might potentially eat up more space.
>> flashback data should be read only, of course.
>>
>>     best regards,
>>
>>         hans
>>
>>
>>
>> On Oct 31, 2007, at 11:31 AM, Gokulakannan Somasundaram wrote:
>>
>>     
>>> Hi,
>>>    I went through the mailing list and couldn't get answer to the
>>> question.
>>>
>>> a) Is there a proposal in place for going back in time within a
>>> transaction?
>>>
>>>
>>>
>>> --
>>> Thanks,
>>> Gokul.
>>> CertoSQL Project,
>>> Allied Solution Groups.
>>> (www.alliedgroups.com)
>>>       
>>
>> --
>> 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
>>
>>
>>
>> --
>> This message has been scanned for viruses and
>> dangerous content by MailScanner, and is
>> believed to be clean.
>>
>>
>>     
>
>
>
>
> ----------------------------------------------------------------
> This message was sent using IMP, the Internet Messaging Program.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>   

There is one more condition under which this becomes useful. Suppose you have requirements for setting transaction isolation level as serializable only for select statements, you can avoid setting the transaction isolation level as serializable by making use of this feature. any expert comments?






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