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: Wed, 4 Mar 2026 10:35:48 -0500
Message-ID: <CADK3HHLvsPbz3e=1=N3TSYoB6B-d6AM3nUFK-SvWJSzfysKxrA@mail.gmail.com> (raw)
In-Reply-To: <CADK3HHKO0jXxj+r5f=qLJG-L20w0kw6mm9rR0dvUStwqiM+zkA@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>
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] 0002-wip-holdable-portals.patch (23.6K, 3-0002-wip-holdable-portals.patch)
download | inline diff:
From 39079f2957e4b50e5f093d86d1e0568d56486dec Mon Sep 17 00:00:00 2001
From: Dave Cramer <[email protected]>
Date: Fri, 5 Dec 2025 18:20:23 -0500
Subject: [PATCH] wip holdable portals
update docs for new protocol message
add function PQsendBindWithCursorOptions to allow cursors with options to be created and fix test to work properly
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 | 36 ++-
src/backend/tcop/backend_startup.c | 21 +-
src/backend/tcop/postgres.c | 37 +++
src/include/libpq/libpq-be.h | 1 +
src/interfaces/libpq/exports.txt | 2 +
src/interfaces/libpq/fe-connect.c | 9 +
src/interfaces/libpq/fe-exec.c | 222 ++++++++++++++++++
src/interfaces/libpq/fe-protocol3.c | 7 +
src/interfaces/libpq/libpq-fe.h | 8 +
src/interfaces/libpq/libpq-int.h | 2 +
.../modules/libpq_pipeline/libpq_pipeline.c | 90 +++++++
11 files changed, 430 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml
index 49f81676712..6e980fb1d51 100644
--- a/doc/src/sgml/protocol.sgml
+++ b/doc/src/sgml/protocol.sgml
@@ -366,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>,
@@ -1101,6 +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.
+ 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>
@@ -1125,7 +1138,11 @@ 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. An unnamed portal 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
statement specifying the unnamed portal as destination is issued. (Note
that a simple Query message also destroys the unnamed portal.) Named
@@ -4411,6 +4428,23 @@ psql "dbname=postgres replication=database" -c "IDENTIFY_SYSTEM;"
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term>Int32</term>
+ <listitem>
+ <para>
+ 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
+ portal that survives transaction commit).
+ This field is optional; if not present, no cursor options are set.
+ Named portals are required when using CURSOR_OPT_HOLD.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
</listitem>
</varlistentry>
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 d01a09dd0c4..4e4de82214b 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -1633,6 +1633,7 @@ exec_bind_message(StringInfo input_message)
int numParams;
int numRFormats;
int16 *rformats = NULL;
+ int cursorOptions = 0;
CachedPlanSource *psrc;
CachedPlan *cplan;
Portal portal;
@@ -2009,6 +2010,13 @@ exec_bind_message(StringInfo input_message)
rformats[i] = pq_getmsgint(input_message, 2);
}
+ /* 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);
+ }
pq_getmsgend(input_message);
/*
@@ -2057,6 +2065,26 @@ exec_bind_message(StringInfo input_message)
*/
PortalSetResultFormat(portal, numRFormats, rformats);
+ /* Apply cursor options */
+ if (cursorOptions & CURSOR_OPT_HOLD)
+ {
+ elog(DEBUG1, "exec_bind_message: applying CURSOR_OPT_HOLD to portal '%s'", portal_name);
+
+ if (portal_name[0] == '\0')
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_CURSOR_NAME),
+ errmsg("holdable cursors require a named portal")));
+ if (InSecurityRestrictedOperation())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("cannot create cursor WITH HOLD in restricted operation")));
+
+ elog(DEBUG1, "exec_bind_message: CURSOR_OPT_HOLD validation passed for portal '%s'", portal_name);
+ }
+
+ portal->cursorOptions = cursorOptions;
+ elog(DEBUG1, "exec_bind_message: portal '%s' cursorOptions set to 0x%04x", portal_name, cursorOptions);
+
/*
* Done binding; remove the parameters error callback. Entries emitted
* later determine independently whether to log the parameters or not.
@@ -4942,7 +4970,16 @@ PostgresMain(const char *dbname, const char *username)
portal = GetPortalByName(close_target);
if (PortalIsValid(portal))
+ {
+ elog(DEBUG1, "Close message: closing portal '%s' (cursorOptions=0x%04x)",
+ close_target, portal->cursorOptions);
PortalDrop(portal, false);
+ elog(DEBUG1, "Close message: portal '%s' closed successfully", close_target);
+ }
+ else
+ {
+ elog(DEBUG1, "Close message: portal '%s' not found", close_target);
+ }
}
break;
default:
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/interfaces/libpq/exports.txt b/src/interfaces/libpq/exports.txt
index dbbae642d76..b01c0948585 100644
--- a/src/interfaces/libpq/exports.txt
+++ b/src/interfaces/libpq/exports.txt
@@ -210,3 +210,5 @@ PQgetAuthDataHook 207
PQdefaultAuthDataHook 208
PQfullProtocolVersion 209
appendPQExpBufferVA 210
+PQsendQueryPreparedWithCursorOptions 211
+PQsendBindWithCursorOptions 212
diff --git a/src/interfaces/libpq/fe-connect.c b/src/interfaces/libpq/fe-connect.c
index b42a0cb4c78..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}
@@ -8369,6 +8373,11 @@ pqParseProtocolVersion(const char *value, ProtocolVersion *result, PGconn *conn,
*result = PG_PROTOCOL(3, 2);
return true;
}
+ if (strcmp(value, "3.3") == 0)
+ {
+ *result = PG_PROTOCOL(3, 3);
+ return true;
+ }
libpq_append_conn_error(conn, "invalid %s value: \"%s\"",
context, value);
diff --git a/src/interfaces/libpq/fe-exec.c b/src/interfaces/libpq/fe-exec.c
index 203d388bdbf..9facb606f20 100644
--- a/src/interfaces/libpq/fe-exec.c
+++ b/src/interfaces/libpq/fe-exec.c
@@ -1682,6 +1682,228 @@ PQsendQueryPrepared(PGconn *conn,
resultFormat);
}
+int
+PQsendQueryPreparedWithCursorOptions(PGconn *conn,
+ const char *stmtName,
+ int nParams,
+ const char *const *paramValues,
+ const int *paramLengths,
+ const int *paramFormats,
+ int resultFormat,
+ const char *portalName,
+ int cursorOptions)
+{
+ PGcmdQueueEntry *entry;
+
+ if (!PQsendQueryStart(conn, true))
+ return 0;
+
+ if (!stmtName)
+ {
+ libpq_append_conn_error(conn, "statement name is a null pointer");
+ return 0;
+ }
+
+ if ((cursorOptions & 0x0020) && (!portalName || portalName[0] == '\0'))
+ {
+ libpq_append_conn_error(conn, "holdable cursors require a named portal");
+ return 0;
+ }
+
+ entry = pqAllocCmdQueueEntry(conn);
+ if (entry == NULL)
+ return 0;
+
+ if (pqPutMsgStart(PqMsg_Bind, conn) < 0 ||
+ pqPuts(portalName ? portalName : "", conn) < 0 ||
+ pqPuts(stmtName, conn) < 0)
+ goto sendFailed;
+
+ if (nParams > 0 && paramFormats)
+ {
+ if (pqPutInt(nParams, 2, conn) < 0)
+ goto sendFailed;
+ for (int i = 0; i < nParams; i++)
+ if (pqPutInt(paramFormats[i], 2, conn) < 0)
+ goto sendFailed;
+ }
+ else if (pqPutInt(0, 2, conn) < 0)
+ goto sendFailed;
+
+ if (pqPutInt(nParams, 2, conn) < 0)
+ goto sendFailed;
+
+ for (int i = 0; i < nParams; i++)
+ {
+ if (paramValues && paramValues[i])
+ {
+ int len = paramLengths ? paramLengths[i] : strlen(paramValues[i]);
+ if (pqPutInt(len, 4, conn) < 0 ||
+ pqPutnchar(paramValues[i], len, conn) < 0)
+ goto sendFailed;
+ }
+ else if (pqPutInt(-1, 4, conn) < 0)
+ goto sendFailed;
+ }
+
+ if (pqPutInt(1, 2, conn) < 0 ||
+ pqPutInt(resultFormat, 2, conn) < 0)
+ goto sendFailed;
+
+ /* Send cursor options if _pq_.holdable_portal enabled */
+ if (conn->holdable_portal_enabled)
+ {
+ if (pqPutInt(cursorOptions, 4, conn) < 0)
+ goto sendFailed;
+ }
+
+ if (pqPutMsgEnd(conn) < 0)
+ goto sendFailed;
+
+ if (pqPutMsgStart(PqMsg_Describe, conn) < 0 ||
+ pqPutc('P', conn) < 0 ||
+ pqPuts(portalName ? portalName : "", conn) < 0 ||
+ pqPutMsgEnd(conn) < 0)
+ goto sendFailed;
+
+ if (pqPutMsgStart(PqMsg_Execute, conn) < 0 ||
+ pqPuts(portalName ? portalName : "", conn) < 0 ||
+ pqPutInt(0, 4, conn) < 0 ||
+ pqPutMsgEnd(conn) < 0)
+ goto sendFailed;
+
+ if (conn->pipelineStatus == PQ_PIPELINE_OFF)
+ {
+ if (pqPutMsgStart(PqMsg_Sync, conn) < 0 ||
+ pqPutMsgEnd(conn) < 0)
+ goto sendFailed;
+ }
+
+ entry->queryclass = PGQUERY_EXTENDED;
+
+ if (pqPipelineFlush(conn) < 0)
+ goto sendFailed;
+
+ conn->asyncStatus = PGASYNC_BUSY;
+ return 1;
+
+sendFailed:
+ pqRecycleCmdQueueEntry(conn, entry);
+ return 0;
+}
+
+/*
+ * PQsendBindWithCursorOptions
+ * Like PQsendQueryPreparedWithCursorOptions but sends only Bind+Describe,
+ * not Execute. This allows creating a portal that can be executed later,
+ * which is necessary for testing holdable portals (execute after commit).
+ */
+int
+PQsendBindWithCursorOptions(PGconn *conn,
+ const char *stmtName,
+ int nParams,
+ const char *const *paramValues,
+ const int *paramLengths,
+ const int *paramFormats,
+ int resultFormat,
+ const char *portalName,
+ int cursorOptions)
+{
+ PGcmdQueueEntry *entry;
+
+ if (!PQsendQueryStart(conn, true))
+ return 0;
+
+ if (!stmtName)
+ {
+ libpq_append_conn_error(conn, "statement name is a null pointer");
+ return 0;
+ }
+
+ if ((cursorOptions & 0x0020) && (!portalName || portalName[0] == '\0'))
+ {
+ libpq_append_conn_error(conn, "holdable cursors require a named portal");
+ return 0;
+ }
+
+ entry = pqAllocCmdQueueEntry(conn);
+ if (entry == NULL)
+ return 0;
+
+ if (pqPutMsgStart(PqMsg_Bind, conn) < 0 ||
+ pqPuts(portalName ? portalName : "", conn) < 0 ||
+ pqPuts(stmtName, conn) < 0)
+ goto sendFailed;
+
+ if (nParams > 0 && paramFormats)
+ {
+ if (pqPutInt(nParams, 2, conn) < 0)
+ goto sendFailed;
+ for (int i = 0; i < nParams; i++)
+ if (pqPutInt(paramFormats[i], 2, conn) < 0)
+ goto sendFailed;
+ }
+ else if (pqPutInt(0, 2, conn) < 0)
+ goto sendFailed;
+
+ if (pqPutInt(nParams, 2, conn) < 0)
+ goto sendFailed;
+
+ for (int i = 0; i < nParams; i++)
+ {
+ if (paramValues && paramValues[i])
+ {
+ int len = paramLengths ? paramLengths[i] : strlen(paramValues[i]);
+ if (pqPutInt(len, 4, conn) < 0 ||
+ pqPutnchar(paramValues[i], len, conn) < 0)
+ goto sendFailed;
+ }
+ else if (pqPutInt(-1, 4, conn) < 0)
+ goto sendFailed;
+ }
+
+ if (pqPutInt(1, 2, conn) < 0 ||
+ pqPutInt(resultFormat, 2, conn) < 0)
+ goto sendFailed;
+
+ /* Send cursor options if _pq_.holdable_portal enabled */
+ if (conn->holdable_portal_enabled)
+ {
+ if (pqPutInt(cursorOptions, 4, conn) < 0)
+ goto sendFailed;
+ }
+
+ if (pqPutMsgEnd(conn) < 0)
+ goto sendFailed;
+
+ if (pqPutMsgStart(PqMsg_Describe, conn) < 0 ||
+ pqPutc('P', conn) < 0 ||
+ pqPuts(portalName ? portalName : "", conn) < 0 ||
+ pqPutMsgEnd(conn) < 0)
+ goto sendFailed;
+
+ /* No Execute message - portal is created but not executed */
+
+ if (conn->pipelineStatus == PQ_PIPELINE_OFF)
+ {
+ if (pqPutMsgStart(PqMsg_Sync, conn) < 0 ||
+ pqPutMsgEnd(conn) < 0)
+ goto sendFailed;
+ }
+
+ entry->queryclass = PGQUERY_EXTENDED;
+
+ if (pqPipelineFlush(conn) < 0)
+ goto sendFailed;
+
+ conn->asyncStatus = PGASYNC_BUSY;
+ return 1;
+
+sendFailed:
+ pqRecycleCmdQueueEntry(conn, entry);
+ return 0;
+}
+
/*
* PQsendQueryStart
* Common startup code for PQsendQuery and sibling routines
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-fe.h b/src/interfaces/libpq/libpq-fe.h
index 905f2f33ab8..00607a7ee67 100644
--- a/src/interfaces/libpq/libpq-fe.h
+++ b/src/interfaces/libpq/libpq-fe.h
@@ -525,6 +525,14 @@ extern int PQsendQueryPrepared(PGconn *conn,
const int *paramLengths,
const int *paramFormats,
int resultFormat);
+extern int PQsendQueryPreparedWithCursorOptions(PGconn *conn, const char *stmtName,
+ int nParams, const char *const *paramValues,
+ const int *paramLengths, const int *paramFormats,
+ int resultFormat, const char *portalName, int cursorOptions);
+extern int PQsendBindWithCursorOptions(PGconn *conn, const char *stmtName,
+ int nParams, const char *const *paramValues,
+ const int *paramLengths, const int *paramFormats,
+ int resultFormat, const char *portalName, int cursorOptions);
extern int PQsetSingleRowMode(PGconn *conn);
extern int PQsetChunkedRowsMode(PGconn *conn, int chunkSize);
extern PGresult *PQgetResult(PGconn *conn);
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 */
diff --git a/src/test/modules/libpq_pipeline/libpq_pipeline.c b/src/test/modules/libpq_pipeline/libpq_pipeline.c
index aa0a6bbe762..87484dcfb67 100644
--- a/src/test/modules/libpq_pipeline/libpq_pipeline.c
+++ b/src/test/modules/libpq_pipeline/libpq_pipeline.c
@@ -2100,6 +2100,93 @@ process_result(PGconn *conn, PGresult *res, int results, int numsent)
return got_error;
}
+/*
+ * Test holdable cursors using protocol 3.3 cursor options in Bind message.
+ */
+static void
+test_holdable_cursor(PGconn *conn)
+{
+ PGresult *res;
+
+ fprintf(stderr, "holdable cursor... ");
+
+ /* Verify protocol 3.3 */
+ if (PQfullProtocolVersion(conn) < 30003)
+ pg_fatal("protocol 3.3 required, got %d", PQfullProtocolVersion(conn));
+
+ /* Start transaction */
+ res = PQexec(conn, "BEGIN");
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ pg_fatal("BEGIN failed: %s", PQerrorMessage(conn));
+ PQclear(res);
+
+ /* Create test table */
+ res = PQexec(conn, "CREATE TEMP TABLE holdable_test(id int)");
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ pg_fatal("CREATE TABLE failed: %s", PQerrorMessage(conn));
+ PQclear(res);
+
+ res = PQexec(conn, "INSERT INTO holdable_test VALUES (1), (2), (3)");
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ pg_fatal("INSERT failed: %s", PQerrorMessage(conn));
+ PQclear(res);
+
+ /* Prepare statement */
+ res = PQprepare(conn, "holdstmt", "SELECT * FROM holdable_test", 0, NULL);
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ pg_fatal("PREPARE failed: %s", PQerrorMessage(conn));
+ PQclear(res);
+
+ /* Enter pipeline mode */
+ if (PQenterPipelineMode(conn) != 1)
+ pg_fatal("failed to enter pipeline mode: %s", PQerrorMessage(conn));
+
+ /* Create holdable portal using Bind with cursor options (no Execute) */
+ if (PQsendBindWithCursorOptions(conn, "holdstmt", 0, NULL, NULL, NULL, 0, "holdportal", 0x0020) != 1)
+ pg_fatal("PQsendBindWithCursorOptions failed: %s", PQerrorMessage(conn));
+
+ /* Commit - portal should survive */
+ if (PQsendQueryParams(conn, "COMMIT", 0, NULL, NULL, NULL, NULL, 0) != 1)
+ pg_fatal("COMMIT failed: %s", PQerrorMessage(conn));
+
+ /* Execute portal after commit using FETCH (portals created via Bind are cursors) */
+ if (PQsendQueryParams(conn, "FETCH ALL FROM holdportal", 0, NULL, NULL, NULL, NULL, 0) != 1)
+ pg_fatal("FETCH failed: %s", PQerrorMessage(conn));
+
+ /* Close portal */
+ if (PQsendQueryParams(conn, "CLOSE holdportal", 0, NULL, NULL, NULL, NULL, 0) != 1)
+ pg_fatal("CLOSE failed: %s", PQerrorMessage(conn));
+
+ if (PQpipelineSync(conn) != 1)
+ pg_fatal("pipeline sync failed: %s", PQerrorMessage(conn));
+
+ /* Get results */
+ res = confirm_result_status(conn, PGRES_TUPLES_OK); /* RowDescription from Bind+Describe */
+ if (PQnfields(res) != 1)
+ pg_fatal("expected 1 field, got %d", PQnfields(res));
+ PQclear(res);
+ consume_null_result(conn);
+
+ /* COMMIT result seems to be skipped/combined - this is a libpq behavior */
+
+ res = confirm_result_status(conn, PGRES_TUPLES_OK); /* FETCH after commit */
+ if (PQntuples(res) != 3)
+ pg_fatal("expected 3 rows after commit, got %d", PQntuples(res));
+ PQclear(res);
+ consume_null_result(conn);
+
+ consume_result_status(conn, PGRES_COMMAND_OK); /* CLOSE */
+ consume_null_result(conn);
+
+ consume_result_status(conn, PGRES_PIPELINE_SYNC);
+ consume_null_result(conn);
+
+ if (PQexitPipelineMode(conn) != 1)
+ pg_fatal("failed to exit pipeline mode: %s", PQerrorMessage(conn));
+
+ fprintf(stderr, "ok\n");
+}
+
static void
usage(const char *progname)
@@ -2118,6 +2205,7 @@ print_test_list(void)
{
printf("cancel\n");
printf("disallowed_in_pipeline\n");
+ printf("holdable_cursor\n");
printf("multi_pipelines\n");
printf("nosync\n");
printf("pipeline_abort\n");
@@ -2225,6 +2313,8 @@ main(int argc, char **argv)
test_cancel(conn);
else if (strcmp(testname, "disallowed_in_pipeline") == 0)
test_disallowed_in_pipeline(conn);
+ else if (strcmp(testname, "holdable_cursor") == 0)
+ test_holdable_cursor(conn);
else if (strcmp(testname, "multi_pipelines") == 0)
test_multi_pipelines(conn);
else if (strcmp(testname, "nosync") == 0)
--
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: <CADK3HHLvsPbz3e=1=N3TSYoB6B-d6AM3nUFK-SvWJSzfysKxrA@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