Re: Clarification on a Time travel feature

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)

This is the complete question.

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?

I am thinking of some functionality like this.

BEGIN
select ... (command id 1)
update ... (command id 10)
..

select ... as of command id 1.

END;

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

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

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

On 10/31/07, Gokulakannan Somasundaram <gokul007(at)gmail(dot)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(dot)harris(at)enterprisedb(dot)com
Edison, NJ 08837 | http://www.enterprisedb.com/

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

On 10/31/07, Hans-Juergen Schoenig <postgres(at)cybertec(dot)at> wrote:
>
> 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
>
>
>

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

On 10/31/07, Heikki Linnakangas <heikki(at)enterprisedb(dot)com> wrote:
>
> 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
>

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

I want to unsubscrib me please,

Mensaje citado por Hans-Juergen Schoenig <postgres(at)cybertec(dot)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.

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

jaimelima(at)al(dot)uap(dot)edu(dot)ar wrote:
> I want to unsubscrib me please,
>
>
>
> Mensaje citado por Hans-Juergen Schoenig <postgres(at)cybertec(dot)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
>
>

On Oct 31, 2007, at 11:10 , jaimelima(at)al(dot)uap(dot)edu(dot)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(at)postgresql(dot)org?body=help>
> List-ID: <pgsql-hackers.postgresql.org>
> List-Owner: <mailto:pgsql-hackers-owner(at)postgresql(dot)org>
> List-Post: <mailto:pgsql-hackers(at)postgresql(dot)org>
> List-Subscribe: <mailto:majordomo(at)postgresql(dot)org?body=sub%20pgsql-
> hackers>
> List-Unsubscribe: <mailto:majordomo(at)postgresql(dot)org?body=unsub%
> 20pgsql-hackers>

And from the website:

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

Michael Glaesemann
grzm seespotcode net

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?

On 10/31/07, Gokulakannan Somasundaram <gokul007(at)gmail(dot)com> wrote:
>
> 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.
>
>
>
>
>
>
>
> On 10/31/07, Heikki Linnakangas <heikki(at)enterprisedb(dot)com > wrote:
> >
> > 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
> >
>
>
>
> --
> Thanks,
> Gokul.
> CertoSQL Project,
> Allied Solution Groups.
> (www.alliedgroups.com)
>

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