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

Hello Amit,

>> [...]
>> Then you would get the -r cut at the end of the compound command. Thus the
>> current version gives full control of what will appear in the summary. If
>> I change "\into xxx\n" to mean "also cut here", then there is less control
>> on when the cut occurs when into is used.
>
> So it means that position of \into affects where a compound command gets
> cut for -r display.  I was just wondering if that was unintentional.

Yes, but it happens to work reasonnably:-)

>> The other thing I have considered is whether to implemented a "\gset"
>> syntax, as suggested by Pavel and Tom. Bar the aesthetic, the main issue I
>> have with it is that it does not work with compound commands, and what I
>> want is to get the values out of compound commands... because of my focus
>> on latency... so basically "\gset" does not do the job I want... Now I
>> recognize that other people would like it, so probably I'll do it anyway
>> in another patch.
>
> So, psql's \gset does not work as desired for compound commands (viz. it
> is only able to save the result of the last sub-command).

Yes.

> You need to use \gset with each sub-command separately if no result 
> should be discarded. Because of undesirable latency characteristics of 
> sending multiple commands, you want to be able to modify compound 
> command handling such that every sub-command's result could be saved.

Exactly.

> In that regard, it's good that pgbench does not use PQexec() which only 
> returns the result of the last sub-command if a compound command was 
> issued through it.

Indeed!

>  pgbench's
> doCustom() currently discards all results by issuing discard_response().
> You propose to change things such that results are intercepted in a new
> function read_response(), values assigned to intovars corresponding to
> each sub-command, and then discarded. The intovars arrays are allocated
> within each sub-command's Command struct when parsing the compound command
> based on where \into is located wrt to the sub-command.

Yep.

> So, most of the code in the patch is about handling compound statements to
> be be able to save results of all sub-commands, not just the last.

Yep. Previously pgbench did not need to handle compounds commands which 
where just seen as one large string.

Note that the added machinery is also a first step to allow the handling 
of prepared statements on compounds command, which I think is a desirable 
feature for benchmarks.

> Do you think it would be OK to suffer the bad latency of multiple round 
> trips and implement a version of \into (or \gset or \ginto) for pgbench 
> scripts that behaves exactly like psql's \gset as a first step?

I do not see gset as a reasonnable "first step" because: (0) "\into" 
already works while "\gset" in pgbench will need some time that I do not 
have at the moment (1) it is not what I want/need to do a clean bench (2) 
the feature is not orthogonal to compounds statements -- which is what I 
want -- (3) I do not like the "implicit" naming thing -- but this is 
really just a matter of taste.

I simply recognize that Peter & Tom have a point: whatever I think of gset 
it is there in "psql" so it makes some sense to have it as well in 
"pgbench", so I agree to put that on my pgbench todo list.

> But you say you will do it as another patch.

Yep. I suggested another patch because it is a different feature and 
previous submissions where I mixed features, even closely related ones, 
all resulted in me having to separate the features in distinct patches.

> By the way, I tend to agree with your point about \gset syntax being
> suitable (only) in a interactive context such as psql; it's not as
> readable as \into x y ... when used in a script.

Yep, but as people already know it, it makes sense to provide it as well 
at some point.

-- 
Fabien.