Re: Postgres code for a query intermediate dataset

On 14/09/14 20:24, Atri Sharma wrote:
>
>
> On Sun, Sep 14, 2014 at 1:30 PM, Mark Kirkwood
> <mark.kirkwood@catalyst.net.nz <mailto:mark.kirkwood@catalyst.net.nz>>
> wrote:
>
>     On 14/09/14 19:25, Atri Sharma wrote:
>
>
>
>         On Sunday, September 14, 2014, Mark Kirkwood
>         <mark.kirkwood@catalyst.net.nz
>         <mailto:mark.kirkwood@catalyst.net.nz>
>         <mailto:mark.kirkwood@__catalyst.net.nz
>         <mailto:mark.kirkwood@catalyst.net.nz>>>
>
>         wrote:
>
>              On 14/09/14 05:36, Rohit Goyal wrote:
>
>                  Hi All,
>
>                  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.
>
>              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).
>
>
>
>         Any modifications of that sort are bound to introduce lots of
>         pain, not
>         to mention performance degradation and the added responsibility of
>         ensuring that dead tuples don't bloat up the system (prevent
>         vacuum from
>         running at regular intervals and you can have a xid wraparound).
>
>         I just mentioned that in case you are planning to go in that
>         direction.
>         If you only want the data, use the triggers as Gavin mentioned.
>
>
>     Obviously in the general case sure - but (as yet) we don't have much
>     idea about Rohit's use case and workload. If retrieving past
>     versions is the *primary* workload bias and high update concurrency
>     is not required then this could well work better than a trigger
>     based solution.
>
>     And it does not seem too onerous to have the ability to switch this
>     on as required, viz:
>
>     ALTER TABLE table1 VERSIONING;
>
>     (or similar syntax) which makes VACUUM leave this table alone.
>
>
> How do you plan to  do all that VACUUM does for this table then?
>
> It seems to me that you are saying to VACUUM that it need not be
> concerned with table 'A' and you are assuming ownership of all the tasks
> performed by VACUUM for this table. Seems pretty broken to me, not to
> mention the performance degradations.
>

I think the whole point of such a modification is that nothing is done 
to such tables, as you want to see all the previous versions.

Clearly this is less performant for standard workloads...but we are 
talking about non standard workloads surely...

Regards

Mark