psql: Add command to use extended query protocol

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/

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?

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?

st 2. 11. 2022 v 13:43 odesílatel Jehan-Guillaume de Rorthais <
jgdr@dalibo.com> napsal:

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?

I don't think it is possible. The variable evaluation is done before
parsing the backslash command.

Regards

Pavel

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?

On Fri, Nov 4, 2022 at 11:45 AM Peter Eisentraut <
peter.eisentraut@enterprisedb.com> wrote:

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?

A few:

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

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.

If that proves difficult, we might save bind params like registers

something like this, positional:

\bind 1 'param1'
\bind 2 'param2'
SELECT * FROM foo WHERE type_id = $1 AND cost > $2 \g filename.csv
\unbind

or all the binds on one line

\bindmany 'param1' 'param2'
SELECT * FROM foo WHERE type_id = $1 AND cost > $2 \g filename.csv
\unbind

Then psql would merely have to check if it had any bound registers, and if
so, the next query executed is extended query protocol, and \unbind wipes
out the binds to send us back to regular mode.

so 5. 11. 2022 v 7:35 odesílatel Corey Huinker <corey.huinker@gmail.com>
napsal:

On Fri, Nov 4, 2022 at 11:45 AM Peter Eisentraut <
peter.eisentraut@enterprisedb.com> wrote:

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?

A few:

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

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.

If that proves difficult, we might save bind params like registers

something like this, positional:

\bind 1 'param1'
\bind 2 'param2'
SELECT * FROM foo WHERE type_id = $1 AND cost > $2 \g filename.csv
\unbind

or all the binds on one line

\bindmany 'param1' 'param2'
SELECT * FROM foo WHERE type_id = $1 AND cost > $2 \g filename.csv
\unbind

Then psql would merely have to check if it had any bound registers, and if
so, the next query executed is extended query protocol, and \unbind wipes
out the binds to send us back to regular mode.

what about introduction new syntax for psql variables that should be passed
as bind variables.

like

SELECT * FROM foo WHERE x = $x \g

any time when this syntax can be used, then extended query protocol will be
used

and without any variable, the extended query protocol can be forced by psql
config variable

like

\set EXTENDED_QUERY_PROTOCOL true
SELECT 1;

Regards

Pavel

what about introduction new syntax for psql variables that should be
passed as bind variables.

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.

It's unclear from your example if that's what you meant, or if you wanted
actual named variables ($name, $timestamp_before, $x).

Actual named variables might cause problems with CREATE FUNCTION AS ...
$body$ ... $body$; as well as the need to deduplicate them.

So while it is less seamless, I do like the \bind x y z \g idea because it
requires no changes in variable interpolation, and the list can be
terminated with a slash command or ;

To your point about forcing extended query protocol even when no parameters
are, that would be SELECT 1 \bind \g

It hasn't been discussed, but the question of how to handle output
parameters seems fairly straightforward: the value of the bind variable is
the name of the psql variable to be set a la \gset.

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

On Mon, Nov 7, 2022 at 4:12 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

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

I think Pavel was hinting at something like:

\set $1 foo
\set $2 123
UPDATE mytable SET value = $1 WHERE id = $2;

Which wouldn't step on anything, because I tested it, and \set $1 foo
already returns 'Invalid variable name "$1"'.

So far, there seem to be two possible variations on how to go about this:

1. Have special variables or a variable namespace that are known to be bind
variables. So long as one of them is defined, queries are sent using
extended query protocol.
2. Bind parameters one-time-use, applied strictly to the query currently in
the buffer in positional order, and once that query is run their
association with being binds is gone.

Each has its merits, I guess it comes down to how much we expect users to
want to re-use some or all the bind params of the previous query.

út 8. 11. 2022 v 3:47 odesílatel Corey Huinker <corey.huinker@gmail.com>
napsal:

On Mon, Nov 7, 2022 at 4:12 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

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

I think Pavel was hinting at something like:

\set $1 foo
\set $2 123
UPDATE mytable SET value = $1 WHERE id = $2;

no, I just proposed special syntax for variable usage like bind variable

like

\set var Ahoj

SELECT $var;

I think so there should not be problem with custom strings, because we are
able to push $x to stored procedures, so it should be safe to use it
elsewhere

We can use the syntax @var - that is used by pgadmin

Regards

Pavel

On Mon, Nov 7, 2022 at 9:02 PM Pavel Stehule <pavel.stehule@gmail.com>
wrote:

út 8. 11. 2022 v 3:47 odesílatel Corey Huinker <corey.huinker@gmail.com>
napsal:

On Mon, Nov 7, 2022 at 4:12 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

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

I think Pavel was hinting at something like:

\set $1 foo
\set $2 123
UPDATE mytable SET value = $1 WHERE id = $2;

no, I just proposed special syntax for variable usage like bind variable

like

\set var Ahoj

SELECT $var;

Why not extend psql conventions for variable specification?

SELECT :$var$;

Thus:
:var => Ahoj
:'var' => 'Ahoj'
:"var" => "Ahoj"
:$var$ => $n (n => <Ahoj>)

The downside is it looks like dollar-quoting but isn't actually causing
<$Ahoj$> to be produced. Instead psql would have to substitute $n at that
location and internally remember that for this query $1 is the contents of
var.

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

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.

David J.