Postgres Pro
Downloads
Home   >   mailing lists

Re: Postgres code for a query intermediate dataset - Mailing list pgsql-hackers

From Rohit Goyal
Subject Re: Postgres code for a query intermediate dataset
Date
Msg-id CANqGtSs9jTZEP3y1PWP5vMKzWOag8Ln_KNiaLy8WykO8SUHAGA@mail.gmail.com
Whole thread Raw
In response to Re: Postgres code for a query intermediate dataset  (Mark Kirkwood <mark.kirkwood@catalyst.net.nz>)
Responses Re: Postgres code for a query intermediate dataset
Re: Postgres code for a query intermediate dataset
List pgsql-hackers
Tree view
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.kirkwood@catalyst.net.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.kirkwood@catalyst.net.nz <mailto:mark.kirkwood@catalyst.net.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

pgsql-hackers by date:

Previous
From: Björn Harrtell
Date:
Subject: Re: Tips/advice for implementing integrated RESTful HTTP API
Next
From: Mark Kirkwood
Date:
Subject: Re: Postgres code for a query intermediate dataset