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

On Wed, Jul 10, 2019 at 11:33 AM Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr> wrote:

>
> Hello Ibrar,
>
> >> 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.
> >
> > Are you sure two is not set :)?
> >
> > SELECT 2 AS two UNION SELECT 2; -- only returns one row.
> > but
> > SELECT 2 AS two UNION SELECT 10; -- returns the two rows.
>
> Indeed, my intension was to show an example like the second.
>
> > Is this the expected behavior with \aset?
>
> > In my opinion throwing a valid error like "client 0 script 0 command 0
> > query 0: expected one row, got 2" make more sense.
>
> Hmmm. My intention with \aset is really NOT to throw an error. With
> pgbench, the existence of the variable can be tested later to know whether
> it was assigned or not, eg:
>
> SELECT 1 AS x \;
> -- 2 rows, no assignment
> SELECT 'calvin' AS firstname UNION SELECT 'hobbes' \;
> SELECT 2 AS z \aset
> -- next test is false
> \if :{?firstname}
> ...
> \endif
>
> The rational is that one may want to benefit from combined queries (\;)
> which result in less communication thus has lower latency, but still be
> interested in extracting some results.
>
> The question is what to do if the query returns 0 or >1 rows. If an error
> is raised, the construct cannot be used for testing whether there is one
> result or not, eg for a query returning 0 or 1 row, you could not write:
>
> \set id random(1, :number_of_users)
> SELECT firtname AS fn FROM user WHERE id = :id \aset
> \if :{?fn}
> -- the user exists, proceed with further queries
> \else
> -- no user, maybe it was removed, it is not an error
> \endif
>
> Another option would to just assign the value so that
> - on 0 row no assignment is made, and it can be tested afterwards.
> - on >1 rows the last (first?) value is kept. I took last so to
> ensure that all results are received.
>
> I think that having some permissive behavior allows to write some more
> interesting test scripts that use combined queries and extract values.
>
> What do you think?
>
> Yes, I think that make more sense.

> > - With \gset
> >
> > SELECT 2 AS two UNION SELECT 10 \gset
> > INSERT INTO test VALUES(:two,0,0);
> >
> > client 0 script 0 command 0 query 0: expected one row, got 2
> > Run was aborted; the above results are incomplete.
>
> Yes, that is the intented behavior.
>
> > - With \aset
> >
> > SELECT 2 AS two UNION SELECT 10 \aset
> > INSERT INTO test VALUES(:two,0,0);
> > [...]
> > client 0 script 0 aborted in command 1 query 0: ERROR: syntax error at
> or near ":"
>
> Indeed, the user should test whether the variable was assigned before
> using it if the result is not warranted to return one row.
>
> > The new status of this patch is: Waiting on Author
>
> The attached patch implements the altered behavior described above.
>
> --
> Fabien.

--
Ibrar Ahmed