Обсуждение: Support named (destination) portals in extended proto for psql meta commands.

I am a big fan of psql extended proto meta commands feature [0], and I
frequently use these psql commands for testing purposes while
developing [1] & [2].
Recently I had to support and test some more named portals (cursor)
use-cases for [1]. And I faced a problem, that there is no way to test
extended query bind commands for non-empty destination portal. So, I
propose to add a psql meta command just for that purpose. Something
like \bind_cursor CURSOR_NAME STMT_NAME [params..]

Per doc [3]
```
The Bind message gives the name of the source prepared statement
(empty string denotes the unnamed prepared statement), the name of the
destination portal (empty string denotes the unnamed portal), and the
values to use for any parameter placeholders present in the prepared
statement.
```


I did actually start to implement this, but I faced the issue with
libpq. The thing is,
PQsendQueryParams does not support non-unnamed portal (cursor) case,
and its workhorse,
PQsendQueryGuts also. In fact, in PQsendQueryGuts we always send empty
portal name

```
/* Construct the Bind message */
if (pqPutMsgStart(PqMsg_Bind, conn) < 0 ||
pqPuts("", conn) < 0 ||
pqPuts(stmtName, conn) < 0)
goto sendFailed;
```

Per [4] the first string is the name of the destination portal and
PQsendQueryGuts always send empty strings.

I did some archeology only to find that the PQsendQueryParams
declaration did not change since [5].
So, sending non-empty portal names was never supported in libpq?
It makes me think there was a good reason for that. Can somebody
please clarify on that?
If that's ok, I will proceed with sending patches for libpq and psql
to support $subj, if no complains.


[0] https://git.postgresql.org/cgit/postgresql.git/commit/?id=d55322b0da60a8798ffdb8b78ef90db0fb5be18e
[1] https://github.com/pg-sharding/spqr/pulls
[2] https://github.com/yandex/odyssey
[3] https://www.postgresql.org/docs/current/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY
[4] https://www.postgresql.org/docs/current/protocol-message-formats.html
[5] https://git.postgresql.org/cgit/postgresql.git/commit/?id=efc3a25bb02a

-- 
Best regards,
Kirill Reshke

On Sat, 13 Dec 2025 at 09:56, Kirill Reshke <reshkekirill@gmail.com> wrote:
> And I faced a problem, that there is no way to test
> extended query bind commands for non-empty destination portal. So, I
> propose to add a psql meta command just for that purpose. Something
> like \bind_cursor CURSOR_NAME STMT_NAME [params..]

Probably call it bind_portal though. So far we've aligned the meta
command names with the protocol/libpq names (e.g. \parse instead of
\prepare)

> So, sending non-empty portal names was never supported in libpq?
> It makes me think there was a good reason for that. Can somebody
> please clarify on that?

No good reason, just no-one spent the time to implement it. libpq
hasn't implemented most of the somewhat more exotic parts of the
protocol. So yeah, addition is definitely welcome.

Hi,

Thanks for raising this!

> I am a big fan of psql extended proto meta commands feature [0], and I
> frequently use these psql commands for testing purposes while
> developing [1] & [2].

+1

> Recently I had to support and test some more named portals (cursor)
> use-cases for [1]. And I faced a problem, that there is no way to test
> extended query bind commands for non-empty destination portal.

Same here [0], I ended up using JDBC to test my scenario for named
portals, but could not add in-core tests.

> So, I propose to add a psql meta command just for that purpose. Something
> like \bind_cursor CURSOR_NAME STMT_NAME [params..]

I like this idea, although instead of \bind_cursor ( or \bind_portal) what about
allowing a \portal to be optionally supplied to the end of a \bind_named?
This is easier to rationalize IMO because adding \bind_portal while we
have \bind_named is confusing.
If the \portal is not supplied, then an unnamed portal is used (current state),
and if it's supplied a named portal is used. Also, with this syntax we can allow
for a max_rows to be supplied to the portal, if we don't want to fetch
the portal
to completion.

