Re: Proposal: Select ... AS OF Savepoint

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)

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(at)svana(dot)org> http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
> -- John F Kennedy

On 11/2/07, Martijn van Oosterhout <kleptog(at)svana(dot)org> 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(at)svana(dot)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)

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

On 11/2/07, Jonah H. Harris <jonah(dot)harris(at)gmail(dot)com> wrote:
>
> On 11/2/07, Gokulakannan Somasundaram <gokul007(at)gmail(dot)com> 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(dot)harris(at)enterprisedb(dot)com
> Edison, NJ 08837 | http://www.enterprisedb.com/
>

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

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

On 11/2/07, Hans-Juergen Schoenig <postgres(at)cybertec(dot)at> 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)

"Gokulakannan Somasundaram" <gokul007(at)gmail(dot)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

On 11/2/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> "Gokulakannan Somasundaram" <gokul007(at)gmail(dot)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)

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Fri, 2 Nov 2007 22:33:16 +0530
"Gokulakannan Somasundaram" <gokul007(at)gmail(dot)com> 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/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHK1jTATb/zqfZUUQRAtoHAKCd2F35SPaHBsfS4JaanYkvBa/V2QCeP5x3
swyEo4Xm3h7wxxW1FhoSUgA=
=VLSb
-----END PGP SIGNATURE-----

"Gokulakannan Somasundaram" <gokul007(at)gmail(dot)com> writes:
> On 11/2/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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

On 11/2/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Gokulakannan Somasundaram" <gokul007(at)gmail(dot)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(dot)harris(at)enterprisedb(dot)com
Edison, NJ 08837 | http://www.enterprisedb.com/

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

On 11/4/07, Simon Riggs <simon(at)2ndquadrant(dot)com> 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)

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

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(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

"Decibel!" <decibel(at)decibel(dot)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!

Gregory Stark escribió:
> "Decibel!" <decibel(at)decibel(dot)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)

>>> On Mon, Nov 12, 2007 at 1:16 PM, in message
<20071112191606(dot)GA22963(at)alvh(dot)no-ip(dot)org>, Alvaro Herrera
<alvherre(at)alvh(dot)no-ip(dot)org> wrote:
> Gregory Stark escribió:
>> "Decibel!" <decibel(at)decibel(dot)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.

Alvaro Herrera wrote:
> Gregory Stark escribió:
>
>> "Decibel!" <decibel(at)decibel(dot)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