Thread: psql: Add command to use extended query protocol

This adds a new psql command \gp that works like \g (or semicolon) but
uses the extended query protocol.  Parameters can also be passed, like

     SELECT $1, $2 \gp 'foo' 'bar'

I have two main purposes for this:

One, for transparent column encryption [0], we need a way to pass 
protocol-level parameters.  The present patch in the [0] thread uses a 
command \gencr, but based on feedback and further thinking, a 
general-purpose command seems better.

Two, for testing the extended query protocol from psql.  For example, 
for the dynamic result sets patch [1], I have several ad-hoc libpq test 
programs lying around, which would be cumbersome to integrate into the 
patch.  With psql support like proposed here, it would be very easy to 
integrate a few equivalent tests.

Perhaps this would also be useful for general psql scripting.


[0]: https://commitfest.postgresql.org/40/3718/
[1]: https://commitfest.postgresql.org/40/2911/

On Fri, Oct 28, 2022 at 08:52:51AM +0200, Peter Eisentraut wrote:
> Two, for testing the extended query protocol from psql.  For example, for
> the dynamic result sets patch [1], I have several ad-hoc libpq test programs
> lying around, which would be cumbersome to integrate into the patch.  With
> psql support like proposed here, it would be very easy to integrate a few
> equivalent tests.

