public inbox for [email protected]
help / color / mirror / Atom feedFrom: Dave Cramer <[email protected]>
To: Hannu Krosing <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Jelte Fennema-Nio <[email protected]>
Cc: Jacob Champion <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Heikki Linnakangas <[email protected]>
Subject: Re: Proposal to allow setting cursor options on Portals
Date: Tue, 17 Mar 2026 10:41:41 -0400
Message-ID: <CADK3HH+Vef1fWVYj3eH0YnCVgHcWX981UA=Y4k0jCKjwVg-1PA@mail.gmail.com> (raw)
In-Reply-To: <CADK3HHLvsPbz3e=1=N3TSYoB6B-d6AM3nUFK-SvWJSzfysKxrA@mail.gmail.com>
References: <CADK3HHKe1PA1U6aB5-7tWBQ0yZGgNvY7H=ECDD9955Pas_zx_Q@mail.gmail.com>
<CAGECzQRQ5optaG4DPbshKS+zpUtn_oceh8-qdshFbS+-FSb8Dg@mail.gmail.com>
<CAOYmi+nVQRpSs3vd_v9L8ytO9wnL2ndnzGwU31aDGorVFxrAYA@mail.gmail.com>
<CAGECzQSZ43JMjA8QEJoF9DCdTO0GQeR2qyhouQciSG2ik40Yhg@mail.gmail.com>
<CAOYmi+m20jS8zZ2qFpSnvhaqGDX+vtgCsqcu9VhokyLqF8kFag@mail.gmail.com>
<CADK3HH+DPY_x_H=e0c_AVWoUP9E+YXdyJDVvmzYEYxZXT87Agw@mail.gmail.com>
<CAGECzQRZwbuSNp-mgPWmC97q63ODAun=pQtXa1Ru19ksz986Hg@mail.gmail.com>
<CADK3HHKDrnRAoAcUv1ucLB0o_ZUcJRwm+jonNPNUHoDtcA9Crw@mail.gmail.com>
<CAGECzQQriv-h_h8Ygxh_RfnLt2G4P9nWrpgMi9YL2bmcOLbUEA@mail.gmail.com>
<CADK3HHL_cUzm-R+0nHcLvxdOZQeR0YKQMDjwLTEiGX-F9=tbeA@mail.gmail.com>
<CADK3HH+o9dTYsXpCk7-Z0JW-QB2TV7=e97O8B-XDOGQb14AfSQ@mail.gmail.com>
<CAOYmi+kkTbuwGa9X=XomNivAw9P4hN3M1U7QXiP7Jw+nrQXtNQ@mail.gmail.com>
<CAGECzQSfCPXdOpUKfdkPA9iZhGyRjZAad-CXbhApZ2CnjgG2kw@mail.gmail.com>
<CAOYmi+=qE1khrtTD7oQVPJQTHoXffQQ0DPHOx870r7801zhw9g@mail.gmail.com>
<[email protected]>
<CADK3HH+6MTrMBsW+iDJ0VGzu7HQKJLLQbjnbdps3kK8Ug5prUg@mail.gmail.com>
<CA+TgmoavFJW984Y8cUv-U9K+JqxYgqEj-i1A1dORwvyki5642A@mail.gmail.com>
<CAGECzQSHKR2LtBSnDT_bjdpE1=cVzZLRcb2k5d_K=BMvJCFihg@mail.gmail.com>
<[email protected]>
<CADK3HH+TnNaW-b+d8xkUDcFZ-4oWhh5q3hCzbyuR9ShwMasHrA@mail.gmail.com>
<CA+TgmoYr2RsE9OUwo42sxtpHr8gpdTtY6mGrWBbzu2sdFf6s8A@mail.gmail.com>
<CADK3HHJ6TqYHgctgOTbuLJNHiKm5i_UE--goMKbcG7+5g2-fpQ@mail.gmail.com>
<CAMT0RQRFa43CAf773LaDUvBYmZtwcNfdQSAzpBUtdJ+EiHH=kQ@mail.gmail.com>
<CADK3HHKO0jXxj+r5f=qLJG-L20w0kw6mm9rR0dvUStwqiM+zkA@mail.gmail.com>
<CADK3HHLvsPbz3e=1=N3TSYoB6B-d6AM3nUFK-SvWJSzfysKxrA@mail.gmail.com>
Patch rebased, now ready for review
Dave Cramer
On Wed, 4 Mar 2026 at 10:35, Dave Cramer <[email protected]> wrote:
>
>
> On Wed, 4 Mar 2026 at 09:26, Dave Cramer <[email protected]> wrote:
>
>>
>>
>> On Thu, 15 Jan 2026 at 16:06, Hannu Krosing <[email protected]> wrote:
>>
>>> First, let me say that I very much support getting this into the wire
>>> protocol.
>>>
>>> As for ways to extend the protocol, I have been myself working on
>>> another patch + extension where one can return extra info in
>>> ReadyForQuery message
>>>
>>> The first things to add are
>>> * CommitLSN so we can make use of ability to WAIT FOR LSN on replica
>>> and two connection-pooling helpers
>>> * a flag telling that there are HOLD CURSORS
>>> * a flag telling that there are temp tables
>>>
>>> This extra feedback is enabled by setting a flag, so no flag --
>>> nothing to confuse the client.
>>> The extras themselves are uniform (length, tag, data) so client can
>>> ignore any tag they do not recognize.
>>>
>>> On Thu, Jan 15, 2026 at 8:11 PM Dave Cramer <[email protected]>
>>> wrote:
>>> >
>>> >
>>> > On Thu, 15 Jan 2026 at 14:00, Robert Haas <[email protected]>
>>> wrote:
>>> >>
>>> ...
>>> >> I think what I like least about this proposal is the feeling that
>>> >> we're about to embark on a long slippery slope of changing the
>>> >> protocol a little bit in every new PG version. The cancel key thing is
>>> >> a small change, look here's another. If we just keep doing that, we'll
>>> >> end up with either a lot of minor version bumps or a lot of
>>> >> extensions. I don't foresee a good outcome either way. This is a
>>> >> widely used, widely adopted protocol. The idea that we can just start
>>> >> tweaking it a little bit every year and have nothing bad happened
>>> >> seems wild, regardless of how we do the tweaking.
>>>
>>> I think "tweaking ait little bit" and only whhere needed is exactly
>>> the right approach, if it can be cleanly isolated.
>>>
>>> My approach to protocol extension modulation *is* the ability to
>>> enable different parts of the protocol individually.
>>>
>>> For example the protocol extension to allow cursor/portal flags could
>>> be implemented this way
>>>
>>> Client has to set a flag to PROTOCOL_PORTAL_OPTIONS=true to tell the
>>> server that new protocol messages are coming
>>> - If flag setting fails, client will not send the new protocol messages
>>> - If flag setting succeeds, then it is ok to send the new messages
>>> corresponding to the flag.
>>>
>>> This way the extra packets are disconnected from protocol version and
>>> can be enabled/disabled individually and per connection
>>>
>>> And it also lets one cleanly backport the change as needed without
>>> affecting anything else.
>>>
>>> > This leaves us with an all or nothing solution, which practically
>>> means we do nothing, since we have to wait until we have a sufficient
>>> backlog of
>>> > changes or features to change the protocol. I see that as untenable,
>>> unless you are now advocating for using extensions for everything ?
>>> >
>>> > Dave
>>>
>>
>> I have modified the patch to use protocol options instead of protocol
>> version
>>
>
> resending with a different patch name as I think the commitfest app won't
> pick it up with the same name
>
>
>>
>> See new version attached
>>
>
Attachments:
[application/octet-stream] 0001-holdable-portals.patch (13.6K, 3-0001-holdable-portals.patch)
download | inline diff:
From 762c7f0fa6a5bbe79eaf9700c630e120e8d68d4f Mon Sep 17 00:00:00 2001
From: Dave Cramer <[email protected]>
Date: Tue, 3 Mar 2026 09:16:59 -0500
Subject: [PATCH 2/2] Add _pq_.holdable_portal protocol option for holdable
cursors
Implement support for creating holdable portals via the extended query
protocol using a new protocol option instead of bumping the protocol
version. This allows clients to opt-in to sending cursor options in
Bind messages.
Protocol Option:
_pq_.holdable_portal=true
When enabled, clients can include an optional Int32 cursor options
field at the end of Bind messages. The CURSOR_OPT_HOLD bit (0x0020)
creates a holdable portal that survives transaction commit.
Benefits:
- Backward compatible with protocol 3.2
- Opt-in feature via connection parameter
- Uses standard _pq_. protocol option mechanism
- Server can negotiate support via NegotiateProtocolVersion
Backend Changes:
- Add holdable_portal_enabled flag to Port structure
- Parse _pq_.holdable_portal in startup packet (backend_startup.c)
- Check option flag instead of protocol version in exec_bind_message()
- Read cursor options from Bind message only when enabled
Client (libpq) Changes:
- Add holdable_portal connection parameter (default "0")
- Add holdable_portal_enabled flag to PGconn structure
- Send _pq_.holdable_portal=true in startup packet when enabled
- Include cursor options in Bind message when enabled
- Update PQsendQueryPreparedWithCursorOptions() and
PQsendBindWithCursorOptions() to use option flag
Documentation:
- Document _pq_.holdable_portal in protocol options table
- Describe cursor options field in Bind message format
- Explain holdable portal lifecycle and behavior
Usage:
conn = PQconnectdb("dbname=postgres holdable_portal=1");
PQsendQueryPreparedWithCursorOptions(conn, stmtName, ..., 0x0020);
This replaces the previous approach of using protocol version 3.3.
---
doc/src/sgml/protocol.sgml | 42 ++++++++++++++++-------------
src/backend/tcop/backend_startup.c | 21 ++++++++++++---
src/backend/tcop/postgres.c | 5 ++--
src/include/libpq/libpq-be.h | 1 +
src/include/libpq/pqcomm.h | 2 +-
src/interfaces/libpq/fe-connect.c | 4 +++
src/interfaces/libpq/fe-exec.c | 8 +++---
src/interfaces/libpq/fe-protocol3.c | 7 +++++
src/interfaces/libpq/libpq-int.h | 2 ++
9 files changed, 62 insertions(+), 30 deletions(-)
diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml
index fc2b28af2b5..6e980fb1d51 100644
--- a/doc/src/sgml/protocol.sgml
+++ b/doc/src/sgml/protocol.sgml
@@ -18,7 +18,7 @@
</para>
<para>
- This document describes version 3.3 of the protocol, introduced in
+ This document describes version 3.2 of the protocol, introduced in
<productname>PostgreSQL</productname> version 18. The server and the libpq
client library are backwards compatible with protocol version 3.0,
implemented in <productname>PostgreSQL</productname> 7.4 and later.
@@ -192,7 +192,7 @@
<title>Protocol Versions</title>
<para>
- The current, latest version of the protocol is version 3.3. However, for
+ The current, latest version of the protocol is version 3.2. However, for
backwards compatibility with old server versions and middleware that don't
support the version negotiation yet, libpq still uses protocol version 3.0
by default.
@@ -217,7 +217,7 @@
this would occur if the client requested protocol version 4.0, which does
not exist as of this writing). If the minor version requested by the
client is not supported by the server (e.g., the client requests version
- 3.3, but the server supports only 3.0), the server may either reject the
+ 3.2, but the server supports only 3.0), the server may either reject the
connection or may respond with a NegotiateProtocolVersion message
containing the highest minor protocol version which it supports. The
client may then choose either to continue with the connection using the
@@ -251,18 +251,10 @@
</thead>
<tbody>
- <row>
- <entry>3.3</entry>
- <entry>PostgreSQL 18 and later</entry>
- <entry>Current latest version. The Bind message now supports an optional
- cursor options field to control portal behavior, including the ability
- to create holdable portals that survive transaction commit.
- </entry>
- </row>
<row>
<entry>3.2</entry>
<entry>PostgreSQL 18 and later</entry>
- <entry>The secret key used in query
+ <entry>Current latest version. The secret key used in query
cancellation was enlarged from 4 bytes to a variable length field. The
BackendKeyData message was changed to accommodate that, and the CancelRequest
message was redefined to have a variable length payload.
@@ -374,6 +366,16 @@
</thead>
<tbody>
+ <row>
+ <entry><literal>_pq_.holdable_portal</literal></entry>
+ <entry>Enables support for cursor options in the Bind message.
+ When set to <literal>true</literal>, the client may include an
+ optional cursor options field in Bind messages to control portal
+ behavior, such as creating holdable portals that survive transaction
+ commit. See <xref linkend="protocol-flow-ext-query"/> for details.
+ </entry>
+ </row>
+
<row>
<entry><literal>_pq_.<replaceable>[name]</replaceable></literal></entry>
<entry>Any other parameter names beginning with <literal>_pq_.</literal>,
@@ -1109,9 +1111,9 @@ SELCT 1/0;<!-- this typo is intentional -->
pass NULL values for them in the Bind message.)
Bind also specifies the format to use for any data returned
by the query; the format can be specified overall, or per-column.
- In protocol 3.3 and later, Bind can optionally specify cursor options
- to control portal behavior, such as creating a holdable portal that
- survives transaction commit.
+ If the <literal>_pq_.holdable_portal</literal> protocol option is enabled,
+ Bind can optionally include cursor options to control portal behavior,
+ such as creating a holdable portal that survives transaction commit.
The response is either BindComplete or ErrorResponse.
</para>
@@ -1136,8 +1138,9 @@ SELCT 1/0;<!-- this typo is intentional -->
<para>
If successfully created, a named portal object lasts till the end of the
- current transaction, unless explicitly destroyed. However, a portal
- created with the CURSOR_OPT_HOLD option (protocol 3.3 and later) is
+ current transaction, unless explicitly destroyed. However, if the
+ <literal>_pq_.holdable_portal</literal> protocol option is enabled and
+ the portal is created with the CURSOR_OPT_HOLD option, the portal becomes
<firstterm>holdable</firstterm> and survives transaction commit, remaining
valid until explicitly closed or the session ends. An unnamed portal is
destroyed at the end of the transaction, or as soon as the next Bind
@@ -4430,8 +4433,9 @@ psql "dbname=postgres replication=database" -c "IDENTIFY_SYSTEM;"
<term>Int32</term>
<listitem>
<para>
- Cursor options (protocol 3.3 and later). A bitmask of options
- for the portal being created. Currently defined bits are:
+ Cursor options (optional, only if <literal>_pq_.holdable_portal</literal>
+ is enabled). A bitmask of options for the portal being created.
+ Currently defined bits are:
<literal>0x0001</literal> (CURSOR_OPT_BINARY, same as setting
result format codes to binary),
<literal>0x0020</literal> (CURSOR_OPT_HOLD, creates a holdable
diff --git a/src/backend/tcop/backend_startup.c b/src/backend/tcop/backend_startup.c
index c517115927c..055bee287f5 100644
--- a/src/backend/tcop/backend_startup.c
+++ b/src/backend/tcop/backend_startup.c
@@ -779,11 +779,24 @@ ProcessStartupPacket(Port *port, bool ssl_done, bool gss_done)
{
/*
* Any option beginning with _pq_. is reserved for use as a
- * protocol-level option, but at present no such options are
- * defined.
+ * protocol-level option.
*/
- unrecognized_protocol_options =
- lappend(unrecognized_protocol_options, pstrdup(nameptr));
+ if (strcmp(nameptr, "_pq_.holdable_portal") == 0)
+ {
+ /* Enable holdable portal support via Bind message */
+ if (!parse_bool(valptr, &port->holdable_portal_enabled))
+ ereport(FATAL,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid value for parameter \"%s\": \"%s\"",
+ "_pq_.holdable_portal",
+ valptr)));
+ }
+ else
+ {
+ /* Unrecognized protocol option */
+ unrecognized_protocol_options =
+ lappend(unrecognized_protocol_options, pstrdup(nameptr));
+ }
}
else
{
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 10bb898e612..4e4de82214b 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -2010,8 +2010,9 @@ exec_bind_message(StringInfo input_message)
rformats[i] = pq_getmsgint(input_message, 2);
}
- /* Get cursor options if present (protocol 3.3+) */
- if (input_message->cursor < input_message->len)
+ /* Get cursor options if present (_pq_.holdable_portal enabled) */
+ if (MyProcPort->holdable_portal_enabled &&
+ input_message->cursor < input_message->len)
{
cursorOptions = pq_getmsgint(input_message, 4);
elog(DEBUG1, "exec_bind_message: read cursorOptions=0x%04x from message", cursorOptions);
diff --git a/src/include/libpq/libpq-be.h b/src/include/libpq/libpq-be.h
index 921b2daa4ff..1c11d706edd 100644
--- a/src/include/libpq/libpq-be.h
+++ b/src/include/libpq/libpq-be.h
@@ -151,6 +151,7 @@ typedef struct Port
char *user_name;
char *cmdline_options;
List *guc_options;
+ bool holdable_portal_enabled; /* _pq_.holdable_portal option */
/*
* The startup packet application name, only used here for the "connection
diff --git a/src/include/libpq/pqcomm.h b/src/include/libpq/pqcomm.h
index 28e7944cdf4..a29c9c94d79 100644
--- a/src/include/libpq/pqcomm.h
+++ b/src/include/libpq/pqcomm.h
@@ -92,7 +92,7 @@ is_unixsock_path(const char *path)
* The earliest and latest frontend/backend protocol version supported.
*/
#define PG_PROTOCOL_EARLIEST PG_PROTOCOL(3,0)
-#define PG_PROTOCOL_LATEST PG_PROTOCOL(3,3)
+#define PG_PROTOCOL_LATEST PG_PROTOCOL(3,2)
/*
* Reserved protocol numbers, which have special semantics:
diff --git a/src/interfaces/libpq/fe-connect.c b/src/interfaces/libpq/fe-connect.c
index 67fa83ca159..a0622820d7e 100644
--- a/src/interfaces/libpq/fe-connect.c
+++ b/src/interfaces/libpq/fe-connect.c
@@ -417,6 +417,10 @@ static const internalPQconninfoOption PQconninfoOptions[] = {
"SSL-Key-Log-File", "D", 64,
offsetof(struct pg_conn, sslkeylogfile)},
+ {"holdable_portal", NULL, "0", NULL,
+ "Holdable-Portal", "", 1,
+ offsetof(struct pg_conn, holdable_portal)},
+
/* Terminating entry --- MUST BE LAST */
{NULL, NULL, NULL, NULL,
NULL, NULL, 0}
diff --git a/src/interfaces/libpq/fe-exec.c b/src/interfaces/libpq/fe-exec.c
index 6f85382819f..9facb606f20 100644
--- a/src/interfaces/libpq/fe-exec.c
+++ b/src/interfaces/libpq/fe-exec.c
@@ -1750,8 +1750,8 @@ PQsendQueryPreparedWithCursorOptions(PGconn *conn,
pqPutInt(resultFormat, 2, conn) < 0)
goto sendFailed;
- /* Send cursor options if protocol 3.3+ */
- if (conn->pversion >= PG_PROTOCOL(3, 3))
+ /* Send cursor options if _pq_.holdable_portal enabled */
+ if (conn->holdable_portal_enabled)
{
if (pqPutInt(cursorOptions, 4, conn) < 0)
goto sendFailed;
@@ -1866,8 +1866,8 @@ PQsendBindWithCursorOptions(PGconn *conn,
pqPutInt(resultFormat, 2, conn) < 0)
goto sendFailed;
- /* Send cursor options if protocol 3.3+ */
- if (conn->pversion >= PG_PROTOCOL(3, 3))
+ /* Send cursor options if _pq_.holdable_portal enabled */
+ if (conn->holdable_portal_enabled)
{
if (pqPutInt(cursorOptions, 4, conn) < 0)
goto sendFailed;
diff --git a/src/interfaces/libpq/fe-protocol3.c b/src/interfaces/libpq/fe-protocol3.c
index 8c1fda5caf0..b64a23048ef 100644
--- a/src/interfaces/libpq/fe-protocol3.c
+++ b/src/interfaces/libpq/fe-protocol3.c
@@ -2521,6 +2521,13 @@ build_startup_packet(const PGconn *conn, char *packet,
if (conn->pversion == PG_PROTOCOL_GREASE)
ADD_STARTUP_OPTION("_pq_.test_protocol_negotiation", "");
+ /* Add _pq_.holdable_portal option if enabled */
+ if (conn->holdable_portal && conn->holdable_portal[0] == '1')
+ {
+ ADD_STARTUP_OPTION("_pq_.holdable_portal", "true");
+ conn->holdable_portal_enabled = true;
+ }
+
/* Add any environment-driven GUC settings needed */
for (next_eo = options; next_eo->envName; next_eo++)
{
diff --git a/src/interfaces/libpq/libpq-int.h b/src/interfaces/libpq/libpq-int.h
index bd7eb59f5f8..7fdd92f2044 100644
--- a/src/interfaces/libpq/libpq-int.h
+++ b/src/interfaces/libpq/libpq-int.h
@@ -430,6 +430,7 @@ struct pg_conn
char *scram_client_key; /* base64-encoded SCRAM client key */
char *scram_server_key; /* base64-encoded SCRAM server key */
char *sslkeylogfile; /* where should the client write ssl keylogs */
+ char *holdable_portal; /* enable _pq_.holdable_portal option */
bool cancelRequest; /* true if this connection is used to send a
* cancel request, instead of being a normal
@@ -504,6 +505,7 @@ struct pg_conn
int sversion; /* server version, e.g. 70401 for 7.4.1 */
bool pversion_negotiated; /* true if NegotiateProtocolVersion
* was received */
+ bool holdable_portal_enabled; /* _pq_.holdable_portal option */
bool auth_req_received; /* true if any type of auth req received */
bool password_needed; /* true if server demanded a password */
bool gssapi_used; /* true if authenticated via gssapi */
--
2.50.1 (Apple Git-155)
view thread (39+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Proposal to allow setting cursor options on Portals
In-Reply-To: <CADK3HH+Vef1fWVYj3eH0YnCVgHcWX981UA=Y4k0jCKjwVg-1PA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox