Re: pgbench - add \aset to store results of a combined query

From: Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>
To: PostgreSQL Developers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: pgbench - add \aset to store results of a combined query
Date: 2019-05-23 14:10:55
Message-ID: alpine.DEB.2.21.1905231608480.28482@lancre
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


V2 is a rebase.

> A long time ago I submitted a pgbench \into command to store results of
> queries into variables independently of the query being processed, which got
> turn into \gset (;) and \cset (\;), which got committed, then \cset was
> removed because it was not "up to standard", as it could not work with empty
> query (the underlying issue is that pg silently skips empty queries, so that
> "\; SELECT 1 \; \; SELECT 3," returns 2 results instead of 4, a misplaced
> optimisation from my point of view).
>
> Now there is a pgbench \gset which allows to extract the results of variables
> of the last query, but as it does both setting and ending a query at the same
> time, there is no way to set variables out of a combined (\;) query but the
> last, which is the kind of non orthogonal behavior that I dislike much.
>
> This annoys me because testing the performance of combined queries cannot be
> tested if the script needs to extract variables.
>
> To make the feature somehow accessible to combined queries, the attached
> patch adds the "\aset" (all set) command to store all results of queries
> which return just one row into variables, i.e.:
>
> SELECT 1 AS one \;
> SELECT 2 AS two UNION SELECT 2 \;
> SELECT 3 AS three \aset
>
> will set both "one" and "three", while "two" is not set because there were
> two rows. It is a kind of more permissive \gset.
>
> Because it does it for all queries, there is no need for synchronizing with
> the underlying queries, which made the code for \cset both awkward and with
> limitations. Hopefully this version might be "up to standard".
> I'll see. I'm in no hurry:-)
>
>

--
Fabien.

Attachment Content-Type Size
pgbench-aset-2.patch text/x-diff 8.5 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Fabien COELHO 2019-05-23 14:16:35 Re: [HACKERS] Re: [COMMITTERS] pgsql: Remove pgbench "progress" test pending solution of its timing is (fwd)
Previous Message Robert Haas 2019-05-23 13:52:41 Re: "long" type is not appropriate for counting tuples