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!