Re: Postgres code for a query intermediate dataset

Hi Mark & Atri, :)

Thanks for reply. But, I think i confused you. I am talking about access
using indexes. So, I assume that B+ tree store key-value pair where rohit
is the key and all the versions are its value.

Another way to think is I have a secondary index on emp. name and there are
4 rohit exist in DB. So, now B+ tree gives me 4 different tuple pointer for
each Rohit. I want to know the code portion for this where i can see all 4
tuple pointer before each one have I/O access to fetch its tuple.

Are the suggestions still valid?

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

> On 14/09/14 20:11, Rohit Goyal wrote:
>
>> Hi Mark,
>>
>> On Sun, Sep 14, 2014 at 8:57 AM, Mark Kirkwood
>> <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz <mailto:mark(dot)kirkwood(at)catalyst(dot)net(dot)nz>>
>> wrote:
>>
>
> 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
>>
>
> Well that's going to be a bit of a learning curve for you :-), the concept
> to get familiar with is snapshots (see src/backand/access/heap/heapam.c
> to get started).

Thanks I will read it. :) Can you please tel me some specifics from this c
file, if you already know :)

>
>
>
>>
>> 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?
>>
>
> I'm not sure we have specif examples in the docs for what you want to do,
> but generally see http://www.postgresql.org/docs/9.4/static/plpgsql-
> trigger.html
>
> Cheers
>
> Mark
>

--
Regards,
Rohit Goyal