public inbox for [email protected]
help / color / mirror / Atom feedFrom: Dave Cramer <[email protected]>
To: Jelte Fennema-Nio <[email protected]>
Cc: Sami Imseih <[email protected]>
Cc: Hannu Krosing <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: Tom Lane <[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, 25 Mar 2026 10:34:42 -0400
Message-ID: <CADK3HH+AKcTUas-W7ZodeVzfwJgXWQH8NM1O=-550eU-hgrB5A@mail.gmail.com> (raw)
In-Reply-To: <CAGECzQTcWG4Y7nuZ7vjkcDFQKnH9uYd3gf411WBuv-6L8zChvA@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>
<CADK3HH+Vef1fWVYj3eH0YnCVgHcWX981UA=Y4k0jCKjwVg-1PA@mail.gmail.com>
<CADK3HHJHGPrzWpwmVPErq9XOcdg8nue-gPfoSz_=kabiKoZCOg@mail.gmail.com>
<CAA5RZ0u=M0u1FHu1bFxYUoFCON=8OiQamdEnAH4-EJNDbYH7Xg@mail.gmail.com>
<CAA5RZ0uESzrmA5zwHC=Uye2aH9_jHfHX7qAJPgUh8s_JLLn7PA@mail.gmail.com>
<CADK3HH+JtK=-EDO0RzWjD5aV-Wa+DG991at=vmQa08KY6YY1Dg@mail.gmail.com>
<CAGECzQTcWG4Y7nuZ7vjkcDFQKnH9uYd3gf411WBuv-6L8zChvA@mail.gmail.com>
On Wed, 25 Mar 2026 at 03:46, Jelte Fennema-Nio <[email protected]> wrote:
> On Tue, 24 Mar 2026 at 11:01, Dave Cramer <[email protected]> wrote:
> > _pq_.cursor would be fine.
>
> I think that makes sense as a name for the option. I think adding flag
> support for SCROLL and NO SCROLL would make sense in that case.
>
> Some notes on the patch (but I didn't look look at the client side
> libpq code in detail):
>
> For the protocol definition I'd like a few changes:
> 1. I'd like the new field in the bind message that you add to be
> described as an extension bitmap, not specifically for cursor options,
> so that future extensions could add bits too it too.
> 2. Related to that, I think the used bits should not align with the
> internal bits. Having the only valid flag bit be 0x0020 is kinda
> weird. Let's just make that 0x0001. We could update the internal ones
> to match if desired, but I think it's fine for the protocol bits to
> differ from the bits in the postgres server.
>
> Docs still mention CURSOR_OPT_BINARY, but support for that has been
> removed from the code afaict (which I think is indeed what should
> happen)
>
> There's a bunch of protocol version 3.3 code still around, which
> should be removed now that the protocol option is added.
>
> PQsendBindWithCursorOptions and PQsendQueryPreparedWithCursorOptions
> should error out if conn->holdable_portal_enabled is false. Right now
> it silently skips the cursor options if the connection does not
> support the protocol extension.
>
> There should be a libpq function to inspect whether the connection
> supports cursor options, so some kind of graceful fallback logic can
> be implemented by the application when it's not supported.
>
> libpq docs are missing
>
Attached is v4 of the patch
Co-Authored by Sami Imseih
Adds docs and test module
Dave
Attachments:
[application/x-patch] v4-0001-Add-_pq_.cursor-protocol-extension-for-cursor-opt.patch (50.2K, 3-v4-0001-Add-_pq_.cursor-protocol-extension-for-cursor-opt.patch)
download | inline diff:
From f70ed302b0467347ffdd475b505d0ec237c3dc67 Mon Sep 17 00:00:00 2001
From: Dave Cramer <[email protected]>
Date: Fri, 5 Dec 2025 18:20:23 -0500
Subject: [PATCH v4 1/1] Add _pq_.cursor protocol extension for cursor options
Add a protocol extension that allows clients to pass cursor option
flags in Bind messages, enabling HOLD, SCROLL, and NO_SCROLL on
named portals.
The extension appends the options to an optional Int32
field to the Bind message when negotiated during connection
startup.
The cursor_protocol connection parameter controls if the
extension is enabled.
Add PQsendBindWithCursorOptions() to libpq, which sends
Bind+Describe to create a named portal with the
cursor options.
Also, a new test module is added.
---
doc/src/sgml/libpq.sgml | 83 +-
doc/src/sgml/protocol.sgml | 27 +-
src/backend/tcop/backend_startup.c | 21 +-
src/backend/tcop/postgres.c | 39 +
src/include/libpq/libpq-be.h | 1 +
src/interfaces/libpq/exports.txt | 2 +
src/interfaces/libpq/fe-connect.c | 22 +
src/interfaces/libpq/fe-exec.c | 135 ++++
src/interfaces/libpq/fe-protocol3.c | 14 +
src/interfaces/libpq/libpq-fe.h | 22 +
src/interfaces/libpq/libpq-int.h | 2 +
src/test/modules/Makefile | 1 +
.../modules/libpq_protocol_cursor/.gitignore | 5 +
.../modules/libpq_protocol_cursor/Makefile | 25 +
.../libpq_protocol_cursor.c | 749 ++++++++++++++++++
.../modules/libpq_protocol_cursor/meson.build | 32 +
.../t/001_libpq_protocol_cursor.pl | 42 +
src/test/modules/meson.build | 1 +
18 files changed, 1212 insertions(+), 11 deletions(-)
create mode 100644 src/test/modules/libpq_protocol_cursor/.gitignore
create mode 100644 src/test/modules/libpq_protocol_cursor/Makefile
create mode 100644 src/test/modules/libpq_protocol_cursor/libpq_protocol_cursor.c
create mode 100644 src/test/modules/libpq_protocol_cursor/meson.build
create mode 100644 src/test/modules/libpq_protocol_cursor/t/001_libpq_protocol_cursor.pl
diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
index 6db823808fc..96fa1de3a67 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -3137,6 +3137,28 @@ int PQconnectionUsedGSSAPI(const PGconn *conn);
</para>
</listitem>
</varlistentry>
+
+ <varlistentry id="libpq-PQPortalCursorEnabled">
+ <term><function>PQPortalCursorEnabled</function><indexterm><primary>PQPortalCursorEnabled</primary></indexterm></term>
+ <listitem>
+ <para>
+ Returns true (1) if the connection has successfully negotiated
+ the <literal>_pq_.protocol_cursor</literal> protocol extension,
+ false (0) if not.
+
+<synopsis>
+int PQPortalCursorEnabled(const PGconn *conn);
+</synopsis>
+ </para>
+
+ <para>
+ When this returns true, <xref linkend="libpq-PQsendBindWithCursorOptions"/>
+ can be used with non-zero cursor options to create scrollable or
+ holdable portals. Applications can use this function to implement
+ graceful fallback logic when the server does not support the extension.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
</para>
@@ -5331,8 +5353,9 @@ unsigned char *PQunescapeBytea(const unsigned char *from, size_t *to_length);
<xref linkend="libpq-PQsendQueryPrepared"/>,
<xref linkend="libpq-PQsendDescribePrepared"/>,
<xref linkend="libpq-PQsendDescribePortal"/>,
- <xref linkend="libpq-PQsendClosePrepared"/>, and
- <xref linkend="libpq-PQsendClosePortal"/>,
+ <xref linkend="libpq-PQsendClosePrepared"/>,
+ <xref linkend="libpq-PQsendClosePortal"/>, and
+ <xref linkend="libpq-PQsendBindWithCursorOptions"/>,
which can be used with <xref linkend="libpq-PQgetResult"/> to duplicate
the functionality of
<xref linkend="libpq-PQexecParams"/>,
@@ -5530,6 +5553,56 @@ int PQsendClosePortal(PGconn *conn, const char *portalName);
</listitem>
</varlistentry>
+ <varlistentry id="libpq-PQsendBindWithCursorOptions">
+ <term><function>PQsendBindWithCursorOptions</function><indexterm><primary>PQsendBindWithCursorOptions</primary></indexterm></term>
+
+ <listitem>
+ <para>
+ Creates a named portal from a previously prepared statement, with
+ the specified cursor options applied.
+<synopsis>
+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);
+</synopsis>
+ </para>
+
+ <para>
+ The <literal>cursorOptions</literal> parameter is a bitmask of
+ cursor option flags. See
+ <xref linkend="protocol-extensions-table"/> for the flags defined
+ by the <literal>_pq_.protocol_cursor</literal> extension.
+ </para>
+
+ <para>
+ The <literal>portalName</literal> must be a non-empty string;
+ unnamed portals are rejected. The function sends a Bind message
+ to create the portal but does not execute it. The portal can
+ later be operated on with cursor commands such as FETCH, MOVE,
+ and CLOSE.
+ Returns 1 on success, 0 on failure.
+ </para>
+
+ <para>
+ The <literal>_pq_.protocol_cursor</literal> protocol extension must have
+ been successfully negotiated during connection startup for cursor
+ options to take effect. This is enabled by setting the
+ <literal>protocol_cursor</literal> connection parameter to
+ <literal>1</literal>. If the extension was not negotiated and
+ <literal>cursorOptions</literal> is non-zero, the function
+ returns 0. Passing <literal>cursorOptions</literal> as 0 is
+ always permitted and creates a named portal without any cursor
+ options, regardless of whether the extension was negotiated.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="libpq-PQgetResult">
<term><function>PQgetResult</function><indexterm><primary>PQgetResult</primary></indexterm></term>
@@ -5544,6 +5617,7 @@ int PQsendClosePortal(PGconn *conn, const char *portalName);
<xref linkend="libpq-PQsendDescribePortal"/>,
<xref linkend="libpq-PQsendClosePrepared"/>,
<xref linkend="libpq-PQsendClosePortal"/>,
+ <xref linkend="libpq-PQsendBindWithCursorOptions"/>,
<xref linkend="libpq-PQsendPipelineSync"/>, or
<xref linkend="libpq-PQpipelineSync"/>
call, and returns it.
@@ -5920,8 +5994,9 @@ int PQflush(PGconn *conn);
The functions <xref linkend="libpq-PQsendPrepare"/>,
<xref linkend="libpq-PQsendDescribePrepared"/>,
<xref linkend="libpq-PQsendDescribePortal"/>,
- <xref linkend="libpq-PQsendClosePrepared"/>, and
- <xref linkend="libpq-PQsendClosePortal"/> also work in pipeline mode.
+ <xref linkend="libpq-PQsendClosePrepared"/>,
+ <xref linkend="libpq-PQsendClosePortal"/>, and
+ <xref linkend="libpq-PQsendBindWithCursorOptions"/> also work in pipeline mode.
Result processing is described below.
</para>
diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml
index 49f81676712..6b633dff67e 100644
--- a/doc/src/sgml/protocol.sgml
+++ b/doc/src/sgml/protocol.sgml
@@ -346,9 +346,18 @@
<tbody>
<row>
- <entry namest="last" align="center" valign="middle">
- <emphasis>(No supported protocol extensions are currently defined.)</emphasis>
- </entry>
+ <entry><literal>_pq_.protocol_cursor</literal></entry>
+ <entry><literal>true</literal></entry>
+ <entry>PostgreSQL 19 and later</entry>
+ <entry>Enables cursor options in the Bind message.
+ When set to <literal>true</literal>, the Bind message includes
+ an optional extension bitmap field. The following flags are
+ defined for this extension:
+ <symbol>PQ_BIND_CURSOR_SCROLL</symbol> (scroll),
+ <symbol>PQ_BIND_CURSOR_NO_SCROLL</symbol> (no scroll), and
+ <symbol>PQ_BIND_CURSOR_HOLD</symbol> (hold).
+ These are defined in <filename>libpq-fe.h</filename>.
+ </entry>
</row>
</tbody>
</tgroup>
@@ -1101,6 +1110,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_.protocol_cursor</literal> protocol option is enabled,
+ Bind can optionally include cursor options to control portal behavior,
+ such as creating scrollable or holdable cursors.
The response is either BindComplete or ErrorResponse.
</para>
@@ -4411,6 +4423,15 @@ psql "dbname=postgres replication=database" -c "IDENTIFY_SYSTEM;"
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term>Int32 (optional)</term>
+ <listitem>
+ <para>
+ Bitmap set by protocol extensions.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
</listitem>
</varlistentry>
diff --git a/src/backend/tcop/backend_startup.c b/src/backend/tcop/backend_startup.c
index 5abf276c898..792d69515c0 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_.protocol_cursor") == 0)
+ {
+ /* Enable cursor options support via Bind message */
+ if (!parse_bool(valptr, &port->protocol_cursor_enabled))
+ ereport(FATAL,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid value for parameter \"%s\": \"%s\"",
+ "_pq_.protocol_cursor",
+ 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 b3563113219..b6d9eeb04a9 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -2010,6 +2010,45 @@ exec_bind_message(StringInfo input_message)
rformats[i] = pq_getmsgint(input_message, 2);
}
+ /*
+ * Get bind extension flags if present (_pq_.protocol_cursor enabled).
+ *
+ * The wire-level flag values (PQ_BIND_CURSOR_*) are defined independently
+ * of the server-internal CURSOR_OPT_* constants in parsenodes.h, so we
+ * must map between the two representations here.
+ */
+ if (MyProcPort->protocol_cursor_enabled &&
+ input_message->cursor < input_message->len)
+ {
+ int bind_ext_flags;
+
+ bind_ext_flags = pq_getmsgint(input_message, 4);
+
+ /* Reject any bits we don't recognize */
+ if (bind_ext_flags & ~0x0007)
+ ereport(ERROR,
+ (errcode(ERRCODE_PROTOCOL_VIOLATION),
+ errmsg("unrecognized bind extension flags: 0x%x",
+ bind_ext_flags & ~0x0007)));
+
+ /*
+ * Only override the default cursorOptions when the client has
+ * explicitly set flags. A value of 0 means no cursor options were
+ * requested, so keep the CreatePortal defaults.
+ */
+ if (bind_ext_flags != 0)
+ {
+ portal->cursorOptions = 0;
+
+ /* Map protocol flags to internal CURSOR_OPT_* values */
+ if (bind_ext_flags & 0x0001) /* PQ_BIND_CURSOR_SCROLL */
+ portal->cursorOptions |= CURSOR_OPT_SCROLL;
+ if (bind_ext_flags & 0x0002) /* PQ_BIND_CURSOR_NO_SCROLL */
+ portal->cursorOptions |= CURSOR_OPT_NO_SCROLL;
+ if (bind_ext_flags & 0x0004) /* PQ_BIND_CURSOR_HOLD */
+ portal->cursorOptions |= CURSOR_OPT_HOLD;
+ }
+ }
pq_getmsgend(input_message);
/*
diff --git a/src/include/libpq/libpq-be.h b/src/include/libpq/libpq-be.h
index 921b2daa4ff..8330f40f2b8 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 protocol_cursor_enabled; /* _pq_.protocol_cursor 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 1e3d5bd5867..4007ed932cc 100644
--- a/src/interfaces/libpq/exports.txt
+++ b/src/interfaces/libpq/exports.txt
@@ -211,3 +211,5 @@ PQdefaultAuthDataHook 208
PQfullProtocolVersion 209
appendPQExpBufferVA 210
PQgetThreadLock 211
+PQsendBindWithCursorOptions 212
+PQPortalCursorEnabled 213
diff --git a/src/interfaces/libpq/fe-connect.c b/src/interfaces/libpq/fe-connect.c
index db9b4c8edbf..e0912152253 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)},
+ {"protocol_cursor", NULL, "0", NULL,
+ "Protocol-Cursor", "", 1,
+ offsetof(struct pg_conn, protocol_cursor)},
+
/* Terminating entry --- MUST BE LAST */
{NULL, NULL, NULL, NULL,
NULL, NULL, 0}
@@ -3732,6 +3736,13 @@ keep_going: /* We will come back to here until there is
* proceed without.
*/
+ /*
+ * Set protocol_cursor_enabled flag based on connection
+ * parameter
+ */
+ if (conn->protocol_cursor && conn->protocol_cursor[0] == '1')
+ conn->protocol_cursor_enabled = true;
+
/* Build the startup packet. */
startpacket = pqBuildStartupPacket3(conn, &packetlen,
EnvironmentOptions);
@@ -7823,6 +7834,17 @@ PQconnectionUsedGSSAPI(const PGconn *conn)
return false;
}
+int
+PQPortalCursorEnabled(const PGconn *conn)
+{
+ if (!conn)
+ return false;
+ if (conn->protocol_cursor_enabled)
+ return true;
+ else
+ return false;
+}
+
int
PQclientEncoding(const PGconn *conn)
{
diff --git a/src/interfaces/libpq/fe-exec.c b/src/interfaces/libpq/fe-exec.c
index 203d388bdbf..8e37786552f 100644
--- a/src/interfaces/libpq/fe-exec.c
+++ b/src/interfaces/libpq/fe-exec.c
@@ -1682,6 +1682,141 @@ PQsendQueryPrepared(PGconn *conn,
resultFormat);
}
+/*
+ * PQsendBindWithCursorOptions
+ * Send a Bind message with cursor options, followed by Describe, but not
+ * Execute. This creates a named portal with the specified cursor options
+ * (PQ_BIND_CURSOR_* from libpq-fe.h) that can be fetched from later.
+ *
+ * Non-zero cursorOptions require the _pq_.protocol_cursor protocol
+ * extension; returns 0 if the extension was not negotiated. Passing
+ * cursorOptions as 0 creates a named portal without cursor options.
+ */
+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 (!portalName || portalName[0] == '\0')
+ {
+ libpq_append_conn_error(conn, "a named portal is required");
+ return 0;
+ }
+
+ if (cursorOptions != 0 && !conn->protocol_cursor_enabled)
+ {
+ libpq_append_conn_error(conn,
+ "cursor options require the _pq_.protocol_cursor protocol extension");
+ return 0;
+ }
+
+ if (cursorOptions & ~PQ_BIND_CURSOR_VALID_FLAGS)
+ {
+ libpq_append_conn_error(conn,
+ "unrecognized cursor option flags: 0x%x",
+ cursorOptions & ~PQ_BIND_CURSOR_VALID_FLAGS);
+ 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_.protocol_cursor enabled */
+ if (conn->protocol_cursor_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_DESCRIBE;
+
+ if (pqPipelineFlush(conn) < 0)
+ goto sendFailed;
+
+ /* OK, it's launched! */
+ pqAppendCmdQueueEntry(conn, entry);
+
+ 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..b2ab3f2263a 100644
--- a/src/interfaces/libpq/fe-protocol3.c
+++ b/src/interfaces/libpq/fe-protocol3.c
@@ -1544,6 +1544,16 @@ pqGetNegotiateProtocolVersion3(PGconn *conn)
strcmp(conn->workBuffer.data, "_pq_.test_protocol_negotiation") == 0)
{
found_test_protocol_negotiation = true;
+ continue;
+ }
+
+ /*
+ * Handle rejected protocol extensions we requested. Disable the
+ * corresponding feature so the client doesn't try to use it.
+ */
+ if (strcmp(conn->workBuffer.data, "_pq_.protocol_cursor") == 0)
+ {
+ conn->protocol_cursor_enabled = false;
}
else
{
@@ -2521,6 +2531,10 @@ build_startup_packet(const PGconn *conn, char *packet,
if (conn->pversion == PG_PROTOCOL_GREASE)
ADD_STARTUP_OPTION("_pq_.test_protocol_negotiation", "");
+ /* Add _pq_.protocol_cursor option if enabled */
+ if (conn->protocol_cursor && conn->protocol_cursor[0] == '1')
+ ADD_STARTUP_OPTION("_pq_.protocol_cursor", "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 f06e7a972c3..68bc4b770f7 100644
--- a/src/interfaces/libpq/libpq-fe.h
+++ b/src/interfaces/libpq/libpq-fe.h
@@ -69,6 +69,23 @@ extern "C"
/* Indicates presence of the PQAUTHDATA_OAUTH_BEARER_TOKEN_V2 authdata hook */
#define LIBPQ_HAS_OAUTH_BEARER_TOKEN_V2 1
+/*
+ * Bind message extension flags. These flags are sent in the optional
+ * extension bitmap field of the Bind message when a protocol extension
+ * is negotiated. Future extensions may define additional bits.
+ */
+
+/* Flags for the _pq_.protocol_cursor extension */
+#define PQ_BIND_CURSOR_SCROLL 0x0001 /* SCROLL */
+#define PQ_BIND_CURSOR_NO_SCROLL 0x0002 /* NO SCROLL */
+#define PQ_BIND_CURSOR_HOLD 0x0004 /* WITH HOLD */
+#define PQ_BIND_CURSOR_VALID_FLAGS (PQ_BIND_CURSOR_SCROLL | \
+ PQ_BIND_CURSOR_NO_SCROLL | \
+ PQ_BIND_CURSOR_HOLD)
+
+/* Mask of all valid Bind extension flags */
+#define PQ_BIND_EXT_VALID_FLAGS PQ_BIND_CURSOR_VALID_FLAGS
+
/*
* Option flags for PQcopyResult
*/
@@ -535,6 +552,11 @@ extern int PQsendQueryPrepared(PGconn *conn,
const int *paramLengths,
const int *paramFormats,
int resultFormat);
+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 PQPortalCursorEnabled(const PGconn *conn);
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..f7e1980981f 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 *protocol_cursor; /* enable _pq_.protocol_cursor 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 protocol_cursor_enabled; /* _pq_.protocol_cursor 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/Makefile b/src/test/modules/Makefile
index 28ce3b35eda..357fb97ea8f 100644
--- a/src/test/modules/Makefile
+++ b/src/test/modules/Makefile
@@ -11,6 +11,7 @@ SUBDIRS = \
dummy_index_am \
dummy_seclabel \
index \
+ libpq_protocol_cursor \
libpq_pipeline \
oauth_validator \
plsample \
diff --git a/src/test/modules/libpq_protocol_cursor/.gitignore b/src/test/modules/libpq_protocol_cursor/.gitignore
new file mode 100644
index 00000000000..8f55ad176bf
--- /dev/null
+++ b/src/test/modules/libpq_protocol_cursor/.gitignore
@@ -0,0 +1,5 @@
+# Generated subdirectories
+/log/
+/results/
+/tmp_check/
+/libpq_protocol_cursor
diff --git a/src/test/modules/libpq_protocol_cursor/Makefile b/src/test/modules/libpq_protocol_cursor/Makefile
new file mode 100644
index 00000000000..1fa00af1530
--- /dev/null
+++ b/src/test/modules/libpq_protocol_cursor/Makefile
@@ -0,0 +1,25 @@
+# src/test/modules/libpq_protocol_cursor/Makefile
+
+PGFILEDESC = "libpq_protocol_cursor - test program for extended query protocol cursors"
+PGAPPICON = win32
+
+PROGRAM = libpq_protocol_cursor
+OBJS = $(WIN32RES) libpq_protocol_cursor.o
+
+NO_INSTALL = 1
+
+PG_CPPFLAGS = -I$(libpq_srcdir)
+PG_LIBS_INTERNAL += $(libpq_pgport)
+
+TAP_TESTS = 1
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = src/test/modules/libpq_protocol_cursor
+top_builddir = ../../../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/src/test/modules/libpq_protocol_cursor/libpq_protocol_cursor.c b/src/test/modules/libpq_protocol_cursor/libpq_protocol_cursor.c
new file mode 100644
index 00000000000..d0c9e9ca199
--- /dev/null
+++ b/src/test/modules/libpq_protocol_cursor/libpq_protocol_cursor.c
@@ -0,0 +1,749 @@
+/*-------------------------------------------------------------------------
+ *
+ * libpq_protocol_cursor.c
+ * Tests for extended query protocol cursor options via
+ * PQsendBindWithCursorOptions (_pq_.protocol_cursor protocol extension).
+ *
+ * Copyright (c) 2024-2026, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ * src/test/modules/libpq_protocol_cursor/libpq_protocol_cursor.c
+ *
+ *-------------------------------------------------------------------------
+ */
+
+#include "postgres_fe.h"
+
+#include <string.h>
+
+#include "libpq-fe.h"
+#include "pg_getopt.h"
+
+/*
+ * Cursor option flags for PQsendBindWithCursorOptions, defined in libpq-fe.h
+ * as PQ_BIND_CURSOR_*. We use those directly.
+ */
+
+static const char *const progname = "libpq_protocol_cursor";
+
+static void exit_nicely(PGconn *conn);
+pg_noreturn static void pg_fatal_impl(int line, const char *fmt,...)
+ pg_attribute_printf(2, 3);
+
+static void
+exit_nicely(PGconn *conn)
+{
+ PQfinish(conn);
+ exit(1);
+}
+
+/*
+ * The following few functions are wrapped in macros to make the reported line
+ * number in an error match the line number of the invocation.
+ */
+
+/*
+ * Print an error to stderr and terminate the program.
+ */
+#define pg_fatal(...) pg_fatal_impl(__LINE__, __VA_ARGS__)
+pg_noreturn static void
+pg_fatal_impl(int line, const char *fmt,...)
+{
+ va_list args;
+
+ fflush(stdout);
+
+ fprintf(stderr, "\n%s:%d: ", progname, line);
+ va_start(args, fmt);
+ vfprintf(stderr, fmt, args);
+ va_end(args);
+ Assert(fmt[strlen(fmt) - 1] != '\n');
+ fprintf(stderr, "\n");
+ exit(1);
+}
+
+/*
+ * Check that libpq next returns a PGresult with the specified status,
+ * returning the PGresult so that caller can perform additional checks.
+ */
+#define confirm_result_status(conn, status) confirm_result_status_impl(__LINE__, conn, status)
+static PGresult *
+confirm_result_status_impl(int line, PGconn *conn, ExecStatusType status)
+{
+ PGresult *res;
+
+ res = PQgetResult(conn);
+ if (res == NULL)
+ pg_fatal_impl(line, "PQgetResult returned null unexpectedly: %s",
+ PQerrorMessage(conn));
+ if (PQresultStatus(res) != status)
+ pg_fatal_impl(line, "PQgetResult returned status %s, expected %s: %s",
+ PQresStatus(PQresultStatus(res)),
+ PQresStatus(status),
+ PQerrorMessage(conn));
+ return res;
+}
+
+/*
+ * Check that libpq next returns a PGresult with the specified status,
+ * then free the PGresult.
+ */
+#define consume_result_status(conn, status) consume_result_status_impl(__LINE__, conn, status)
+static void
+consume_result_status_impl(int line, PGconn *conn, ExecStatusType status)
+{
+ PGresult *res;
+
+ res = confirm_result_status_impl(line, conn, status);
+ PQclear(res);
+}
+
+/*
+ * Check that libpq next returns a null PGresult.
+ */
+#define consume_null_result(conn) consume_null_result_impl(__LINE__, conn)
+static void
+consume_null_result_impl(int line, PGconn *conn)
+{
+ PGresult *res;
+
+ res = PQgetResult(conn);
+ if (res != NULL)
+ pg_fatal_impl(line, "expected NULL PGresult, got %s: %s",
+ PQresStatus(PQresultStatus(res)),
+ PQerrorMessage(conn));
+}
+
+/*
+ * Test holdable cursor: create a portal with PQ_BIND_CURSOR_HOLD via Bind,
+ * commit the transaction, then FETCH from the surviving portal.
+ */
+static void
+test_holdable_cursor(PGconn *conn)
+{
+ PGresult *res;
+
+ fprintf(stderr, "test_holdable_cursor... ");
+
+ res = PQexec(conn, "BEGIN");
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ pg_fatal("BEGIN failed: %s", PQerrorMessage(conn));
+ PQclear(res);
+
+ res = PQexec(conn, "CREATE TEMP TABLE IF NOT EXISTS 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);
+
+ 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);
+
+ if (PQenterPipelineMode(conn) != 1)
+ pg_fatal("failed to enter pipeline mode: %s", PQerrorMessage(conn));
+
+ if (PQsendBindWithCursorOptions(conn, "holdstmt", 0, NULL, NULL, NULL, 0,
+ "holdportal", PQ_BIND_CURSOR_HOLD) != 1)
+ pg_fatal("PQsendBindWithCursorOptions failed: %s", PQerrorMessage(conn));
+
+ if (PQsendQueryParams(conn, "COMMIT", 0, NULL, NULL, NULL, NULL, 0) != 1)
+ pg_fatal("COMMIT failed: %s", PQerrorMessage(conn));
+
+ if (PQsendQueryParams(conn, "FETCH ALL FROM holdportal", 0, NULL, NULL, NULL, NULL, 0) != 1)
+ pg_fatal("FETCH failed: %s", PQerrorMessage(conn));
+
+ if (PQsendClosePortal(conn, "holdportal") != 1)
+ pg_fatal("PQsendClosePortal failed: %s", PQerrorMessage(conn));
+
+ if (PQpipelineSync(conn) != 1)
+ pg_fatal("pipeline sync failed: %s", PQerrorMessage(conn));
+
+ /* Bind+Describe result (RowDescription metadata) */
+ res = confirm_result_status(conn, PGRES_COMMAND_OK);
+ if (PQnfields(res) != 1)
+ pg_fatal("expected 1 field, got %d", PQnfields(res));
+ PQclear(res);
+ consume_null_result(conn);
+
+ /* COMMIT result */
+ consume_result_status(conn, PGRES_COMMAND_OK);
+ consume_null_result(conn);
+
+ /* FETCH after commit */
+ res = confirm_result_status(conn, PGRES_TUPLES_OK);
+ if (PQntuples(res) != 3)
+ pg_fatal("expected 3 rows after commit, got %d", PQntuples(res));
+ PQclear(res);
+ consume_null_result(conn);
+
+ /* CLOSE */
+ consume_result_status(conn, PGRES_COMMAND_OK);
+ 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");
+}
+
+/*
+ * Test scroll cursor: create a portal with PQ_BIND_CURSOR_SCROLL and verify
+ * backward fetching works.
+ */
+static void
+test_scroll_cursor(PGconn *conn)
+{
+ PGresult *res;
+
+ fprintf(stderr, "test_scroll_cursor... ");
+
+ res = PQexec(conn, "BEGIN");
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ pg_fatal("BEGIN failed: %s", PQerrorMessage(conn));
+ PQclear(res);
+
+ res = PQexec(conn, "CREATE TEMP TABLE IF NOT EXISTS scroll_test(id int)");
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ pg_fatal("CREATE TABLE failed: %s", PQerrorMessage(conn));
+ PQclear(res);
+
+ res = PQexec(conn, "INSERT INTO scroll_test VALUES (1), (2), (3)");
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ pg_fatal("INSERT failed: %s", PQerrorMessage(conn));
+ PQclear(res);
+
+ res = PQprepare(conn, "scrollstmt", "SELECT * FROM scroll_test ORDER BY id", 0, NULL);
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ pg_fatal("PREPARE failed: %s", PQerrorMessage(conn));
+ PQclear(res);
+
+ if (PQenterPipelineMode(conn) != 1)
+ pg_fatal("failed to enter pipeline mode: %s", PQerrorMessage(conn));
+
+ if (PQsendBindWithCursorOptions(conn, "scrollstmt", 0, NULL, NULL, NULL, 0,
+ "scrollportal", PQ_BIND_CURSOR_SCROLL) != 1)
+ pg_fatal("PQsendBindWithCursorOptions failed: %s", PQerrorMessage(conn));
+
+ /* Fetch forward then backward */
+ if (PQsendQueryParams(conn, "FETCH 2 FROM scrollportal", 0, NULL, NULL, NULL, NULL, 0) != 1)
+ pg_fatal("FETCH forward failed: %s", PQerrorMessage(conn));
+
+ if (PQsendQueryParams(conn, "FETCH BACKWARD 1 FROM scrollportal", 0, NULL, NULL, NULL, NULL, 0) != 1)
+ pg_fatal("FETCH backward failed: %s", PQerrorMessage(conn));
+
+ if (PQsendClosePortal(conn, "scrollportal") != 1)
+ pg_fatal("PQsendClosePortal failed: %s", PQerrorMessage(conn));
+
+ if (PQsendQueryParams(conn, "COMMIT", 0, NULL, NULL, NULL, NULL, 0) != 1)
+ pg_fatal("COMMIT failed: %s", PQerrorMessage(conn));
+
+ if (PQpipelineSync(conn) != 1)
+ pg_fatal("pipeline sync failed: %s", PQerrorMessage(conn));
+
+ /* Bind+Describe result (RowDescription metadata) */
+ res = confirm_result_status(conn, PGRES_COMMAND_OK);
+ if (PQnfields(res) != 1)
+ pg_fatal("expected 1 field, got %d", PQnfields(res));
+ PQclear(res);
+ consume_null_result(conn);
+
+ /* FETCH forward 2 */
+ res = confirm_result_status(conn, PGRES_TUPLES_OK);
+ if (PQntuples(res) != 2)
+ pg_fatal("expected 2 rows from forward fetch, got %d", PQntuples(res));
+ PQclear(res);
+ consume_null_result(conn);
+
+ /* FETCH backward 1 - should get row with id=1 */
+ res = confirm_result_status(conn, PGRES_TUPLES_OK);
+ if (PQntuples(res) != 1)
+ pg_fatal("expected 1 row from backward fetch, got %d", PQntuples(res));
+ if (strcmp(PQgetvalue(res, 0, 0), "1") != 0)
+ pg_fatal("expected value '1' from backward fetch, got '%s'", PQgetvalue(res, 0, 0));
+ PQclear(res);
+ consume_null_result(conn);
+
+ /* CLOSE */
+ consume_result_status(conn, PGRES_COMMAND_OK);
+ consume_null_result(conn);
+
+ /* COMMIT */
+ consume_result_status(conn, PGRES_COMMAND_OK);
+ 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");
+}
+
+/*
+ * Test no-scroll cursor: create a portal with PQ_BIND_CURSOR_NO_SCROLL and
+ * verify backward fetching is rejected.
+ */
+static void
+test_no_scroll_cursor(PGconn *conn)
+{
+ PGresult *res;
+
+ fprintf(stderr, "test_no_scroll_cursor... ");
+
+ res = PQexec(conn, "BEGIN");
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ pg_fatal("BEGIN failed: %s", PQerrorMessage(conn));
+ PQclear(res);
+
+ res = PQexec(conn, "CREATE TEMP TABLE IF NOT EXISTS noscroll_test(id int)");
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ pg_fatal("CREATE TABLE failed: %s", PQerrorMessage(conn));
+ PQclear(res);
+
+ res = PQexec(conn, "INSERT INTO noscroll_test VALUES (1), (2), (3)");
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ pg_fatal("INSERT failed: %s", PQerrorMessage(conn));
+ PQclear(res);
+
+ res = PQprepare(conn, "noscrollstmt", "SELECT * FROM noscroll_test ORDER BY id", 0, NULL);
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ pg_fatal("PREPARE failed: %s", PQerrorMessage(conn));
+ PQclear(res);
+
+ if (PQenterPipelineMode(conn) != 1)
+ pg_fatal("failed to enter pipeline mode: %s", PQerrorMessage(conn));
+
+ if (PQsendBindWithCursorOptions(conn, "noscrollstmt", 0, NULL, NULL, NULL, 0,
+ "noscrollportal", PQ_BIND_CURSOR_NO_SCROLL) != 1)
+ pg_fatal("PQsendBindWithCursorOptions failed: %s", PQerrorMessage(conn));
+
+ /* Forward fetch should work */
+ if (PQsendQueryParams(conn, "FETCH 1 FROM noscrollportal", 0, NULL, NULL, NULL, NULL, 0) != 1)
+ pg_fatal("FETCH forward failed: %s", PQerrorMessage(conn));
+
+ /* Backward fetch should fail */
+ if (PQsendQueryParams(conn, "FETCH BACKWARD 1 FROM noscrollportal", 0, NULL, NULL, NULL, NULL, 0) != 1)
+ pg_fatal("FETCH backward send failed: %s", PQerrorMessage(conn));
+
+ if (PQsendClosePortal(conn, "noscrollportal") != 1)
+ pg_fatal("PQsendClosePortal failed: %s", PQerrorMessage(conn));
+
+ if (PQpipelineSync(conn) != 1)
+ pg_fatal("pipeline sync failed: %s", PQerrorMessage(conn));
+
+ /* Bind+Describe result (RowDescription metadata) */
+ res = confirm_result_status(conn, PGRES_COMMAND_OK);
+ if (PQnfields(res) != 1)
+ pg_fatal("expected 1 field, got %d", PQnfields(res));
+ PQclear(res);
+ consume_null_result(conn);
+
+ /* FETCH forward 1 - should succeed */
+ res = confirm_result_status(conn, PGRES_TUPLES_OK);
+ if (PQntuples(res) != 1)
+ pg_fatal("expected 1 row from forward fetch, got %d", PQntuples(res));
+ PQclear(res);
+ consume_null_result(conn);
+
+ /* FETCH backward - should fail */
+ consume_result_status(conn, PGRES_FATAL_ERROR);
+ consume_null_result(conn);
+
+ /* CLOSE - pipeline is aborted after the error */
+ consume_result_status(conn, PGRES_PIPELINE_ABORTED);
+ consume_null_result(conn);
+
+ /* Pipeline sync resets the abort state */
+ 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));
+
+ /* Clean up: rollback the failed transaction */
+ res = PQexec(conn, "ROLLBACK");
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ pg_fatal("ROLLBACK failed: %s", PQerrorMessage(conn));
+ PQclear(res);
+
+ fprintf(stderr, "ok\n");
+}
+
+/*
+ * Test combined cursor options: create a holdable + scrollable portal,
+ * commit the transaction, then fetch backward from the surviving portal.
+ */
+static void
+test_holdable_scroll_cursor(PGconn *conn)
+{
+ PGresult *res;
+
+ fprintf(stderr, "test_holdable_scroll_cursor... ");
+
+ res = PQexec(conn, "BEGIN");
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ pg_fatal("BEGIN failed: %s", PQerrorMessage(conn));
+ PQclear(res);
+
+ res = PQexec(conn, "CREATE TEMP TABLE IF NOT EXISTS holdscroll_test(id int)");
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ pg_fatal("CREATE TABLE failed: %s", PQerrorMessage(conn));
+ PQclear(res);
+
+ res = PQexec(conn, "INSERT INTO holdscroll_test VALUES (1), (2), (3)");
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ pg_fatal("INSERT failed: %s", PQerrorMessage(conn));
+ PQclear(res);
+
+ res = PQprepare(conn, "holdscrollstmt",
+ "SELECT * FROM holdscroll_test ORDER BY id", 0, NULL);
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ pg_fatal("PREPARE failed: %s", PQerrorMessage(conn));
+ PQclear(res);
+
+ if (PQenterPipelineMode(conn) != 1)
+ pg_fatal("failed to enter pipeline mode: %s", PQerrorMessage(conn));
+
+ /* Combine HOLD and SCROLL options */
+ if (PQsendBindWithCursorOptions(conn, "holdscrollstmt", 0, NULL, NULL, NULL, 0,
+ "holdscrollportal",
+ PQ_BIND_CURSOR_HOLD | PQ_BIND_CURSOR_SCROLL) != 1)
+ pg_fatal("PQsendBindWithCursorOptions failed: %s", PQerrorMessage(conn));
+
+ if (PQsendQueryParams(conn, "COMMIT", 0, NULL, NULL, NULL, NULL, 0) != 1)
+ pg_fatal("COMMIT failed: %s", PQerrorMessage(conn));
+
+ /* Fetch forward after commit — holdable keeps the portal alive */
+ if (PQsendQueryParams(conn, "FETCH 2 FROM holdscrollportal",
+ 0, NULL, NULL, NULL, NULL, 0) != 1)
+ pg_fatal("FETCH forward failed: %s", PQerrorMessage(conn));
+
+ /* Fetch backward — scroll option allows this */
+ if (PQsendQueryParams(conn, "FETCH BACKWARD 1 FROM holdscrollportal",
+ 0, NULL, NULL, NULL, NULL, 0) != 1)
+ pg_fatal("FETCH backward failed: %s", PQerrorMessage(conn));
+
+ if (PQsendClosePortal(conn, "holdscrollportal") != 1)
+ pg_fatal("PQsendClosePortal failed: %s", PQerrorMessage(conn));
+
+ if (PQpipelineSync(conn) != 1)
+ pg_fatal("pipeline sync failed: %s", PQerrorMessage(conn));
+
+ /* Bind+Describe result (RowDescription metadata) */
+ res = confirm_result_status(conn, PGRES_COMMAND_OK);
+ if (PQnfields(res) != 1)
+ pg_fatal("expected 1 field, got %d", PQnfields(res));
+ PQclear(res);
+ consume_null_result(conn);
+
+ /* COMMIT */
+ consume_result_status(conn, PGRES_COMMAND_OK);
+ consume_null_result(conn);
+
+ /* FETCH forward 2 */
+ res = confirm_result_status(conn, PGRES_TUPLES_OK);
+ if (PQntuples(res) != 2)
+ pg_fatal("expected 2 rows from forward fetch, got %d", PQntuples(res));
+ PQclear(res);
+ consume_null_result(conn);
+
+ /* FETCH backward 1 — should get row with id=1 */
+ res = confirm_result_status(conn, PGRES_TUPLES_OK);
+ if (PQntuples(res) != 1)
+ pg_fatal("expected 1 row from backward fetch, got %d", PQntuples(res));
+ if (strcmp(PQgetvalue(res, 0, 0), "1") != 0)
+ pg_fatal("expected value '1' from backward fetch, got '%s'",
+ PQgetvalue(res, 0, 0));
+ PQclear(res);
+ consume_null_result(conn);
+
+ /* CLOSE */
+ consume_result_status(conn, PGRES_COMMAND_OK);
+ 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");
+}
+
+/*
+ * Test that cursor options on a DML statement are harmlessly ignored.
+ * The portal gets cursorOptions set, but since it's not a DECLARE CURSOR,
+ * the options have no effect.
+ */
+static void
+test_dml_with_cursor_options(PGconn *conn)
+{
+ PGresult *res;
+
+ fprintf(stderr, "test_dml_with_cursor_options... ");
+
+ res = PQexec(conn, "CREATE TEMP TABLE IF NOT EXISTS dml_test(id int)");
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ pg_fatal("CREATE TABLE failed: %s", PQerrorMessage(conn));
+ PQclear(res);
+
+ res = PQprepare(conn, "dmlstmt",
+ "INSERT INTO dml_test VALUES (1), (2), (3)", 0, NULL);
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ pg_fatal("PREPARE failed: %s", PQerrorMessage(conn));
+ PQclear(res);
+
+ if (PQenterPipelineMode(conn) != 1)
+ pg_fatal("failed to enter pipeline mode: %s", PQerrorMessage(conn));
+
+ /* Pass SCROLL option on a DML — should be silently ignored */
+ if (PQsendBindWithCursorOptions(conn, "dmlstmt", 0, NULL, NULL, NULL, 0,
+ "dmlportal", PQ_BIND_CURSOR_SCROLL) != 1)
+ pg_fatal("PQsendBindWithCursorOptions failed: %s", PQerrorMessage(conn));
+
+ if (PQpipelineSync(conn) != 1)
+ pg_fatal("pipeline sync failed: %s", PQerrorMessage(conn));
+
+ /* Bind+Describe result */
+ res = confirm_result_status(conn, PGRES_COMMAND_OK);
+ PQclear(res);
+ 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));
+
+ /* Verify the INSERT didn't actually execute (Bind+Describe only) */
+ res = PQexec(conn, "SELECT count(*) FROM dml_test");
+ if (PQresultStatus(res) != PGRES_TUPLES_OK)
+ pg_fatal("SELECT count failed: %s", PQerrorMessage(conn));
+ if (strcmp(PQgetvalue(res, 0, 0), "0") != 0)
+ pg_fatal("expected 0 rows (Bind+Describe doesn't execute), got %s",
+ PQgetvalue(res, 0, 0));
+ PQclear(res);
+
+ fprintf(stderr, "ok\n");
+}
+
+/*
+ * Test client-side validation: PQsendBindWithCursorOptions should reject
+ * an unnamed (empty) portal.
+ */
+static void
+test_unnamed_portal_rejected(PGconn *conn)
+{
+ PGresult *res;
+
+ fprintf(stderr, "test_unnamed_portal_rejected... ");
+
+ res = PQprepare(conn, "rejectstmt", "SELECT 1", 0, NULL);
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ pg_fatal("PREPARE failed: %s", PQerrorMessage(conn));
+ PQclear(res);
+
+ if (PQenterPipelineMode(conn) != 1)
+ pg_fatal("failed to enter pipeline mode: %s", PQerrorMessage(conn));
+
+ /* Empty portal name should be rejected client-side */
+ if (PQsendBindWithCursorOptions(conn, "rejectstmt", 0, NULL, NULL, NULL, 0,
+ "", PQ_BIND_CURSOR_HOLD) != 0)
+ pg_fatal("expected PQsendBindWithCursorOptions to reject empty portal name");
+
+ /* NULL portal name should also be rejected */
+ if (PQsendBindWithCursorOptions(conn, "rejectstmt", 0, NULL, NULL, NULL, 0,
+ NULL, PQ_BIND_CURSOR_HOLD) != 0)
+ pg_fatal("expected PQsendBindWithCursorOptions to reject NULL portal name");
+
+ if (PQexitPipelineMode(conn) != 1)
+ pg_fatal("failed to exit pipeline mode: %s", PQerrorMessage(conn));
+
+ fprintf(stderr, "ok\n");
+}
+
+/*
+ * Test that cursor options are rejected when _pq_.protocol_cursor is not negotiated.
+ * HOLD is requested but the extension is disabled, so the API call itself
+ * returns 0.
+ */
+static void
+test_cursor_options_without_extension(PGconn *conn)
+{
+ PGresult *res;
+
+ fprintf(stderr, "test_cursor_options_without_extension... ");
+
+ /*
+ * PQPortalCursorEnabled should return false when extension is not
+ * negotiated
+ */
+ if (PQPortalCursorEnabled(conn) != 0)
+ pg_fatal("expected PQPortalCursorEnabled to return false");
+
+ res = PQprepare(conn, "noextstmt", "SELECT 1", 0, NULL);
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ pg_fatal("PREPARE failed: %s", PQerrorMessage(conn));
+ PQclear(res);
+
+ if (PQenterPipelineMode(conn) != 1)
+ pg_fatal("failed to enter pipeline mode: %s", PQerrorMessage(conn));
+
+ /* Non-zero cursorOptions should be rejected when extension is disabled */
+ if (PQsendBindWithCursorOptions(conn, "noextstmt", 0, NULL, NULL, NULL, 0,
+ "noextportal", PQ_BIND_CURSOR_HOLD) != 0)
+ pg_fatal("expected PQsendBindWithCursorOptions to reject cursor options");
+
+ /* Zero cursorOptions should still succeed */
+ if (PQsendBindWithCursorOptions(conn, "noextstmt", 0, NULL, NULL, NULL, 0,
+ "noextportal", 0) != 1)
+ pg_fatal("PQsendBindWithCursorOptions with zero options failed: %s",
+ PQerrorMessage(conn));
+
+ if (PQpipelineSync(conn) != 1)
+ pg_fatal("pipeline sync failed: %s", PQerrorMessage(conn));
+
+ /* Bind+Describe result */
+ res = confirm_result_status(conn, PGRES_COMMAND_OK);
+ PQclear(res);
+ 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)
+{
+ fprintf(stderr, "%s tests extended query protocol cursor options.\n\n", progname);
+ fprintf(stderr, "Usage:\n");
+ fprintf(stderr, " %s tests\n", progname);
+ fprintf(stderr, " %s TESTNAME [CONNINFO]\n", progname);
+}
+
+/*
+ * Test that invalid cursor option flags are rejected client-side.
+ */
+static void
+test_invalid_flags_rejected(PGconn *conn)
+{
+ PGresult *res;
+
+ fprintf(stderr, "test_invalid_flags_rejected... ");
+
+ res = PQprepare(conn, "invalidstmt", "SELECT 1", 0, NULL);
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ pg_fatal("PREPARE failed: %s", PQerrorMessage(conn));
+ PQclear(res);
+
+ if (PQenterPipelineMode(conn) != 1)
+ pg_fatal("failed to enter pipeline mode: %s", PQerrorMessage(conn));
+
+ /* Flag 0x0008 is not a valid bind extension flag */
+ if (PQsendBindWithCursorOptions(conn, "invalidstmt", 0, NULL, NULL, NULL, 0,
+ "invalidportal", 0x0008) != 0)
+ pg_fatal("expected PQsendBindWithCursorOptions to reject invalid flags");
+
+ /* Combination of valid and invalid flags should also be rejected */
+ if (PQsendBindWithCursorOptions(conn, "invalidstmt", 0, NULL, NULL, NULL, 0,
+ "invalidportal",
+ PQ_BIND_CURSOR_HOLD | 0x0100) != 0)
+ pg_fatal("expected PQsendBindWithCursorOptions to reject mixed invalid flags");
+
+ if (PQexitPipelineMode(conn) != 1)
+ pg_fatal("failed to exit pipeline mode: %s", PQerrorMessage(conn));
+
+ fprintf(stderr, "ok\n");
+}
+
+static void
+print_test_list(void)
+{
+ printf("holdable_cursor\n");
+ printf("scroll_cursor\n");
+ printf("no_scroll_cursor\n");
+ printf("holdable_scroll_cursor\n");
+ printf("dml_with_cursor_options\n");
+ printf("unnamed_portal_rejected\n");
+ printf("invalid_flags_rejected\n");
+ printf("cursor_options_without_extension\n");
+}
+
+int
+main(int argc, char **argv)
+{
+ const char *conninfo = "";
+ PGconn *conn;
+ char *testname;
+ PGresult *res;
+
+ if (argc < 2)
+ {
+ usage(argv[0]);
+ exit(1);
+ }
+
+ testname = argv[1];
+
+ if (strcmp(testname, "tests") == 0)
+ {
+ print_test_list();
+ exit(0);
+ }
+
+ if (argc > 2)
+ conninfo = argv[2];
+
+ conn = PQconnectdb(conninfo);
+ if (PQstatus(conn) != CONNECTION_OK)
+ {
+ fprintf(stderr, "Connection to database failed: %s\n",
+ PQerrorMessage(conn));
+ exit_nicely(conn);
+ }
+
+ res = PQexec(conn, "SET lc_messages TO \"C\"");
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ pg_fatal("failed to set \"lc_messages\": %s", PQerrorMessage(conn));
+ PQclear(res);
+
+ if (strcmp(testname, "cursor_options_without_extension") == 0)
+ test_cursor_options_without_extension(conn);
+ else if (strcmp(testname, "dml_with_cursor_options") == 0)
+ test_dml_with_cursor_options(conn);
+ else if (strcmp(testname, "holdable_scroll_cursor") == 0)
+ test_holdable_scroll_cursor(conn);
+ else if (strcmp(testname, "holdable_cursor") == 0)
+ test_holdable_cursor(conn);
+ else if (strcmp(testname, "invalid_flags_rejected") == 0)
+ test_invalid_flags_rejected(conn);
+ else if (strcmp(testname, "no_scroll_cursor") == 0)
+ test_no_scroll_cursor(conn);
+ else if (strcmp(testname, "scroll_cursor") == 0)
+ test_scroll_cursor(conn);
+ else if (strcmp(testname, "unnamed_portal_rejected") == 0)
+ test_unnamed_portal_rejected(conn);
+ else
+ {
+ fprintf(stderr, "\"%s\" is not a recognized test name\n", testname);
+ exit(1);
+ }
+
+ PQfinish(conn);
+ return 0;
+}
diff --git a/src/test/modules/libpq_protocol_cursor/meson.build b/src/test/modules/libpq_protocol_cursor/meson.build
new file mode 100644
index 00000000000..cc7624012cb
--- /dev/null
+++ b/src/test/modules/libpq_protocol_cursor/meson.build
@@ -0,0 +1,32 @@
+# Copyright (c) 2022-2026, PostgreSQL Global Development Group
+
+libpq_protocol_cursor_sources = files(
+ 'libpq_protocol_cursor.c',
+)
+
+if host_system == 'windows'
+ libpq_protocol_cursor_sources += rc_bin_gen.process(win32ver_rc, extra_args: [
+ '--NAME', 'libpq_protocol_cursor',
+ '--FILEDESC', 'libpq_protocol_cursor - test program for extended query protocol cursors',])
+endif
+
+libpq_protocol_cursor = executable('libpq_protocol_cursor',
+ libpq_protocol_cursor_sources,
+ dependencies: [frontend_code, libpq],
+ kwargs: default_bin_args + {
+ 'install': false,
+ },
+)
+testprep_targets += libpq_protocol_cursor
+
+tests += {
+ 'name': 'libpq_protocol_cursor',
+ 'sd': meson.current_source_dir(),
+ 'bd': meson.current_build_dir(),
+ 'tap': {
+ 'tests': [
+ 't/001_libpq_protocol_cursor.pl',
+ ],
+ 'deps': [libpq_protocol_cursor],
+ },
+}
diff --git a/src/test/modules/libpq_protocol_cursor/t/001_libpq_protocol_cursor.pl b/src/test/modules/libpq_protocol_cursor/t/001_libpq_protocol_cursor.pl
new file mode 100644
index 00000000000..860631c8947
--- /dev/null
+++ b/src/test/modules/libpq_protocol_cursor/t/001_libpq_protocol_cursor.pl
@@ -0,0 +1,42 @@
+# Copyright (c) 2024-2026, PostgreSQL Global Development Group
+
+use strict;
+use warnings FATAL => 'all';
+
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+my $node = PostgreSQL::Test::Cluster->new('main');
+$node->init;
+$node->start;
+
+my ($out, $err) = run_command(['libpq_protocol_cursor', 'tests']);
+die "oops: $err" unless $err eq '';
+my @tests = split(/\s+/, $out);
+
+for my $testname (@tests)
+{
+ # cursor_options_without_extension must run without protocol_cursor enabled
+ my $connstr = $node->connstr('postgres');
+ if ($testname eq 'cursor_options_without_extension')
+ {
+ $connstr .= " protocol_cursor=0";
+ }
+ else
+ {
+ $connstr .= " protocol_cursor=1 max_protocol_version=latest";
+ }
+
+ $node->command_ok(
+ [
+ 'libpq_protocol_cursor',
+ $testname,
+ $connstr
+ ],
+ "libpq_protocol_cursor $testname");
+}
+
+$node->stop('fast');
+
+done_testing();
diff --git a/src/test/modules/meson.build b/src/test/modules/meson.build
index 3ac291656c1..5627a274164 100644
--- a/src/test/modules/meson.build
+++ b/src/test/modules/meson.build
@@ -9,6 +9,7 @@ subdir('gin')
subdir('index')
subdir('injection_points')
subdir('ldap_password_func')
+subdir('libpq_protocol_cursor')
subdir('libpq_pipeline')
subdir('nbtree')
subdir('oauth_validator')
--
2.47.3
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], [email protected]
Subject: Re: Proposal to allow setting cursor options on Portals
In-Reply-To: <CADK3HH+AKcTUas-W7ZodeVzfwJgXWQH8NM1O=-550eU-hgrB5A@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