Thread
Commits
Same data as JSON:
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
psql: Add command to use extended query protocol
- 5b66de3433e2 16.0 landed
-
psql: Add command to use extended query protocol
Peter Eisentraut <peter.eisentraut@enterprisedb.com> — 2022-10-28T06:52:51Z
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/ -
Re: psql: Add command to use extended query protocol
Michael Paquier <michael@paquier.xyz> — 2022-10-28T07:07:31Z
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
-
Re: psql: Add command to use extended query protocol
Justin Pryzby <pryzby@telsasoft.com> — 2022-10-28T13:27:46Z
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
-
Re: psql: Add command to use extended query protocol
Tom Lane <tgl@sss.pgh.pa.us> — 2022-10-28T13:35:55Z
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
-
Re: psql: Add command to use extended query protocol
Simon Riggs <simon.riggs@enterprisedb.com> — 2022-11-01T09:10:20Z
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/
-
Re: psql: Add command to use extended query protocol
Peter Eisentraut <peter.eisentraut@enterprisedb.com> — 2022-11-01T20:47:51Z
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' -
Re: psql: Add command to use extended query protocol
Simon Riggs <simon.riggs@enterprisedb.com> — 2022-11-01T22:58:52Z
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/
-
Re: psql: Add command to use extended query protocol
Corey Huinker <corey.huinker@gmail.com> — 2022-11-02T05:18:54Z
> > > 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?
-
Re: psql: Add command to use extended query protocol
Jehan-Guillaume de Rorthais <jgdr@dalibo.com> — 2022-11-02T12:43:27Z
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?
-
Re: psql: Add command to use extended query protocol
Pavel Stehule <pavel.stehule@gmail.com> — 2022-11-02T12:55:22Z
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
-
Re: psql: Add command to use extended query protocol
Daniel Verite <daniel@manitou-mail.org> — 2022-11-02T15:04:02Z
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
-
Re: psql: Add command to use extended query protocol
Jehan-Guillaume de Rorthais <jgdr@dalibo.com> — 2022-11-02T16:24:35Z
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,
-
Re: psql: Add command to use extended query protocol
Peter Eisentraut <peter.eisentraut@enterprisedb.com> — 2022-11-04T15:45:35Z
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?
-
Re: psql: Add command to use extended query protocol
Corey Huinker <corey.huinker@gmail.com> — 2022-11-05T06:34:47Z
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.
-
Re: psql: Add command to use extended query protocol
Pavel Stehule <pavel.stehule@gmail.com> — 2022-11-05T08:46:14Z
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 > > >
-
Re: psql: Add command to use extended query protocol
Corey Huinker <corey.huinker@gmail.com> — 2022-11-07T20:27:40Z
> > > > 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.
-
Re: psql: Add command to use extended query protocol
Tom Lane <tgl@sss.pgh.pa.us> — 2022-11-07T21:12:11Z
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
-
Re: psql: Add command to use extended query protocol
Corey Huinker <corey.huinker@gmail.com> — 2022-11-08T02:47:28Z
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.
-
Re: psql: Add command to use extended query protocol
Pavel Stehule <pavel.stehule@gmail.com> — 2022-11-08T04:01:59Z
ú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 > 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. > >
-
Re: psql: Add command to use extended query protocol
David G. Johnston <david.g.johnston@gmail.com> — 2022-11-08T04:21:41Z
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.
-
Re: psql: Add command to use extended query protocol
Pavel Stehule <pavel.stehule@gmail.com> — 2022-11-08T04:29:41Z
út 8. 11. 2022 v 5:21 odesílatel David G. Johnston < david.g.johnston@gmail.com> napsal: > 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. > :$var$ looks little bit scary, and there can be risk of collision with custom string separator but :$var can be ok? There is not necessity of showing symmetry > > David J. >
-
Re: psql: Add command to use extended query protocol
Daniel Verite <daniel@manitou-mail.org> — 2022-11-08T12:02:17Z
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
-
Re: psql: Add command to use extended query protocol
Peter Eisentraut <peter.eisentraut@enterprisedb.com> — 2022-11-08T12:37:20Z
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.
-
Re: psql: Add command to use extended query protocol
Peter Eisentraut <peter.eisentraut@enterprisedb.com> — 2022-11-08T12:39:14Z
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.
-
Re: psql: Add command to use extended query protocol
Corey Huinker <corey.huinker@gmail.com> — 2022-11-08T23:12:14Z
> > > Btw., this also allows doing things like > > SELECT $1, $2 > \bind '1' '2' \g > \bind '3' '4' \g > That's one of the things I was hoping for. Very cool. > > 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. Yeah, if they wanted a prepared statement there's nothing stopping them. Review: Patch applies, tests pass. Code is quite straightforward. 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. -
Re: psql: Add command to use extended query protocol
Daniel Verite <daniel@manitou-mail.org> — 2022-11-09T19:10:34Z
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
-
Re: psql: Add command to use extended query protocol
Peter Eisentraut <peter.eisentraut@enterprisedb.com> — 2022-11-11T15:09:39Z
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?
-
Re: psql: Add command to use extended query protocol
Daniel Verite <daniel@manitou-mail.org> — 2022-11-14T13:47:35Z
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
-
Re: psql: Add command to use extended query protocol
Peter Eisentraut <peter.eisentraut@enterprisedb.com> — 2022-11-15T13:29:54Z
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.
-
Re: psql: Add command to use extended query protocol
Corey Huinker <corey.huinker@gmail.com> — 2022-11-21T22:02:08Z
On Tue, Nov 15, 2022 at 8:29 AM Peter Eisentraut < peter.eisentraut@enterprisedb.com> wrote: > 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. > > 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: String literals can include spaces [16:51:35 EST] corey=# select $1, $2 \bind 'abc def' gee \g ?column? | ?column? ----------+---------- abc def | gee (1 row) String literal includes spaces, but also includes quotes: Time: 0.363 ms [16:51:44 EST] corey=# select $1, $2 \bind "abc def" gee \g ?column? | ?column? -----------+---------- "abc def" | gee (1 row) Semi-colon does not terminate an EQP statement, ';' is seen as a parameter: [16:51:47 EST] corey=# select $1, $2 \bind "abc def" gee ; corey-# \g ERROR: bind message supplies 3 parameters, but prepared statement "" requires 2 Confirming that slash-commands must be unquoted [16:52:23 EST] corey=# select $1, $2 \bind "abc def" '\\g' \g ?column? | ?column? -----------+---------- "abc def" | \g (1 row) [16:59:00 EST] corey=# select $1, $2 \bind "abc def" '\watch' \g ?column? | ?column? -----------+---------- "abc def" | watch (1 row) Confirming that any slash command terminates the bind list, but ';' does not [16:59:54 EST] corey=# select $1, $2 \bind "abc def" gee \watch 5 Mon 21 Nov 2022 05:00:07 PM EST (every 5s) ?column? | ?column? -----------+---------- "abc def" | gee (1 row) Time: 0.422 ms Mon 21 Nov 2022 05:00:12 PM EST (every 5s) ?column? | ?column? -----------+---------- "abc def" | gee (1 row) Is this all working as expected?
-
Re: psql: Add command to use extended query protocol
Peter Eisentraut <peter.eisentraut@enterprisedb.com> — 2022-11-22T15:51:27Z
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.
-
Re: psql: Add command to use extended query protocol
Tobias Bussmann <t.bussmann@gmx.net> — 2023-09-14T20:26:55Z
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. 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
-
Re: psql: Add command to use extended query protocol
Alvaro Herrera <alvherre@alvh.no-ip.org> — 2023-09-15T09:51:22Z
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)
-
Re: psql: Add command to use extended query protocol
Aleksander Alekseev <aleksander@timescale.com> — 2024-02-16T15:15:57Z
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