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

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