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

From: Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>
To: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
Cc: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, Tatsuo Ishii <ishii(at)sraoss(dot)co(dot)jp>, rafia(dot)sabih(at)enterprisedb(dot)com, Michael Paquier <michael(dot)paquier(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>, Robert Haas <robertmhaas(at)gmail(dot)com>, PostgreSQL Developers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [HACKERS] pgbench - allow to store select results into variables
Date: 2018-11-17 10:25:32
Message-ID: alpine.DEB.2.21.1811171039400.16105@lancre
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers


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.

Attachment Content-Type Size
pgbench-into-22.patch text/x-diff 27.4 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Fabien COELHO 2018-11-17 10:43:27 Re: [HACKERS] pgbench - allow to store select results into variables
Previous Message Amit Kapila 2018-11-17 06:07:05 Re: zheap: a new storage format for PostgreSQL