Re: Postgres code for a query intermediate dataset

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

    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