```
\portal name [max_rows]

-- prepare the statements
select from mytab limit $1 \parse p1

-- bind/execute the statement, optionally to a portal
\bind_named p1 10 \portal portalname \g
\bind_named p1 50 \portal portalname \g

-- bind/execute the statement, optionally to a portal, with max_rows
\bind_named p1 50 \portal portalname 50 \g
\bind_named p1 50 \portal portalname 40 \g
```

The syntax may seem awkward, but we already do this with \bind,
where the meta command is not at the start.

```
select $ \bind 1 \g
```

[0] https://www.postgresql.org/message-id/CAA5RZ0s-JLjD4E7shD9otcqJTgy-1K7FLrs9F%3D0QCC5qn_bMrQ%40mail.gmail.com

--
Sami Imseih
Amazon Web Services (AWS)

Sami, Jelte, thank you for looking into this.

On Sat, 13 Dec 2025 at 18:48, Sami Imseih <samimseih@gmail.com> wrote:

>
> ```
> \portal name [max_rows]
>
> -- prepare the statements
> select from mytab limit $1 \parse p1
>
> -- bind/execute the statement, optionally to a portal
> \bind_named p1 10 \portal portalname \g
> \bind_named p1 50 \portal portalname \g
>
> -- bind/execute the statement, optionally to a portal, with max_rows
> \bind_named p1 50 \portal portalname 50 \g
> \bind_named p1 50 \portal portalname 40 \g
> ```
>
> The syntax may seem awkward, but we already do this with \bind,
> where the meta command is not at the start.
>
> ```
> select $ \bind 1 \g
> ```

The separate "\portal" command is the design I actually find
surprisingly simple and pretty. So, that's what I have implemented in
v1-0002.

I am now posting some WIP versions of changes. v1-0001 still needs
some comprehensive changes to the doc, but I decided to already post
something to this thread.
Another concern is changing the ABI of libpq. v1-0001 changes the
number of `PQsendQueryPrepared` parameters, and this is something that
has not been changed for 20+ years... Maybe the better choice would be
a new, separate function (which will still be just a proxy-function to
PQsendQueryGuts)?

Also, I think that we need to support closing destination portals
using psql meta-commands. The current design in my head is a separate
"'\close_cursor <name>" command. Since we still can cancel a portal
using SQL-level command (CLOSE), I think this should be separate
patches to the series.  WHYT?

-- 
Best regards,
Kirill Reshke