+1.  As far as I recall, we now have only ECPG to rely on when it
comes to coverage of the extended query protocol, but even that has
its limits.  (Haven't looked at the patch)
--
Michael

On Fri, Oct 28, 2022 at 08:52:51AM +0200, Peter Eisentraut wrote:
> Perhaps this would also be useful for general psql scripting.

+1

It makes great sense to that psql would support it (I've suggested to a
few people over the last few years to do that using pygres, lacking an
easier way).

I wondered briefly if normal \g should change to use the extended
protocol.  But there ought to be a way to do both/either, so it's better
how you wrote it.

On Fri, Oct 28, 2022 at 04:07:31PM +0900, Michael Paquier wrote:
> +1.  As far as I recall, we now have only ECPG to rely on when it
> comes to coverage of the extended query protocol, but even that has
> its limits.  (Haven't looked at the patch)

And pgbench (see 1ea396362)

-- 
Justin

Michael Paquier <michael@paquier.xyz> writes:
> On Fri, Oct 28, 2022 at 08:52:51AM +0200, Peter Eisentraut wrote:
>> Two, for testing the extended query protocol from psql.  For example, for
>> the dynamic result sets patch [1], I have several ad-hoc libpq test programs
>> lying around, which would be cumbersome to integrate into the patch.  With
>> psql support like proposed here, it would be very easy to integrate a few
>> equivalent tests.

> +1.  As far as I recall, we now have only ECPG to rely on when it
> comes to coverage of the extended query protocol, but even that has
> its limits.  (Haven't looked at the patch)

pgbench can be used too, but we lack any infrastructure for using it
in the regression tests.  Something in psql could be a lot more
helpful.  (I've not studied the patch either.)

            regards, tom lane

On Fri, 28 Oct 2022 at 07:53, Peter Eisentraut
<peter.eisentraut@enterprisedb.com> wrote:
>
> This adds a new psql command \gp that works like \g (or semicolon) but
> uses the extended query protocol.  Parameters can also be passed, like
>
>      SELECT $1, $2 \gp 'foo' 'bar'

+1 for the concept. The patch looks simple and complete.

I find it strange to use it the way you have shown above, i.e. \gp on
same line after a query.

For me it would be clearer to have tests and docs showing this
  SELECT $1, $2
  \gp 'foo' 'bar'

> Perhaps this would also be useful for general psql scripting.

...since if we used this in a script, it would be used like this, I think...

  SELECT $1, $2
  \gp 'foo' 'bar'
  \gp 'bar' 'baz'
  ...

-- 
Simon Riggs                http://www.EnterpriseDB.com/

On 01.11.22 10:10, Simon Riggs wrote:
> On Fri, 28 Oct 2022 at 07:53, Peter Eisentraut
> <peter.eisentraut@enterprisedb.com> wrote:
>>
>> This adds a new psql command \gp that works like \g (or semicolon) but
>> uses the extended query protocol.  Parameters can also be passed, like
>>
>>       SELECT $1, $2 \gp 'foo' 'bar'
> 
> +1 for the concept. The patch looks simple and complete.
> 
> I find it strange to use it the way you have shown above, i.e. \gp on
> same line after a query.

That's how all the "\g" commands work.

> ...since if we used this in a script, it would be used like this, I think...
> 
>    SELECT $1, $2
>    \gp 'foo' 'bar'
>    \gp 'bar' 'baz'
>    ...

Interesting, but I think for that we should use named prepared 
statements, so that would be a separate "\gsomething" command in psql, like

     SELECT $1, $2 \gprep p1
     \grun p1 'foo' 'bar'
     \grun p1 'bar' 'baz'

On Tue, 1 Nov 2022 at 20:48, Peter Eisentraut
<peter.eisentraut@enterprisedb.com> wrote:
>
> On 01.11.22 10:10, Simon Riggs wrote:
> > On Fri, 28 Oct 2022 at 07:53, Peter Eisentraut
> > <peter.eisentraut@enterprisedb.com> wrote:
> >>
> >> This adds a new psql command \gp that works like \g (or semicolon) but
> >> uses the extended query protocol.  Parameters can also be passed, like
> >>
> >>       SELECT $1, $2 \gp 'foo' 'bar'
> >
> > +1 for the concept. The patch looks simple and complete.
> >
> > I find it strange to use it the way you have shown above, i.e. \gp on
> > same line after a query.
>
> That's how all the "\g" commands work.

Yes, I see that, but it also works exactly the way I said also.

i.e.
SELECT 'foo'
\g

is the same thing as

SELECT 'foo' \g

But there are no examples in the docs of the latter usage, and so it
is a surprise to me and probably to others also

> > ...since if we used this in a script, it would be used like this, I think...
> >
> >    SELECT $1, $2
> >    \gp 'foo' 'bar'
> >    \gp 'bar' 'baz'
> >    ...
>
> Interesting, but I think for that we should use named prepared
> statements, so that would be a separate "\gsomething" command in psql, like
>
>      SELECT $1, $2 \gprep p1
>      \grun p1 'foo' 'bar'
>      \grun p1 'bar' 'baz'

Not sure I understand this... you seem to be arguing against your own
patch?? I quite liked the way you had it, I'm just asking for the docs
to put the \gp on the following line.

-- 
Simon Riggs                http://www.EnterpriseDB.com/

Hi,

On Fri, 28 Oct 2022 08:52:51 +0200
Peter Eisentraut <peter.eisentraut@enterprisedb.com> wrote:

> This adds a new psql command \gp that works like \g (or semicolon) but
> uses the extended query protocol.  Parameters can also be passed, like
> 
>      SELECT $1, $2 \gp 'foo' 'bar'

As I wrote in my TCE review, would it be possible to use psql vars to set some
named parameters for the prepared query? This would looks like:

  \set p1 foo
  \set p2 bar
  SELECT :'p1', :'p2' \gp

This seems useful when running psql script passing it some variables using
-v arg. It helps with var position, changing some between exec, repeating them
in the query, etc.

Thoughts?

    Jehan-Guillaume de Rorthais wrote:

> As I wrote in my TCE review, would it be possible to use psql vars to set
> some
> named parameters for the prepared query? This would looks like:
>
>  \set p1 foo
>  \set p2 bar
>  SELECT :'p1', :'p2' \gp

As I understand the feature, variables would be passed like this:

\set var1 'foo bar'
\set var2 'baz''qux'

select $1, $2 \gp :var1 :var2

 ?column? | ?column?
----------+----------
 foo bar  | baz'qux

It appears to work fine with the current patch.

This is consistent with the fact that PQexecParams passes $N
parameters ouf of the SQL query (versus injecting them in the text of
the query) which is also why no quoting is needed.


Best regards,
--
Daniel Vérité
https://postgresql.verite.pro/
Twitter: @DanielVerite

On Wed, 02 Nov 2022 16:04:02 +0100
"Daniel Verite" <daniel@manitou-mail.org> wrote:

>     Jehan-Guillaume de Rorthais wrote:
> 
> > As I wrote in my TCE review, would it be possible to use psql vars to set
> > some named parameters for the prepared query? This would looks like:
> > 
> >  \set p1 foo
> >  \set p2 bar
> >  SELECT :'p1', :'p2' \gp  
> 
> As I understand the feature, variables would be passed like this:
> 
> \set var1 'foo bar'
> \set var2 'baz''qux'
> 
> select $1, $2 \gp :var1 :var2
> 
>  ?column? | ?column? 
> ----------+----------
>  foo bar  | baz'qux
> 
> It appears to work fine with the current patch.

Indeed, nice.

> This is consistent with the fact that PQexecParams passes $N
> parameters ouf of the SQL query (versus injecting them in the text of
> the query)

I was not thinking about injecting them in the texte of the query, this
would not be using the extended protocol anymore, or maybe with no parameter,
but there's no point.

What I was thinking about is psql replacing the variables from the query text
with the $N notation before sending it using PQprepare.

> which is also why no quoting is needed.

Indeed, the quotes were not needed in my example.

Thanks,

On 02.11.22 01:18, Corey Huinker wrote:
> 
>           SELECT $1, $2 \gp 'foo' 'bar'
> 
> 
> I think this is a great idea, but I foresee people wanting to send that 
> output to a file or a pipe like \g allows. If we assume everything after 
> the \gp is a param, don't we paint ourselves into a corner?

Any thoughts on how that syntax could be generalized?

Corey Huinker <corey.huinker@gmail.com> writes:
> I thought about basically reserving the \$[0-9]+ space as bind variables,
> but it is possible, though unlikely, that users have been naming their
> variables like that.

Don't we already reserve that syntax as Params?  Not sure whether there
would be any conflicts versus Params, but these are definitely not legal
as SQL identifiers.

            regards, tom lane

    David G. Johnston wrote:

> I would keep the \gp meta-command to force extended mode regardless
> of whether the query itself requires it.

+1

> A pset variable to control the default seems reasonable as well.
> The implication would be that if you set that pset variable there is
> no way to have individual commands use simple query mode directly.

+1 except that it would be a \set variable for consistency with the
other execution-controlling variables. \pset variables control only
the display.

BTW if we wanted to auto-detect that a query requires binding or the
extended query protocol, we need to keep in mind that for instance
"PREPARE stmt AS $1" must pass without binding, with both the simple
and the extended query protocol.


Best regards,
--
Daniel Vérité
https://postgresql.verite.pro/
Twitter: @DanielVerite

On 05.11.22 07:34, Corey Huinker wrote:
> The most compact idea I can think of is to have \bind and \endbind (or 
> more terse equivalents \bp and \ebp)
> 
> SELECT * FROM foo WHERE type_id = $1 AND cost > $2 \bind 'param1' 
> 'param2' \endbind $2 \g filename.csv

I like it.  It makes my code even simpler, and it allows using all the 
different \g variants transparently.  See attached patch.

> Maybe the end-bind param isn't needed at all, we just insist that bind 
> params be single quoted strings or numbers, so the next slash command 
> ends the bind list.

Right, the end-bind isn't needed.

Btw., this also allows doing things like

SELECT $1, $2
\bind '1' '2' \g
\bind '3' '4' \g

This isn't a prepared statement being reused, but it relies on the fact 
that psql \g with an empty query buffer resends the previous query. 
Still kind of neat.

On 08.11.22 13:02, Daniel Verite wrote:
>> A pset variable to control the default seems reasonable as well.
>> The implication would be that if you set that pset variable there is
>> no way to have individual commands use simple query mode directly.
> +1 except that it would be a \set variable for consistency with the
> other execution-controlling variables. \pset variables control only
> the display.

Is there a use case for a global setting?

It seems to me that that would be just another thing that a 
super-careful psql script would have to reset to get a consistent 
starting state.

    Peter Eisentraut wrote:

> Is there a use case for a global setting?

I assume that we may sometimes want to use the
extended protocol on all queries of a script, like
pgbench does with --protocol=extended.
Outside of psql, it's too complicated to parse a SQL script to
replace the end-of-query semicolons with \gp, whereas
a psql setting solves this effortlessly.


Best regards,
--
Daniel Vérité
https://postgresql.verite.pro/
Twitter: @DanielVerite

On 09.11.22 20:10, Daniel Verite wrote:
>     Peter Eisentraut wrote:
> 
>> Is there a use case for a global setting?
> 
> I assume that we may sometimes want to use the
> extended protocol on all queries of a script, like
> pgbench does with --protocol=extended.

But is there an actual use case for this in psql?  In pgbench, there are 
scenarios where you want to test aspects of prepared statements, plan 
caching, and so on.  Is there something like that for psql?

    Peter Eisentraut wrote:

> > I assume that we may sometimes want to use the
> > extended protocol on all queries of a script, like
> > pgbench does with --protocol=extended.
>
> But is there an actual use case for this in psql?  In pgbench, there are
> scenarios where you want to test aspects of prepared statements, plan
> caching, and so on.  Is there something like that for psql?

If we set aside "exercising the protocol" as not an interesting use case
for psql, then no, I can't think of any benefit.


Best regards,
--
Daniel Vérité
https://postgresql.verite.pro/
Twitter: @DanielVerite

On 09.11.22 00:12, Corey Huinker wrote:
> As for the docs, they're very clear and probably sufficient as-is, but I 
> wonder if we should we explicitly state that the bind-state and bind 
> parameters do not "stay around" after the query is executed? Suggestions 
> in bold:
> 
>           This command causes the extended query protocol (see <xref
>           linkend="protocol-query-concepts"/>) to be used, unlike normal
>           <application>psql</application> operation, which uses the simple
>           query protocol. *Extended query protocol will be used* *even 
> if no parameters are specified, s*o this command can be useful to test 
> the extended
>           query protocol from psql. *This command affects only the next 
> query executed, all subsequent queries will use the regular query 
> protocol by default.*
> 
> Tests seem comprehensive. I went looking for the TAP test that this 
> would have replaced, but found none, and it seems the only test where we 
> exercise PQsendQueryParams is libpq_pipeline.c, so these tests are a 
> welcome addition.
> 
> Aside from the possible doc change, it looks ready to go.

Committed with those doc changes.  Thanks.

On 21.11.22 23:02, Corey Huinker wrote:
> I got thinking about this, and while things may be fine as-is, I would 
> like to hear some opinions as to whether this behavior is correct:

This is all psql syntax, nothing specific to this command.  The only 
leeway is choosing the appropriate enum slash_option_type, but the 
choices other than OT_NORMAL don't seem to be particularly applicable to 
this.

In one of my environments, this feature didn't work as expected. Digging into it, I found that it is incompatible with
FETCH_COUNTbeing set. Sorry for not recognising this during the betas. 

Attached a simple patch with tests running the cursor declaration through PQexecParams instead of PGexec.

Alternatively, we could avoid going to ExecQueryUsingCursor and force execution via ExecQueryAndProcessResults in
SendQuery(around line 1134 in src/bin/psql/common.c) when \bind is used: 

    else if (pset.fetch_count <= 0 || pset.gexec_flag ||
-             pset.crosstab_flag || !is_select_command(query))
+             pset.crosstab_flag || !is_select_command(query) ||
+             pset.bind_flag)

best regards
Tobias

On 2023-Sep-14, Tobias Bussmann wrote:

> In one of my environments, this feature didn't work as expected.
> Digging into it, I found that it is incompatible with FETCH_COUNT
> being set. Sorry for not recognising this during the betas.
> 
> Attached a simple patch with tests running the cursor declaration
> through PQexecParams instead of PGexec.

Hmm, strange.  I had been trying to make \bind work with extended
protocol, and my findings were that there's interactions with the code
that was added for pipeline mode(*).  I put research aside to work on
other things, but intended to get back to it soon ... I'm really
surprised that it works for you here.

Maybe your tests are just not extensive enough to show that it fails.

(*) This is not actually proven, but Peter had told me that his \bind
stuff had previously worked when he first implemented it before pipeline
landed.  Because that's the only significant change that has happened to
the libpq code lately, it's a reasonable hypothesis.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"No deja de ser humillante para una persona de ingenio saber
que no hay tonto que no le pueda enseñar algo." (Jean B. Say)

Hi,

> > In one of my environments, this feature didn't work as expected.
> > Digging into it, I found that it is incompatible with FETCH_COUNT
> > being set. Sorry for not recognising this during the betas.
> >
> > Attached a simple patch with tests running the cursor declaration
> > through PQexecParams instead of PGexec.
>
> Hmm, strange.  I had been trying to make \bind work with extended
> protocol, and my findings were that there's interactions with the code
> that was added for pipeline mode(*).  I put research aside to work on
> other things, but intended to get back to it soon ... I'm really
> surprised that it works for you here.
>
> Maybe your tests are just not extensive enough to show that it fails.
>
> (*) This is not actually proven, but Peter had told me that his \bind
> stuff had previously worked when he first implemented it before pipeline
> landed.  Because that's the only significant change that has happened to
> the libpq code lately, it's a reasonable hypothesis.

A colleague of mine is very excited about the new \bind functionality
in psql. However he is puzzled by the fact that there is no obvious
way to bind a NULL value, except for something like:

```
create table t (v text);
insert into t values (case when $1 = '' then NULL else $1 end) \bind '' \g
select v, v is null from t;
```

Maybe we should also support something like ... \bind val1 \null val3 \g ?

-- 
Best regards,
Aleksander Alekseev