On Fri, 19 Jan 2024 at 10:50, Michael Paquier <mich...@paquier.xyz> wrote:
>
> On Thu, Jan 18, 2024 at 09:25:16AM +0100, Anthonin Bonnefoy wrote:
> > From what I can tell, there's no change in the behaviour. All paths
> > would eventually go through HandleSlashCmds's cleaning logic. This is
> > also mentioned in ignore_slash_options's comment.
>
> Yeah, I can confirm that. I would be really tempted to backpatch that
> because that's a bug: we have to call ignore_slash_options() for
> inactive branches when a command parses options with OT_NORMAL. Now,
> I cannot break things, either.
>
> > Done. patch 1 adds ignore_slash_options to bind. patch 2 adds the new
> > \bindx, \close and \parse commands.
>
> 0001 has been applied on HEAD.
Since the 0001 patch has been applied, sending only 0002 as v5-0001 so
that CFBot can apply and run.
Regards,
Vignesh
From fed9e757c793815251fe41069cc8f01a5f8aacb8 Mon Sep 17 00:00:00 2001
From: Anthonin Bonnefoy <anthonin.bonne...@datadoghq.com>
Date: Thu, 18 Jan 2024 08:46:33 +0100
Subject: [PATCH v5] psql: Add support for prepared stmt with extended protocol
Currently, only unnamed prepared statement is supported by psql with the
\bind command so it's not possible to test named statement creation and
execution through extended protocol.
This commit introduces three additional commands: \parse, \bindx and
\close.
\parse creates a prepared statement using extended protocol.
\bindx binds and execute an existing prepared statement using extended
protocol.
\close closes an existing prepared statement using extended protocol.
---
doc/src/sgml/ref/psql-ref.sgml | 86 +++++++++++++++++++++
src/bin/psql/command.c | 115 +++++++++++++++++++++++++++++
src/bin/psql/common.c | 28 ++++++-
src/bin/psql/help.c | 4 +
src/bin/psql/settings.h | 6 ++
src/bin/psql/tab-complete.c | 6 +-
src/test/regress/expected/psql.out | 49 ++++++++++++
src/test/regress/sql/psql.sql | 27 +++++++
8 files changed, 316 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index cc7d797159..b0f383ab2a 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -916,6 +916,35 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
</listitem>
</varlistentry>
+ <varlistentry id="app-psql-meta-command-bindx">
+ <term><literal>\bindx</literal> <replaceable class="parameter">prepared_statement_name</replaceable> [ <replaceable class="parameter">parameter</replaceable> ] ... </term>
+
+ <listitem>
+ <para>
+ <literal>\bindx</literal> is equivalent to <literal>\bind</literal>,
+ except that it takes the name of an explicit existing prepared
+ statement as first parameter.
+ </para>
+
+ <para>
+ Example:
+<programlisting>
+INSERT INTO tbls1 VALUES ($1, $2) \parse stmt1
+\bindx stmt1 'first value' 'second value' \g
+</programlisting>
+ </para>
+
+ <para>
+ 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. So this command can be useful to test the extended
+ query protocol from psql.
+ </para>
+
+ </listitem>
+ </varlistentry>
+
<varlistentry id="app-psql-meta-command-c-lc">
<term><literal>\c</literal> or <literal>\connect [ -reuse-previous=<replaceable class="parameter">on|off</replaceable> ] [ <replaceable class="parameter">dbname</replaceable> [ <replaceable class="parameter">username</replaceable> ] [ <replaceable class="parameter">host</replaceable> ] [ <replaceable class="parameter">port</replaceable> ] | <replaceable class="parameter">conninfo</replaceable> ]</literal></term>
<listitem>
@@ -1037,6 +1066,35 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
</listitem>
</varlistentry>
+ <varlistentry id="app-psql-meta-command-close">
+ <term><literal>\close</literal> [ <replaceable class="parameter">prepared_statement_name</replaceable> ] </term>
+
+ <listitem>
+ <para>
+ Closes the specified prepared statement. Without argument, closes the unnamed statement.
+ If no prepared statement exists with this name, the operation is a no-op.
+ </para>
+
+ <para>
+ Example:
+<programlisting>
+SELECT $1 \parse stmt1
+\close stmt1
+</programlisting>
+ </para>
+
+ <para>
+ 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. So this command can be useful to test the extended
+ query protocol from psql.
+ </para>
+
+ </listitem>
+ </varlistentry>
+
+
<varlistentry id="app-psql-meta-commands-copy">
<term><literal>\copy { <replaceable class="parameter">table</replaceable> [ ( <replaceable class="parameter">column_list</replaceable> ) ] }
<literal>from</literal>
@@ -2779,6 +2837,34 @@ lo_import 152801
</listitem>
</varlistentry>
+ <varlistentry id="app-psql-meta-command-parse">
+ <term><literal>\parse <replaceable class="parameter">prepared_statement_name</replaceable></literal></term>
+ <listitem>
+ <para>
+ Creates a prepared statement from the current query buffer
+ </para>
+
+ <para>
+ Example:
+<programlisting>
+SELECT $1 \parse stmt1
+</programlisting>
+ </para>
+
+ <para>
+ 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. A <xref linkend="protocol-message-formats-Parse"/>
+ message will be issued by this command so it 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
+ simple query protocol by default.
+ </para>
+
+ </listitem>
+ </varlistentry>
+
<varlistentry id="app-psql-meta-command-password">
<term><literal>\password [ <replaceable class="parameter">username</replaceable> ]</literal></term>
<listitem>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 5c906e4806..07d58ec66f 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -64,10 +64,14 @@ static backslashResult exec_command(const char *cmd,
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_bindx(PsqlScanState scan_state, bool active_branch,
+ const char *cmd);
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,
const char *cmd);
+static backslashResult exec_command_close(PsqlScanState scan_state, bool active_branch,
+ const char *cmd);
static backslashResult exec_command_conninfo(PsqlScanState scan_state, bool active_branch);
static backslashResult exec_command_copy(PsqlScanState scan_state, bool active_branch);
static backslashResult exec_command_copyright(PsqlScanState scan_state, bool active_branch);
@@ -116,6 +120,8 @@ static backslashResult exec_command_lo(PsqlScanState scan_state, bool active_bra
static backslashResult exec_command_out(PsqlScanState scan_state, bool active_branch);
static backslashResult exec_command_print(PsqlScanState scan_state, bool active_branch,
PQExpBuffer query_buf, PQExpBuffer previous_buf);
+static backslashResult exec_command_parse(PsqlScanState scan_state, bool active_branch,
+ const char* cmd);
static backslashResult exec_command_password(PsqlScanState scan_state, bool active_branch);
static backslashResult exec_command_prompt(PsqlScanState scan_state, bool active_branch,
const char *cmd);
@@ -311,12 +317,16 @@ exec_command(const char *cmd,
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, "bindx") == 0)
+ status = exec_command_bindx(scan_state, active_branch, cmd);
else if (strcmp(cmd, "C") == 0)
status = exec_command_C(scan_state, active_branch);
else if (strcmp(cmd, "c") == 0 || strcmp(cmd, "connect") == 0)
status = exec_command_connect(scan_state, active_branch);
else if (strcmp(cmd, "cd") == 0)
status = exec_command_cd(scan_state, active_branch, cmd);
+ else if (strcmp(cmd, "close") == 0)
+ status = exec_command_close(scan_state, active_branch, cmd);
else if (strcmp(cmd, "conninfo") == 0)
status = exec_command_conninfo(scan_state, active_branch);
else if (pg_strcasecmp(cmd, "copy") == 0)
@@ -378,6 +388,8 @@ exec_command(const char *cmd,
else if (strcmp(cmd, "p") == 0 || strcmp(cmd, "print") == 0)
status = exec_command_print(scan_state, active_branch,
query_buf, previous_buf);
+ else if (strcmp(cmd, "parse") == 0)
+ status = exec_command_parse(scan_state, active_branch, cmd);
else if (strcmp(cmd, "password") == 0)
status = exec_command_password(scan_state, active_branch);
else if (strcmp(cmd, "prompt") == 0)
@@ -471,6 +483,7 @@ exec_command_bind(PsqlScanState scan_state, bool active_branch)
int nalloc = 0;
pset.bind_params = NULL;
+ pset.stmtName = NULL;
while ((opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false)))
{
@@ -492,6 +505,53 @@ exec_command_bind(PsqlScanState scan_state, bool active_branch)
return status;
}
+/*
+ * \bindx -- set query parameters for an existing prepared statement
+ */
+static backslashResult
+exec_command_bindx(PsqlScanState scan_state, bool active_branch,
+ const char* cmd)
+{
+ backslashResult status = PSQL_CMD_SKIP_LINE;
+
+ if (active_branch)
+ {
+ char *opt;
+ int nparams = 0;
+ int nalloc = 0;
+
+ pset.bind_params = NULL;
+ pset.stmtName = NULL;
+
+ opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false);
+ if (!opt)
+ {
+ pg_log_error("\\%s: missing required argument", cmd);
+ status = PSQL_CMD_ERROR;
+ free(opt);
+ } else {
+ pset.stmtName = opt;
+ pset.bind_flag = true;
+
+ while ((opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false)))
+ {
+ nparams++;
+ if (nparams > nalloc)
+ {
+ nalloc = nalloc ? nalloc * 2 : 1;
+ pset.bind_params = pg_realloc_array(pset.bind_params, char *, nalloc);
+ }
+ pset.bind_params[nparams - 1] = opt;
+ }
+ pset.bind_nparams = nparams;
+ }
+ }
+ else
+ ignore_slash_options(scan_state);
+
+ return status;
+}
+
/*
* \C -- override table title (formerly change HTML caption)
*/
@@ -642,6 +702,28 @@ exec_command_cd(PsqlScanState scan_state, bool active_branch, const char *cmd)
return success ? PSQL_CMD_SKIP_LINE : PSQL_CMD_ERROR;
}
+/*
+ * \close -- close a previously prepared statement
+ */
+static backslashResult
+exec_command_close(PsqlScanState scan_state, bool active_branch, const char *cmd)
+{
+ backslashResult status = PSQL_CMD_SKIP_LINE;
+
+ if (active_branch)
+ {
+ char *opt = psql_scan_slash_option(scan_state,
+ OT_NORMAL, NULL, false);
+ pset.stmtName = opt;
+ pset.close_flag = true;
+ status = PSQL_CMD_SEND;
+ }
+ else
+ ignore_slash_options(scan_state);
+
+ return status;
+}
+
/*
* \conninfo -- display information about the current connection
*/
@@ -2095,6 +2177,39 @@ exec_command_print(PsqlScanState scan_state, bool active_branch,
return PSQL_CMD_SKIP_LINE;
}
+/*
+ * \parse -- parse query
+ */
+static backslashResult
+exec_command_parse(PsqlScanState scan_state, bool active_branch,
+ const char* cmd)
+{
+ backslashResult status = PSQL_CMD_SKIP_LINE;
+
+ if (active_branch)
+ {
+ char *opt = psql_scan_slash_option(scan_state,
+ OT_NORMAL, NULL, false);
+ pset.stmtName = NULL;
+ if (!opt)
+ {
+ pg_log_error("\\%s: missing required argument", cmd);
+ status = PSQL_CMD_ERROR;
+ free(opt);
+ }
+ else
+ {
+ pset.stmtName = opt;
+ pset.parse_flag = true;
+ status = PSQL_CMD_SEND;
+ }
+ }
+ else
+ ignore_slash_options(scan_state);
+
+ return status;
+}
+
/*
* \password -- set user password
*/
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index 76e01b02a3..2e853b71c4 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -1241,16 +1241,34 @@ sendquery_cleanup:
pset.gsavepopt = NULL;
}
- /* clean up after \bind */
+ /* clean up after \bind or \bindx */
if (pset.bind_flag)
{
for (i = 0; i < pset.bind_nparams; i++)
free(pset.bind_params[i]);
free(pset.bind_params);
+ free(pset.stmtName);
pset.bind_params = NULL;
+ pset.stmtName = NULL;
pset.bind_flag = false;
}
+ /* clean up after \parse */
+ if (pset.parse_flag)
+ {
+ free(pset.stmtName);
+ pset.stmtName = NULL;
+ pset.parse_flag = false;
+ }
+
+ /* clean up after \close */
+ if (pset.close_flag)
+ {
+ free(pset.stmtName);
+ pset.stmtName = NULL;
+ pset.close_flag = false;
+ }
+
/* reset \gset trigger */
if (pset.gset_prefix)
{
@@ -1433,8 +1451,14 @@ ExecQueryAndProcessResults(const char *query,
else
INSTR_TIME_SET_ZERO(before);
- if (pset.bind_flag)
+ if (pset.bind_flag && pset.stmtName == NULL)
success = PQsendQueryParams(pset.db, query, pset.bind_nparams, NULL, (const char *const *) pset.bind_params, NULL, NULL, 0);
+ else if (pset.bind_flag && pset.stmtName != NULL)
+ success = PQsendQueryPrepared(pset.db, pset.stmtName, pset.bind_nparams, (const char *const *) pset.bind_params, NULL, NULL, 0);
+ else if (pset.close_flag)
+ success = PQsendClosePrepared(pset.db, pset.stmtName);
+ else if (pset.parse_flag)
+ success = PQsendPrepare(pset.db, pset.stmtName, query, 0, NULL);
else
success = PQsendQuery(pset.db, query);
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 4e79a819d8..bb2e66c109 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -165,6 +165,9 @@ slashUsage(unsigned short int pager)
HELP0("General\n");
HELP0(" \\bind [PARAM]... set query parameters\n");
+ HELP0(" \\bindx STMT_NAME [PARAM]...\n"
+ " set query parameters for an existing prepared statement\n");
+ HELP0(" \\close STMT_NAME close an existing prepared statement\n");
HELP0(" \\copyright show PostgreSQL usage and distribution terms\n");
HELP0(" \\crosstabview [COLUMNS] execute query and display result in crosstab\n");
HELP0(" \\errverbose show most recent error message at maximum verbosity\n");
@@ -312,6 +315,7 @@ slashUsage(unsigned short int pager)
" connect to new database (currently no connection)\n");
HELP0(" \\conninfo display information about current connection\n");
HELP0(" \\encoding [ENCODING] show or set client encoding\n");
+ HELP0(" \\parse STMT_NAME create a prepared statement\n");
HELP0(" \\password [USERNAME] securely change the password for a user\n");
HELP0("\n");
diff --git a/src/bin/psql/settings.h b/src/bin/psql/settings.h
index 505f99d8e4..4048c45ebf 100644
--- a/src/bin/psql/settings.h
+++ b/src/bin/psql/settings.h
@@ -100,6 +100,12 @@ typedef struct _psqlSettings
* protocol */
int bind_nparams; /* number of parameters */
char **bind_params; /* parameters for extended query protocol call */
+ bool close_flag; /* one-shot request to close a prepared statement using
+ * extended query protocol */
+ bool parse_flag; /* one-shot request to parse query using extended query
+ * protocol */
+ char *stmtName; /* prepared statement name used for extended query
+ * protocol commands */
bool crosstab_flag; /* one-shot request to crosstab result */
char *ctv_args[4]; /* \crosstabview arguments */
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index ada711d02f..7c6f4a5ff4 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1712,8 +1712,8 @@ psql_completion(const char *text, int start, int end)
/* psql's backslash commands. */
static const char *const backslash_commands[] = {
"\\a",
- "\\bind",
- "\\connect", "\\conninfo", "\\C", "\\cd", "\\copy",
+ "\\bind", "\\bindx",
+ "\\connect", "\\conninfo", "\\C", "\\cd", "\\close", "\\copy",
"\\copyright", "\\crosstabview",
"\\d", "\\da", "\\dA", "\\dAc", "\\dAf", "\\dAo", "\\dAp",
"\\db", "\\dc", "\\dconfig", "\\dC", "\\dd", "\\ddp", "\\dD",
@@ -1730,7 +1730,7 @@ psql_completion(const char *text, int start, int end)
"\\if", "\\include", "\\include_relative", "\\ir",
"\\list", "\\lo_import", "\\lo_export", "\\lo_list", "\\lo_unlink",
"\\out",
- "\\password", "\\print", "\\prompt", "\\pset",
+ "\\parse", "\\password", "\\print", "\\prompt", "\\pset",
"\\qecho", "\\quit",
"\\reset",
"\\s", "\\set", "\\setenv", "\\sf", "\\sv",
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index ad02772562..4a2dbdd38f 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -98,6 +98,47 @@ two | 2
1 | 2
(1 row)
+-- \parse (extended query protocol)
+SELECT 1 \parse ''
+SELECT 2 \parse stmt1
+SELECT $1 \parse stmt2
+SELECT $1, $2 \parse stmt3
+-- \bindx (extended query protocol)
+\bindx '' \g
+ ?column?
+----------
+ 1
+(1 row)
+
+\bindx stmt1 \g
+ ?column?
+----------
+ 2
+(1 row)
+
+\bindx stmt2 'foo' \g
+ ?column?
+----------
+ foo
+(1 row)
+
+\bindx stmt3 'foo' 'bar' \g
+ ?column? | ?column?
+----------+----------
+ foo | bar
+(1 row)
+
+-- \close (extended query protocol)
+\close
+\close stmt2
+\close stmt2
+select name, statement from pg_prepared_statements order by name;
+ name | statement
+-------+----------------
+ stmt1 | SELECT 2
+ stmt3 | SELECT $1, $2
+(2 rows)
+
-- \bind (extended query protocol)
SELECT 1 \bind \g
?column?
@@ -129,6 +170,11 @@ ERROR: cannot insert multiple commands into a prepared statement
-- bind error
SELECT $1, $2 \bind 'foo' \g
ERROR: bind message supplies 1 parameters, but prepared statement "" requires 2
+-- bindx error
+\bindx stmt2 'baz' \g
+ERROR: prepared statement "stmt2" does not exist
+\bindx stmt3 'baz' \g
+ERROR: bind message supplies 1 parameters, but prepared statement "stmt3" requires 2
-- \gset
select 10 as test01, 20 as test02, 'Hello' as test03 \gset pref01_
\echo :pref01_test01 :pref01_test02 :pref01_test03
@@ -4507,9 +4553,11 @@ bar 'bar' "bar"
\pset fieldsep | `nosuchcommand` :foo :'foo' :"foo"
\a
SELECT $1 \bind 1 \g
+ \bindx stmt1 1 2 \g
\C arg1
\c arg1 arg2 arg3 arg4
\cd arg1
+ \close stmt1
\conninfo
\copy arg1 arg2 arg3 arg4 arg5 arg6
\copyright
@@ -4538,6 +4586,7 @@ invalid command \lo
\lo_list
\o arg1
\p
+ SELECT 1 \parse
\password arg1
\prompt arg1 arg2
\pset arg1 arg2
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 129f853353..2bec298a17 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -45,6 +45,27 @@ SELECT 1 as one, 2 as two \g (format=csv csv_fieldsep='\t')
SELECT 1 as one, 2 as two \gx (title='foo bar')
\g
+-- \parse (extended query protocol)
+
+SELECT 1 \parse ''
+SELECT 2 \parse stmt1
+SELECT $1 \parse stmt2
+SELECT $1, $2 \parse stmt3
+
+-- \bindx (extended query protocol)
+
+\bindx '' \g
+\bindx stmt1 \g
+\bindx stmt2 'foo' \g
+\bindx stmt3 'foo' 'bar' \g
+
+-- \close (extended query protocol)
+
+\close
+\close stmt2
+\close stmt2
+select name, statement from pg_prepared_statements order by name;
+
-- \bind (extended query protocol)
SELECT 1 \bind \g
@@ -58,6 +79,9 @@ SELECT foo \bind \g
SELECT 1 \; SELECT 2 \bind \g
-- bind error
SELECT $1, $2 \bind 'foo' \g
+-- bindx error
+\bindx stmt2 'baz' \g
+\bindx stmt3 'baz' \g
-- \gset
@@ -990,9 +1014,11 @@ select \if false \\ (bogus \else \\ 42 \endif \\ forty_two;
\pset fieldsep | `nosuchcommand` :foo :'foo' :"foo"
\a
SELECT $1 \bind 1 \g
+ \bindx stmt1 1 2 \g
\C arg1
\c arg1 arg2 arg3 arg4
\cd arg1
+ \close stmt1
\conninfo
\copy arg1 arg2 arg3 arg4 arg5 arg6
\copyright
@@ -1020,6 +1046,7 @@ select \if false \\ (bogus \else \\ 42 \endif \\ forty_two;
\lo_list
\o arg1
\p
+ SELECT 1 \parse
\password arg1
\prompt arg1 arg2
\pset arg1 arg2
--
2.34.1
