Re: [HACKERS] pgbench - allow to store select results into variables

Hello Alvaro,

Thanks for having a look at this patch.

>> Think of one initialization followed by two appends:
>>
>> SELECT 1 AS x \cset
>> SELECT 2 \; SELECT 3 AS y \cset
>> SELECT 4 \; SELECT 5 \; SELECT 6 AS z \gset
>>
>> In the end, we must have the full 6 queries
>>
>> "SELECT 1 AS x \; SELECT 2 \; SELECT 3 AS y \; SELECT 4 \; SELECT 5 \; SELECT 6 AS z"
>>
>> and know that we want to set variables from queries 1, 3 and 6 and ignore
>> the 3 others.
>
> I'm not sure I understand this. Why is the "SELECT 2" ignored?

Because there is no \cset nor \gset attached to it, so the command does
not say to put the result into variables.

> (I can see why the 4 and 5 are ignored: they are not processed by gset).

Same thing with SELECT 2, which is followed by "\;", meaning execute and
that's all.

> What exactly does \cset do? I thought "SELECT 2 \; SELECT 3 AS y \cset"
> would search for the \; and process *both* queries.

No, "\cset" does not end the compound query, only ";" or "\gset" do that.

\cset separates queries (like \;) and adds the fact that the just
preceding query result is to be put into variables when received.

\cset = \; + store result into variables

\gset = ; + store result into variables

From a performance point of view, the point is to be able to use compound
queries which reduce the number of round trips so should impact latency.

> I think the doc addition should be split.

Indeed, as the current version is confusing.

Attached an attempt at clarifying the documentation on this point. The doc
is split as suggested, descriptions and examples are specific to each
presented case.

--
Fabien.