Re: Postgres code for a query intermediate dataset

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.

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

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

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

Cheers

Mark