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

Hello Amit,

> [...]
> There still seems to be a change in behavior of the -r option due to the
> patch. Consider the following example:
>
> select a from a where a = 1 \;
> select a+1 from a where a = 1;
> ...
> - statement latencies in milliseconds:
>         2.889  select a from a where a = 1 ;

vs

> select a from a where a = 1 \; \into a
> select a+1 from a where a = 1; \into b
> ...
>         2.093  select a from a where a = 1 ; select a+1 from a where a = 1;

Yep.

Note that there is a small logical conumdrum in this argument: As the
script is not the same, especially as into was not possible before,
strictly speaking there is no behavior "change".

This said, what you suggest can be done.

After giving it some thought, I suggest that it is not needed nor
desirable. If you want to achieve the initial effect, you just have to put
the "into a" on the next line:

   select a from a where a = 1 \;
   \into a
   select a+1 from a where a = 1; \into b

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.

> One more thing I observed which I am not sure if it's a fault of this
> patch is illustrated below:
>
> $ cat /tmp/into.sql
> \;
> select a from a where a = 1 \;
> select a+1 from a where a = 1;
>
> $ pgbench -r -n -t 1 -f /tmp/into.sql postgres
> <snip>
> - statement latencies in milliseconds:
>         2.349  ;
>
> Note that the compound select statement is nowhere to be seen in the
> latencies output. The output remains the same even if I use the \into's.
> What seems to be going on is that the empty statement on the first line
> (\;) is the only part kept of the compound statement spanning lines 1-3.

Yes.

This is really the (debatable) current behavior, and is not affected by
the patch. The "-r" summary takes the first line of the command, whatever
it is. In your example the first line is "\;", so you get what you asked
for, even if it looks rather strange, obviously.

>>> +    bool        sql_command_in_progress = false;
> [...]
> I understood that it refers to what you explain here.  But to me it
> sounded like the name is referring to the progress of *execution* of a SQL
> command whereas the code in question is simply expecting to finish
> *parsing* the SQL command using the next lines.

Ok. I changed it "sql_command_lexing_in_progress".

>> The attached patch takes into all your comments but:
>>  - comment about discarded results...
>>  - the sql_command_in_progress variable name change
>>  - the longer message on into at the start of a script
>
> The patch seems fine without these, although please consider the concern I
> raised with regard to the -r option above.

I have considered it. As the legitimate behavior you suggested can be
achieved just by putting the into on the next line, ISTM that the current
proposition gives more control than doing a mandatory cut when into is
used.

Attached is a new version with the boolean renaming.

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.

--
Fabien.