Re: Proposal: Select ... AS OF Savepoint - Mailing list pgsql-hackers
| From | Gokulakannan Somasundaram |
|---|---|
| Subject | Re: Proposal: Select ... AS OF Savepoint |
| Date | |
| Msg-id | 9362e74e0711020313hac04ecgdc8eee07539ed49e@mail.gmail.com Whole thread Raw |
| In response to | Re: Proposal: Select ... AS OF Savepoint (Martijn van Oosterhout <kleptog@svana.org>) |
| Responses |
Re: Proposal: Select ... AS OF Savepoint
|
| List | pgsql-hackers |
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)
pgsql-hackers by date: