Re: pgbench - allow to store select results into variables - Mailing list pgsql-hackers

From Fabien COELHO
Subject Re: pgbench - allow to store select results into variables
Date
Msg-id alpine.DEB.2.20.1607140925400.1962@sto
Whole thread Raw
In response to Re: pgbench - allow to store select results into variables  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
Hello Tom,

>>> SELECT 1, 2 \; SELECT 3;
>>> \into one two three
>
> Yeah, that's seriously nasty action-at-a-distance in my view.  I'd be okay
> with
>
> SELECT 1, 2 \into one two
> SELECT 3 \into three

ISTM that is not the same, because then you would have two queries (over 
the network) instead of one, so you pay the network latency twice?

> but I do not think that a metacommand on a following line should
> retroactively affect the execution of a prior command, much less commands
> before the last one.

Nope. The meta-command applies to the preceeding SQL command... which 
happens to be a \;-compound command. ISTM that all is logically fine.


Some motivation about the feature (not its syntax or implementation), from 
a benchmarking perspective:

- clients MUST read the server answers and possibly reuse them, hence a 
proposed \into feature. Discarding the answer as pgbench does not really 
comply with typical benchmark rules, eg from tpc-b:
  """1.3.2 Each transaction shall return to the driver the Account_Balance     resulting from successful commit of the
transaction.
  Comment: It is the intent of this clause that the account balance in the  database be returned to the driver, i.e.,
thatthe application retrieve  the account balance."""
 

- latency is important to applications (eg web applications), thus the 
ability to compound statements is a good thing. However, if in a bench one 
can compound statements but not retrieve their values, it fails the 
previous "retrieve the value" requirement.

So basically I wish to avoid splitting compound queries and paying latency 
just because of a lack of syntax to do the right thing, hence the proposed 
feature which can retrieve data from various parts of a compound 
statement.

-- 
Fabien.



pgsql-hackers by date:

Previous
From: Dilip Kumar
Date:
Subject: Re: Issue in pg_catalog.pg_indexes view definition
Next
From: Amit Langote
Date:
Subject: Re: Issue in pg_catalog.pg_indexes view definition