Re: Postgres code for a query intermediate dataset

Hi Mark,

On Sun, Sep 14, 2014 at 8:57 AM, Mark Kirkwood <
mark(dot)kirkwood(at)catalyst(dot)net(dot)nz> wrote:

> On 14/09/14 05:36, Rohit Goyal wrote:
>
>> Hi All,
>>
>> I want to work on the code of intermediate dataset of select and update
>> query.
>>
>> For example.
>>
>> Rohit's salary has been updated 4 times, so it has 4 different version
>> of salary.
>>
>> I want to select salary of person named Rohit. Now suppose , in
>> intermediate result, I found 4 different versions of the data. I want to
>> know the code portion which i need to look for working on all 4 versions
>> in dataset. :)
>>
>>
>>
> Hi Rohit,
>
> Currently in Postgres, these intermediate versions all exist - however a
> given session can only see one of them. Also VACUUM is allowed to destroy
> versions that no other transactions can see.
>
> Exactly, one visible version is there per session. But, I want to test my
algorithm in which i myself want to work on all the intermediate version
and find the correct one for the session.

> So if I'm understanding you correctly, you would like to have some way for
> a session to see *all* these versions (and I guess preventing VACUUM from
> destroying them).
>
yes and I also want to know the portion of code where i can find all the
historical or intermediate versions

>
> It is certainly possible (or used to be via snapshot manipulation, I
> haven't looked at that code in a while sorry) to enable a session to see
> all the old versions, and is quite a cool idea (Postgres used to have this
> ability in older versions - called Time Travel).
>
> For pure practicality, this can be achieved without any code modifications
> using TRIGGERs and an extra table (as Gavin alludes to).
>
Can you explain me more about how to starting working using trigger?

>
> Do tell us a bit more about what you are wanting to do!
>
> Cheers
>
> Mark
>
>

--
Regards,
Rohit Goyal