Thread: Proposal: Select ... AS OF Savepoint
Hi,<br /> I would like to propose an additional feature for Postgres to enable time-travelling inside a transaction.<br/><br />I went through the source code and i found Savepoint is already saving the necessary information.But currently it doesn't store the CommandId. <br /><br />This change, if implemented would save the commandid into savepoint state, increase the command id counter for every savepoint and finally we can issue a command like<br />(One more global variable to store the current state) <br />Select ... as of savepoint savepoint-name.<br /><br/>This would reduce the requirement for Serializable transactions in some cases and adds one more feature under theumbrella of postgres.<br /><br />-- <br />Thanks,<br />Gokul. <br />CertoSQL Project,<br />Allied Solution Groups.<br/>(<a href="http://www.alliedgroups.com">www.alliedgroups.com</a>)
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
On 11/2/07, Martijn van Oosterhout <kleptog@svana.org> wrote:
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?
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.
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)
On 11/2/07, Gokulakannan Somasundaram <gokul007@gmail.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.harris@enterprisedb.com Edison, NJ 08837 | http://www.enterprisedb.com/
On 11/2/07, Jonah H. Harris <jonah.harris@gmail.com> 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.
On 11/2/07, Gokulakannan Somasundaram <gokul007@gmail.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.harris@enterprisedb.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@cybertec.at> wrote:
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.
When i think about it, Timetravel always look big for me and i don't have the bandwidth to take that up.
Thanks. Please send me your findings.
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 enoughhere - 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@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
On 11/2/07, Tom Lane <tgl@sss.pgh.pa.us> 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.
"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.
--
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@gmail.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@gmail.com> writes:
> On 11/2/07, Tom Lane <tgl@sss.pgh.pa.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@sss.pgh.pa.us> 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/
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@2ndquadrant.com> wrote:
Thanks for your inputs Simon.
I guessed it.
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.
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@decibel.org Give your computer some brain candy! www.distributed.net Team #1828
"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'sPostgreSQL training!
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)
>>> On Mon, Nov 12, 2007 at 1:16 PM, in message <20071112191606.GA22963@alvh.no-ip.org>, Alvaro Herrera <alvherre@alvh.no-ip.org> 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.
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