Thread: unexpected psql "feature"

Hello devs,

Although this is probably a "feature", it is a weird one:
 $ psql psql (9.6beta2) fabien=# SELECT 1 AS one \; fabien-# SELECT 2 AS two ;  two -----    2 (1 row)

Where is my "1"?

Although "\;" behavior is not documented, I would have expected both 
results to be shown one after the other, or having a an error, but not a 
quiet discard.

My guess is that psql uses PQexec which just returns the last result. 
Using PQsendQuery/PQgetResult would result in a much better behavior.
 fabien=# CREATE TABLE foo(id TEXT); CREATE TABLE fabien=# INSERT INTO foo VALUES('calvin') \; fabien-# INSERT INTO foo
VALUES('hobbes'); INSERT 0 1 fabien=# SELECT * FROM foo;    id --------  calvin  hobbes (2 rows)
 

I would suggest that: - the \; psql feature should be documented somewhere - all results should be shown, not just the
lastone
 

Any opinion?

-- 
Fabien.

Hello David,

At least we aggree that having a documentation would be an improvement:-)

On the second point:

>>  - all results should be shown, not just the last one
>
> disagree
>
> # select 1 ; select 2 ;

vs

> # select 1 \; select 2 ;
>
> Result in identical behavior seems undesirable.

In both cases there is the two same queries, so having the same results 
does not strike me as "undesirable", on the contrary.

> At least now if you want to discard all intermediate work and just show 
> the last statement you can do so without going to any great lengths. If 
> you really want both results don't use "\;".  This makes even more sense 
> when the earlier statements are DML instead of SELECT.

Hmmm. I do not buy this "\; executes a statement but does not show the 
results" as a sane and expected behavior.

I think that the underlying and only reason it behaves like this is that 
at the protocol level one can send a batch of queries in one go, but for 
the simple "PQexec" function just one result is returned, the last one was 
chosen probably as a marker that they were all executed, and that is all.

So I see this as a low-level simplified API detail which has an unforeseen 
user impact.

-- 
Fabien.

Fabien COELHO <coelho@cri.ensmp.fr> writes:
> Hello devs,

> Although this is probably a "feature", it is a weird one:

>   $ psql
>   psql (9.6beta2)
>   fabien=# SELECT 1 AS one \;
>   fabien-# SELECT 2 AS two ;
>    two
>   -----
>      2
>   (1 row)

> Where is my "1"?

> Although "\;" behavior is not documented, I would have expected both 
> results to be shown one after the other, or having a an error, but not a 
> quiet discard.

See the documentation for PQexec(): all but the last query result is
discarded.

> I would suggest that:
>   - the \; psql feature should be documented somewhere
>   - all results should be shown, not just the last one

> Any opinion?

I do not think changing this is appropriate.  All you are likely to
accomplish is breaking code that does what its author wanted.
        regards, tom lane

Hello Tom,

>> Although "\;" behavior is not documented, I would have expected both
>> results to be shown one after the other, or having a an error, but not a
>> quiet discard.
>
> See the documentation for PQexec(): all but the last query result is
> discarded.

Sure. That is developer-level answer to "why", although it does not really 
say why the developer chose PQexex over PQsendQuery. At the user-level the 
behavior is still pretty surprising.

>> I would suggest that:
>>   - the \; psql feature should be documented somewhere
>>   - all results should be shown, not just the last one
>
>> Any opinion?
>
> I do not think changing this is appropriate.  All you are likely to
> accomplish is breaking code that does what its author wanted.

Hmmm... My 0.02€: Currently this feature is NOT documented, so somehow it 
is not supported, and relying on it seems risky, as it is really a side 
effect of the current implementation. If it becomes documented, it could 
be made to behave sanely at the same time...

-- 
Fabien.

David G. Johnston wrote:
> On Wed, Jul 13, 2016 at 5:44 PM, Fabien COELHO <coelho@cri.ensmp.fr> wrote:
> 
> > Although "\;" behavior is not documented, I would have expected both
> >>> results to be shown one after the other, or having a an error, but not a
> >>> quiet discard.
> >>
> >> See the documentation for PQexec(): all but the last query result is
> >> discarded.
> >
> > Sure. That is developer-level answer to "why", although it does not really
> > say why the developer chose PQexex over PQsendQuery. At the user-level the
> > behavior is still pretty surprising.
> 
> ​Lets try putting it this way...
> 
> As a psql user I want some way to choose whether I send my query via
> "PQexec" or "PQsendQuery".  I'm not sure why the "PQexec" access point is
> undocumented but this "\;" syntax, vis-a-vis ";" provides me that choice.

