Re: psql: Add command to use extended query protocol
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
Re: psql: Add command to use extended query protocol
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
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 psql-bind-fetch_count.patch Description: Binary data
Re: psql: Add command to use extended query protocol
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
On Tue, Nov 15, 2022 at 8:29 AM Peter Eisentraut < peter.eisentr...@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 > linkend="protocol-query-concepts"/>) to be used, unlike normal > > psql 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
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 ) to be used, unlike normal psql 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
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
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
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
> > > 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 ) to be used, unlike normal psql 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
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
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.From 076df09c701c5e60172e2dc80602726d3761e55c Mon Sep 17 00:00:00 2001
From: Peter Eisentraut
Date: Tue, 8 Nov 2022 13:33:29 +0100
Subject: [PATCH v2] psql: Add command to use extended query protocol
This adds a new psql command \bind that sets query parameters and
causes the next query to be sent using the extended query protocol.
Example:
SELECT $1, $2 \bind 'foo' 'bar' \g
This may be useful for psql scripting, but one of the main purposes is
also to be able to test various aspects of the extended query protocol
from psql and to write tests more easily.
Discussion:
https://www.postgresql.org/message-id/flat/e8dd1cd5-0e04-3598-0518-a605159fe...@enterprisedb.com
---
doc/src/sgml/ref/psql-ref.sgml | 33 ++
src/bin/psql/command.c | 37 ++
src/bin/psql/common.c | 15 +++-
src/bin/psql/help.c| 1 +
src/bin/psql/settings.h| 3 +++
src/bin/psql/tab-complete.c| 1 +
src/test/regress/expected/psql.out | 31 +
src/test/regress/sql/psql.sql | 14 +++
8 files changed, 134 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 9494f28063ad..df93a5ca9897 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -879,6 +879,39 @@ Meta-Commands
+
+ \bind [ parameter ] ...
+
+
+
+ Sets query parameters for the next query execution, with the
+ specified parameters passed for any parameter placeholders
+ ($1 etc.).
+
+
+
+ Example:
+
+INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
+
+
+
+
+ This also works for query-execution commands besides
+ \g, such as \gx and
+ \gset.
+
+
+
+ This command causes the extended query protocol (see ) to be used, unlike normal
+ psql operation, which uses the simple
+ query protocol. So this command can be useful to test the extended
+ query protocol from psql.
+
+
+
+
\c or \connect [
-reuse-previous=on|off ] [
dbname [ username ] [ host ] [ port ] | conninfo ]
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index ab613dd49e0a..3b06169ba0dc 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -63,6 +63,7 @@ static backslashResult exec_command(const char *cmd,
PQExpBuffer query_buf,
PQExpBuffer previous_buf);
static backslashResult exec_command_a(PsqlScanState scan_state, bool
active_branch);
+static backslashResult exec_command_bind(PsqlScanState scan_state, bool
active_branch);
static backslashResult exec_command_C(PsqlScanState scan_state, bool
active_branch);
static backslashResult exec_command_connect(PsqlScanState scan_state, bool
active_branch);
static backslashResult exec_command_cd(PsqlScanState scan_state, bool
active_branch,
@@ -308,6 +309,8 @@ exec_command(const char *cmd,
if (strcmp(cmd, "a") == 0)
status = exec_command_a(scan_state, active_branch);
+ else if (strcmp(cmd, "bind") == 0)
+ status = exec_command_bind(scan_state, active_branch);
else if (strcmp(cmd, "C") == 0)
status = exec_command_C(scan_state, active_branch);
else if (strcmp(cmd, "c") == 0 || strcmp(cmd, "connect") == 0)
@@ -453,6 +456,40 @@ exec_command_a(PsqlScanState scan_state, bool
active_branch)
return success ? PSQL_CMD_SKIP_LINE : PSQL_CMD_ERROR;
}
+/*
+ * \bind -- set query parameters
+ */
+static backslashResult
+exec_command_bind(PsqlScanState scan_state, bool active_branch)
+{
+ backslashResult status = PSQL_CMD_SKIP_LINE;
+
+ if (active_branch)
+ {
+ char *opt;
+ int nparam
Re: psql: Add command to use extended query protocol
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
út 8. 11. 2022 v 5:21 odesílatel David G. Johnston < david.g.johns...@gmail.com> napsal: > On Mon, Nov 7, 2022 at 9:02 PM Pavel Stehule > wrote: > >> >> >> út 8. 11. 2022 v 3:47 odesílatel Corey Huinker >> napsal: >> >>> On Mon, Nov 7, 2022 at 4:12 PM Tom Lane wrote: >>> Corey Huinker 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 => ) > > 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
On Mon, Nov 7, 2022 at 9:02 PM Pavel Stehule wrote: > > > út 8. 11. 2022 v 3:47 odesílatel Corey Huinker > napsal: > >> On Mon, Nov 7, 2022 at 4:12 PM Tom Lane wrote: >> >>> Corey Huinker 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 => ) 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
út 8. 11. 2022 v 3:47 odesílatel Corey Huinker napsal: > On Mon, Nov 7, 2022 at 4:12 PM Tom Lane wrote: > >> Corey Huinker 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
On Mon, Nov 7, 2022 at 4:12 PM Tom Lane wrote: > Corey Huinker 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
Corey Huinker 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
> > > > 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 withCREATE 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
so 5. 11. 2022 v 7:35 odesílatel Corey Huinker napsal: > On Fri, Nov 4, 2022 at 11:45 AM Peter Eisentraut < > peter.eisentr...@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
On Fri, Nov 4, 2022 at 11:45 AM Peter Eisentraut < peter.eisentr...@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
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
On Wed, 02 Nov 2022 16:04:02 +0100 "Daniel Verite" 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
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
st 2. 11. 2022 v 13:43 odesílatel Jehan-Guillaume de Rorthais < j...@dalibo.com> napsal: > Hi, > > On Fri, 28 Oct 2022 08:52:51 +0200 > Peter Eisentraut 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
Hi, On Fri, 28 Oct 2022 08:52:51 +0200 Peter Eisentraut 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
> > > 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
On Tue, 1 Nov 2022 at 20:48, Peter Eisentraut wrote: > > On 01.11.22 10:10, Simon Riggs wrote: > > On Fri, 28 Oct 2022 at 07:53, Peter Eisentraut > > 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 Riggshttp://www.EnterpriseDB.com/
Re: psql: Add command to use extended query protocol
On 01.11.22 10:10, Simon Riggs wrote: On Fri, 28 Oct 2022 at 07:53, Peter Eisentraut 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
On Fri, 28 Oct 2022 at 07:53, Peter Eisentraut 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 Riggshttp://www.EnterpriseDB.com/
Re: psql: Add command to use extended query protocol
Michael Paquier 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
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
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 signature.asc Description: PGP signature
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/From 3f4bf4a68c2edd57c7bf4c4935bad50ea0f528b7 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut
Date: Fri, 28 Oct 2022 08:29:46 +0200
Subject: [PATCH] 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'
This may be useful for psql scripting, but one of the main purposes is
also to be able to test various aspects of the extended query protocol
from psql and to write tests more easily.
---
doc/src/sgml/ref/psql-ref.sgml | 27 +
src/bin/psql/command.c | 39 ++
src/bin/psql/common.c | 15 +++-
src/bin/psql/help.c| 1 +
src/bin/psql/settings.h| 3 +++
src/bin/psql/tab-complete.c| 2 +-
src/test/regress/expected/psql.out | 31
src/test/regress/sql/psql.sql | 14 +++
8 files changed, 130 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 9494f28063ad..51b33fd3b80c 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -2323,6 +2323,33 @@ Meta-Commands
+
+ \gp [ parameter ] ...
+
+
+
+ Sends the current query buffer to the server for execution, as with
+ \g, with the specified parameters passed for any
+ parameter placeholders ($1 etc.).
+
+
+
+ Example:
+
+INSERT INTO tbl1 VALUES ($1, $2) \gp 'first value' 'second value'
+
+
+
+
+ This command uses the extended query protocol (see ), unlike \g,
+ which uses the simple query protocol. So this command can be useful
+ to test the extended query protocol from psql.
+
+
+
+
+
\gset [ prefix ]
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index ab613dd49e0a..0e760eda1f3e 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -101,6 +101,7 @@ static backslashResult exec_command_gdesc(PsqlScanState
scan_state, bool active_
static backslashResult exec_command_getenv(PsqlScanState scan_state, bool
active_branch,
const char *cmd);
static backslashResult exec_command_gexec(PsqlScanState scan_state, bool
active_branch);
+static backslashResult exec_command_gp(PsqlScanState scan_state, bool
active_branch);
static backslashResult exec_command_gset(PsqlScanState scan_state, bool
active_branch);
static backslashResult exec_command_help(PsqlScanState scan_state, bool
active_branch);
static backslashResult exec_command_html(PsqlScanState scan_state, bool
active_branch);
@@ -354,6 +355,8 @@ exec_command(const char *cmd,
status = exec_command_getenv(scan_state, active_branch, cmd);
else if (strcmp(cmd, "gexec") == 0)
status = exec_command_gexec(scan_state, active_branch);
+ else if (strcmp(cmd, "gp") == 0)
+ status = exec_command_gp(scan_state, active_branch);
else if (strcmp(cmd, "gset") == 0)
status = exec_command_gset(scan_state, active_branch);
else if (strcmp(cmd, "h") == 0 || strcmp(cmd, "help") == 0)
@@ -1546,6 +1549,42 @@ exec_command_gexec(PsqlScanState scan_state, bool
active_branch)
return status;
}
+/*
+ * \gp -- send the current query with parameters
+ */
+static backslashResult
+exec_command_gp(PsqlScanState scan_state, bool active_branch)
+{
+ backslashResult status = PSQL_CMD_SKIP_LINE;
+
+ if (active_branch)
+ {
+ char *opt;
+ int nparams = 0;
+ int nalloc = 0;
+
+ pset.gp_params = NULL;
+
+ while ((opt = psql_sca
