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

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?

> - 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.