psql splits the input string on semicolons and submits each resulting
part separately using PQexec.  Since \; defeats the splitting efforts,
what happens is that the whole tihng is submitted via PQexec() as a
single unit instead.  PQsendQuery is never used by psql.

Now PQexec is documented to return only the last resultset if you send
more than one query through it; so that part seems okay since it's been
documented this way forever.  However, psql is not documented to use
PQexec, it just happens to use it.

Now, I think requesting psql not to split query strings is a good
feature, but having it depend on using \; instead of ; seems way too
obscure.  If we want to offer that choice, I propose we do it via some
properly designed mechanism rather than being a random emergent
characteristic falling out of a bunch of historical coincidences.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> Now, I think requesting psql not to split query strings is a good
> feature, but having it depend on using \; instead of ; seems way too
> obscure.  If we want to offer that choice, I propose we do it via some
> properly designed mechanism rather than being a random emergent
> characteristic falling out of a bunch of historical coincidences.

I think it was designed to do that; if you look at the code in psqlscan.l
that causes this to happen, it's clearly intentional not a "random
emergent characteristic".

Personally, I'm fine with documenting this behavior and having done.
What I don't like is Fabien's suggestion that we alter the behavior.
It's possible that that'll break existing applications, and the argument
that printing rather than discarding the PQresult is better seems pretty
weak anyway.  Discarding a PQresult seems like it would have some uses.

Worth noting by the way is thatselect 1 \; select 2;
has the same behavior aspsql -c 'select 1; select 2;'
since in both cases the whole string is sent in one PQexec.  I wonder
whether promoting \; to a recognized and documented behavior would
allow us to get away with converting -c strings to normal parsing
behavior, as was discussed and then rejected on compatibility grounds
not too long ago.  People who need to keep the old behavior could do so
by putting in backslashes.
        regards, tom lane

Hello David,

> Why would you choose to execute "SELECT 1 \; SELECT 2;" instead of "SELECT
> 1; SELECT 2;"​ in a setup where the behavior of both strings is identical?
> Or, rather, how would they differ?

The answer is that at the protocol level the first one is one network
round trip with the server, the second is two.

The difference probably does not really matter that much for psql which is
more an interactive than a scripting tool.

However I'm really looking at that in the context of pgbench, which
exhibits the same behavior.

If you run a bench and have one round trip or several over a local
network, the latency is not the same. Consider a throttled read-only load
composed of 3 random selects, several rounds (so.sql) vs one (so2.sql):

  > pgbench -h server -T 100 -P 1 --rate 200 -c 2 -f so.sql -f so2.sql
  ...
  SQL script 1: so.sql
   - weight = 1 (targets 50.0% of total)
   - 10010 transactions (50.1% of total, tps = 100.101872)
   - latency average = 1.878 ms
   - latency stddev = 3.614 ms
  SQL script 2: so2.sql
   - weight = 1 (targets 50.0% of total)
   - 9954 transactions (49.9% of total, tps = 99.541861)
   - latency average = 1.089 ms
   - latency stddev = 3.022 ms

There is 0.8 ms latency reduction, that is a 40% difference. Standard
deviation is also significantly lower.

--
Fabien.

> Consider a throttled read-only load composed of 3 random selects, 
> several rounds (so.sql) vs one (so2.sql):
>
> > pgbench -h server -T 100 -P 1 --rate 200 -c 2 -f so.sql -f so2.sql
> SQL script 1: so.sql
>  - latency average = 1.878 ms
> SQL script 2: so2.sql
>  - latency average = 1.089 ms
>
> There is 0.8 ms latency reduction, that is a 40% difference. Standard 
> deviation is also significantly lower.

More fun with these two scripts, for those who focus on tps.
 > pgbench -h server -M prepared -T 10 -P 1 -c 8 -f so.sql ... latency average = 1.128 ms latency stddev = 0.167 ms tps
=7024.818252 (including connections establishing)
 
 > pgbench -h server -T 10 -P 1 -c 8 -f so2.sql # cannot do "-M prepared" on multiple commands... anyway: ... latency
average= 0.454 ms latency stddev = 0.110 ms tps = 17317.807554 (including connections establishing)
 

A mere 2.46 speedup for the read-only load.


The same with a "simple update" pgbench load, not compound & prepared:
  latency average = 1.716 ms  latency stddev = 1.890 ms  tps = 4623.270608 (including connections establishing)

vs compound but not prepared:
  latency average = 1.294 ms  latency stddev = 0.870 ms  tps = 6122.289249 (including connections establishing)

Ok, it is less impressive, just 30% better.

-- 
Fabien.