Re: Proposal: Select ... AS OF Savepoint

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)