pgbench - add \aset to store results of a combined query - Mailing list pgsql-hackers

From Fabien COELHO
Subject pgbench - add \aset to store results of a combined query
Date
Msg-id alpine.DEB.2.21.1904081914200.2529@lancre
Whole thread Raw
Responses Re: pgbench - add \aset to store results of a combined query
List 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

pgsql-hackers by date:

Previous
From: Andres Freund
Date:
Subject: Re: lazy_scan_heap() forgets to mark buffer dirty when setting allfrozen?
Next
From: Alvaro Herrera
Date:
Subject: Re: change password_encryption default to scram-sha-256?