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: pgbench - add \aset to store results of a combined query
Date: 2019-04-08 17:32:51
Message-ID: alpine.DEB.2.21.1904081914200.2529@lancre
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers


Hello devs,

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-1.patch text/x-diff 8.6 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2019-04-08 17:34:12 Re: change password_encryption default to scram-sha-256?
Previous Message Andres Freund 2019-04-08 17:14:17 Re: lazy_scan_heap() forgets to mark buffer dirty when setting all frozen?