> > \portal name [max_rows]
> >
> > -- prepare the statements
> > select from mytab limit $1 \parse p1
> >
> > -- bind/execute the statement, optionally to a portal
> > \bind_named p1 10 \portal portalname \g
> > \bind_named p1 50 \portal portalname \g
> >
> > -- bind/execute the statement, optionally to a portal, with max_rows
> > \bind_named p1 50 \portal portalname 50 \g
> > \bind_named p1 50 \portal portalname 40 \g
> > ```
> >
> > The syntax may seem awkward, but we already do this with \bind,
> > where the meta command is not at the start.
> >
> > ```
> > select $ \bind 1 \g
> > ```
>
> The separate "\portal" command is the design I actually find
> surprisingly simple and pretty. So, that's what I have implemented in
> v1-0002.

cool!

> I am now posting some WIP versions of changes. v1-0001 still needs
> some comprehensive changes to the doc, but I decided to already post
> something to this thread.
> Another concern is changing the ABI of libpq. v1-0001 changes the
> number of `PQsendQueryPrepared` parameters, and this is something that
> has not been changed for 20+ years... Maybe the better choice would be
> a new, separate function (which will still be just a proxy-function to
> PQsendQueryGuts)?

We will need a new function called `PQsendQueryPreparedPortal` or something
like that, which takes in a portal name. `PQsendQueryGuts` will need
to be modified
to take in a portal name, but being a local function, that will not
break libpq ABI.

> Also, I think that we need to support closing destination portals
> using psql meta-commands. The current design in my head is a separate
> "'\close_cursor <name>" command. Since we still can cancel a portal
> using SQL-level command (CLOSE), I think this should be separate
> patches to the series.  WHYT?

I think it will be good to have a \close_cursor. I think \close_portal will
be better since a SQL-level cursor is just one way to create a named
portal.

It will be good, IMO, to roll this out with everything else to have
feature parity
with \close_prepared.

--
Sami Imseih
Amazon Web Services (AWS)

Hi,

> We will need a new function called `PQsendQueryPreparedPortal` or something
> like that, which takes in a portal name. `PQsendQueryGuts` will need
> to be modified
> to take in a portal name, but being a local function, that will not
> break libpq ABI.

A github search of PQsendQueryPrepared shows 4.4K code reference, so
it looks widely used by drivers, proxies and ffi bindings. Creating a
new dedicated function is probably required.

> I think it will be good to have a \close_cursor. I think \close_portal will
> be better since a SQL-level cursor is just one way to create a named
> portal.
>
> It will be good, IMO, to roll this out with everything else to have
> feature parity with \close_prepared.

+1 on this. It's similar to why \close_prepared was added, it could be
done with SQL, but the goal was to be able to do it using the extended
protocol.

Looking at 0001:

+const char *    resolvedPortalName;
+
+/* use unnamed portal, if not explicitly set */
+if (portalName)
+        resolvedPortalName = portalName;
+else
+        resolvedPortalName = "";

Defaulting to "" when NULL looks a bit inconsistent with the rest. It
would probably make more sense to align with stmtName and always
expect a valid portalName string, with the NULL check done in
PQsendQueryPrepared.

For 0002:

+/*
+ * \bind_named -- set query parameters for an existing prepared statement
+ */
+static backslashResult
+exec_command_portal(PsqlScanState scan_state, bool active_branch,
...
+               /* get the mandatory prepared statement name */

Comments are still mentioning bind and statement_name (probably from
exec_command_bind_named).

psql_scan_slash_option is returning a malloced buffer that needs to be
freed, thus multiple calls to \portal will leak memory.
clean_extended_state is used for \bind_named, but that's not an option
here since you don't want to reset the send_mode, so you will need to
free pset.portalName I guess?

@@ -2688,6 +2688,7 @@ clean_extended_state(void)
  pset.stmtName = NULL;
+ pset.portalName = NULL;
  pset.send_mode = PSQL_SEND_QUERY;

The portalName also needs to be freed in clean_extended_state, similar
to what's done with stmtName in PSQL_SEND_EXTENDED_QUERY_* cases.

+char       *portalName;         /* destincation portal name used for extended

There's a typo in the comment for "destination portal".

+-- Test named portals
+-- Since portals do not survive transaction
+-- bound, we have to make explicit BEGIN-COMMIT
+BEGIN;
+\startpipeline
+SELECT 10 + $1 \parse s1 \bind_named s1 1 \portal p1 \sendpipeline
\syncpipeline
+\syncpipeline
+\endpipeline
+--recheck that statement was prepared in right portal
+SELECT name FROM pg_cursors WHERE statement = 'SELECT 10 + $1 ';
+COMMIT;

You could leverage pipeline's implicit transaction to simplify the
test and run the pg_cursors check within the pipeline:

\startpipeline
SELECT 10 + $1 \parse s1 \bind_named s1 1 \portal p1 \sendpipeline
--recheck that statement was prepared in right portal
SELECT name FROM pg_cursors WHERE statement = 'SELECT 10 + $1 ';
\endpipeline

The tests would probably benefit from covering more edge cases, like:
- \portal without arguments
- \portal by itself (nothing should happen I guess?)
- \portal with an existing portal (should error)
- \portal with an empty string. It's actually not doing anything. I
would expect this to work and run with an unnamed portal.

Regards,
Anthonin Bonnefoy