Re: Patch proposal: query result history in psql

Attached patch contains feature I've implemented for myself, to make
working with huge datasets easier.

I work with large datasets (1E8 - 1E9 records), and the nature of my
work is such that I must dig something out of the data on ad-hoc
basis. I spend a lot of time with psql.

Sometimes a query runs for few minutes. And when the result finally
arrives, sometimes it's too big, contains too much columns or is
ordered incorrectly. Quite often I was thinking to myself: "If only I
could run query on the result, instead of having to re-run the
original query and wait few more minutes...".

Eventually I just wrote the feature. I use it every day now and I'm
really happy with it.

*How it works*

After query result arrives, a message is displayed : "Query result
stored as :andN", where N is ordinal number.

User can then use the pseudo-variable :ansN as a relation name in
subsequent queries.

Under the hood, all the query results are stored locally, and when
:asnN variable is used, temporary table is created and populated with
the data. The variable then expands to the table's name.

Sample session:

===

anstest=# select * from quotes limit 10;
date | open | high | low | close | volume | adjclose
------------+-------+-------+-------+-------+----------+----------
2013-05-03 | 22.57 | 22.85 | 22.55 | 22.57 | 45523300 | 22.57
2013-05-02 | 22.25 | 22.32 | 22.15 | 22.32 | 27651500 | 22.32
2013-05-01 | 22.10 | 22.35 | 22.10 | 22.15 | 39201600 | 22.15
2013-04-30 | 22.29 | 22.38 | 22.21 | 22.29 | 34054800 | 22.29
2013-04-29 | 22.31 | 22.32 | 22.00 | 22.27 | 36531800 | 22.27
2013-04-26 | 21.98 | 22.40 | 21.97 | 22.21 | 47012500 | 22.21
2013-04-25 | 22.21 | 22.23 | 21.91 | 21.95 | 41462900 | 21.95
2013-04-24 | 21.69 | 22.03 | 21.65 | 21.96 | 51496600 | 21.96
2013-04-23 | 21.55 | 21.69 | 21.36 | 21.50 | 65489600 | 21.50
2013-04-22 | 21.67 | 21.68 | 21.11 | 21.35 | 87787900 | 21.35
(10 rows)

Query result stored as :ans0
anstest=# select date,close from :ans0 where date < '2013-05-01';
date | close
------------+-------
2013-04-30 | 22.29
2013-04-29 | 22.27
2013-04-26 | 22.21
2013-04-25 | 21.95
2013-04-24 | 21.96
2013-04-23 | 21.50
2013-04-22 | 21.35
(7 rows)

Query result stored as :ans1
anstest=# select * from :ans1 order by date;
date | close
------------+-------
2013-04-22 | 21.35
2013-04-23 | 21.50
2013-04-24 | 21.96
2013-04-25 | 21.95
2013-04-26 | 22.21
2013-04-29 | 22.27
2013-04-30 | 22.29
(7 rows)

Query result stored as :ans2

===

I find this feature quite useful, but I understand that my use case
may be quite unique.

If maintainers think that this is something that could be useful for
general public, I'm ready to polish any rough edges of the attached
patch, to make it suitable for inclusion.

Because the feature introduces some overhead, it should probably be
turned off by default and turned on by backslash command and/or
command-line parameter.

Maciek

Maciej Gajewski <maciej(dot)gajewski0(at)gmail(dot)com> writes:
> I find this feature quite useful, but I understand that my use case
> may be quite unique.

Just to say that I too find what you've done quite useful. Please add
your patch to the next commit fest for consideration in 9.4!

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

On May 16, 2013, at 7:02 AM, Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> wrote:

>> I find this feature quite useful, but I understand that my use case
>> may be quite unique.
>
> Just to say that I too find what you've done quite useful. Please add
> your patch to the next commit fest for consideration in 9.4!

FYI, you can add it here:

https://commitfest.postgresql.org/action/commitfest_view?id=18

Best,

David

Polished version of the patch.

* The feature is disabled by default, enabled by backslash command
\ans. Additionaly, \ansclean cleans the result history.
* Escaping is applied when building COPY IN string

This is patch is a diff between master:230e92c and
https://github.com/maciekgajewski/psql-ans.git:2997f9c

Maciek

On 16 May 2013 19:18, David E. Wheeler <david(at)justatheory(dot)com> wrote:
> On May 16, 2013, at 7:02 AM, Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> wrote:
>
>>> I find this feature quite useful, but I understand that my use case
>>> may be quite unique.
>>
>> Just to say that I too find what you've done quite useful. Please add
>> your patch to the next commit fest for consideration in 9.4!
>
> FYI, you can add it here:
>
> https://commitfest.postgresql.org/action/commitfest_view?id=18
>
> Best,
>
> David
>