public inbox for [email protected]
help / color / mirror / Atom feedRe: Proposal to allow setting cursor options on Portals
24+ messages / 7 participants
[nested] [flat]
* Re: Proposal to allow setting cursor options on Portals
@ 2026-01-08 00:39 Jacob Champion <[email protected]>
2026-01-08 02:51 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
2026-01-08 07:38 ` Re: Proposal to allow setting cursor options on Portals Jelte Fennema-Nio <[email protected]>
2026-01-08 19:22 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
0 siblings, 3 replies; 24+ messages in thread
From: Jacob Champion @ 2026-01-08 00:39 UTC (permalink / raw)
To: Jelte Fennema-Nio <[email protected]>; +Cc: Dave Cramer <[email protected]>; PostgreSQL Hackers <[email protected]>; Heikki Linnakangas <[email protected]>
On Wed, Jan 7, 2026 at 6:48 AM Jelte Fennema-Nio <[email protected]> wrote:
> I had a quick discord chat with Dave. And we don't disagree much with
> each other: We both would like to use a version bump for these kinds
> of very simple to implement features.
I asked because I'm worried that the strongest technical argument for
this strategy is "it's simpler (for us)", outweighing all
consideration of downstream consequences. I'm not really on board with
that.
Dave seems not to be particularly worried about our compatibility with
third parties. You seem to be hoping to _force_ clients to update,
even if they disagree with you that they need the new features. I
think I'm on record as saying these are both bad starting points when
making changes to a widely implemented protocol. (If not, now I am.)
That combination will burn hard-earned trust and goodwill.
> Having a single version is only 1 option,
Seems like clients must support 3.0 up to 3.N in practice, and test
all of those. If you want a feature in 3.6 and the server says it only
supports 3.4, you're speaking 3.4 now. That's still N options.
You're saying "well hopefully clients don't actually have to support
all of them," but I don't think you gave a reason why that would be
okay for a production implementation. Is there an unstated assumption
here, that we'll eventually drop support for 3.0 at some point
relatively soon? (And then 3.2, and then...) If so, I'd prefer to
focus the conversation on that assumption. Because that seems like a
complete nonstarter to me, personally.
--Jacob
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Proposal to allow setting cursor options on Portals
2026-01-08 00:39 Re: Proposal to allow setting cursor options on Portals Jacob Champion <[email protected]>
@ 2026-01-08 02:51 ` Tom Lane <[email protected]>
2 siblings, 0 replies; 24+ messages in thread
From: Tom Lane @ 2026-01-08 02:51 UTC (permalink / raw)
To: Jacob Champion <[email protected]>; +Cc: Jelte Fennema-Nio <[email protected]>; Dave Cramer <[email protected]>; PostgreSQL Hackers <[email protected]>; Heikki Linnakangas <[email protected]>
Jacob Champion <[email protected]> writes:
> You're saying "well hopefully clients don't actually have to support
> all of them," but I don't think you gave a reason why that would be
> okay for a production implementation. Is there an unstated assumption
> here, that we'll eventually drop support for 3.0 at some point
> relatively soon? (And then 3.2, and then...) If so, I'd prefer to
> focus the conversation on that assumption. Because that seems like a
> complete nonstarter to me, personally.
After we introduced protocol version 3.0, it took us 18 years to drop
support for version 2 (2003..2021). We introduced 3.2 in 2025, so on
the same schedule we won't drop 3.0 support before 2043. If anyone's
thinking that it can happen significantly faster this time around,
I'm here to disabuse you of that notion. Considering how much larger
the Postgres ecosystem is now than it was in 2003, it will probably
take *longer* for everything to be sufficiently on-board for another
hard break.
I'm pretty bemused by this entire discussion. We have a perfectly
good design for handling new protocol features without any hard
protocol break, so I don't understand why people are insisting on
doing things incompatibly when they could be doing them compatibly.
I quote from Robert's commit ae65f6066 (the same one that invented
NegotiateProtocolVersion):
In addition, if the startup packet includes name/value pairs where
the name starts with "_pq_.", assume that those are protocol options,
not GUCs. Include those we don't support (i.e. all of them, at
present) in the NegotiateProtocolVersion message so that the client
knows they were not understood. This makes it possible for the
client to request previously-unsupported features without bumping
the protocol version at all; the client can tell from the server's
response whether the option was understood.
I think that the right way forward is that the protocol version
stays at 3.2 for several decades more, and we implement requests for
individual protocol-level features through the "_pq_." mechanism.
I would not have any use for 3.2 as such at all, except that
asking for that is needed to ensure that the server knows about
NegotiateProtocolVersion.
regards, tom lane
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Proposal to allow setting cursor options on Portals
2026-01-08 00:39 Re: Proposal to allow setting cursor options on Portals Jacob Champion <[email protected]>
@ 2026-01-08 07:38 ` Jelte Fennema-Nio <[email protected]>
2 siblings, 0 replies; 24+ messages in thread
From: Jelte Fennema-Nio @ 2026-01-08 07:38 UTC (permalink / raw)
To: Jacob Champion <[email protected]>; +Cc: Dave Cramer <[email protected]>; PostgreSQL Hackers <[email protected]>; Heikki Linnakangas <[email protected]>
On Thu, 8 Jan 2026 at 01:39, Jacob Champion
<[email protected]> wrote:
> > Having a single version is only 1 option,
>
> Seems like clients must support 3.0 up to 3.N in practice, and test
> all of those. If you want a feature in 3.6 and the server says it only
> supports 3.4, you're speaking 3.4 now. That's still N options.
I meant 1 per yearly set of protocol features. So if there's M years
with protocol features and on average N features per each of those
years the amount of different options to consider over time are:
For only version bumps: M
For completely orthogonal protocol extensions: M*N
For completely non-orthogonal options: (M*N)^2
> You're saying "well hopefully clients don't actually have to support
> all of them," but I don't think you gave a reason why that would be
> okay for a production implementation.
Clients don't have to care about every postgres version that ever
existed, or every possible proxy in existence. In 5 years, a client
author might just say: well I only care about my client being able to
connect to supported postgres servers, so if a server downgrades to
protocol 3.0 I close the connection.
This is a decision for the client author to make and for the client
author only. And they don't need to care about any of the other
clients in existence at all. Only the servers their client should be
able to connect to.
> Is there an unstated assumption
> here, that we'll eventually drop support for 3.0 at some point
> relatively soon? (And then 3.2, and then...) If so, I'd prefer to
> focus the conversation on that assumption. Because that seems like a
> complete nonstarter to me, personally.
No, that's not what I meant. Because postgres has to care about the
clients it wants to support. And that's all of them. There will
probably be clients supporting only 3.0 for a very long time. So the
postgres server will need to be supporting 3.0 for a very long time.
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Proposal to allow setting cursor options on Portals
2026-01-08 00:39 Re: Proposal to allow setting cursor options on Portals Jacob Champion <[email protected]>
@ 2026-01-08 19:22 ` Tom Lane <[email protected]>
2026-01-09 18:20 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2 siblings, 1 reply; 24+ messages in thread
From: Tom Lane @ 2026-01-08 19:22 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Jelte Fennema-Nio <[email protected]>; Jacob Champion <[email protected]>; Dave Cramer <[email protected]>; PostgreSQL Hackers <[email protected]>; Heikki Linnakangas <[email protected]>
Robert Haas <[email protected]> writes:
> That sounds like the right approach to me. Note that I have also
> previously expressed my disagreement with the idea of bumping the
> protocol version regularly. I'm not entirely comfortable with the idea
> of using protocol extensions for everything, because I really imagined
> that they would be used for larger features that made a cluster of
> related changes rather than solitary changes, and that there wouldn't
> be many of them.
I kind of doubt that there will ever be many of them, but if we start
to feel like there's a lot, we could invent abbreviations: single
feature names that clients can ask for that are defined to represent
a particular set of older features. But I'd argue that those sets
should be groups of related functions, not "whatever random stuff
exists as of Postgres 27". I think it'll be highly useful for clients
to declare which features they want, rather than leave people
wondering exactly which features this client intends to support.
regards, tom lane
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Proposal to allow setting cursor options on Portals
2026-01-08 00:39 Re: Proposal to allow setting cursor options on Portals Jacob Champion <[email protected]>
2026-01-08 19:22 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
@ 2026-01-09 18:20 ` Dave Cramer <[email protected]>
2026-01-14 19:24 ` Re: Proposal to allow setting cursor options on Portals Robert Haas <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Dave Cramer @ 2026-01-09 18:20 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Robert Haas <[email protected]>; Jelte Fennema-Nio <[email protected]>; Jacob Champion <[email protected]>; PostgreSQL Hackers <[email protected]>; Heikki Linnakangas <[email protected]>
On Thu, 8 Jan 2026 at 14:22, Tom Lane <[email protected]> wrote:
> Robert Haas <[email protected]> writes:
> > That sounds like the right approach to me. Note that I have also
> > previously expressed my disagreement with the idea of bumping the
> > protocol version regularly. I'm not entirely comfortable with the idea
> > of using protocol extensions for everything, because I really imagined
> > that they would be used for larger features that made a cluster of
> > related changes rather than solitary changes, and that there wouldn't
> > be many of them.
>
> I kind of doubt that there will ever be many of them, but if we start
> to feel like there's a lot, we could invent abbreviations: single
> feature names that clients can ask for that are defined to represent
> a particular set of older features. But I'd argue that those sets
> should be groups of related functions, not "whatever random stuff
> exists as of Postgres 27". I think it'll be highly useful for clients
> to declare which features they want, rather than leave people
> wondering exactly which features this client intends to support.
>
> regards, tom lane
>
For the particular case of adding the ability to create holdable cursors at
the protocol level which is what my patch is concerned with I don't think
it's even necessary to bump the protocol or create a protocol extension.
The change in the message is backward compatible and clients only need to
know that after version 18 they can use the message to create a holdable
cursor. I would argue that this patch is just rectifying an oversight in
the original protocol.
Dave
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Proposal to allow setting cursor options on Portals
2026-01-08 00:39 Re: Proposal to allow setting cursor options on Portals Jacob Champion <[email protected]>
2026-01-08 19:22 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
2026-01-09 18:20 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
@ 2026-01-14 19:24 ` Robert Haas <[email protected]>
2026-01-14 23:12 ` Re: Proposal to allow setting cursor options on Portals Jelte Fennema-Nio <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Robert Haas @ 2026-01-14 19:24 UTC (permalink / raw)
To: Dave Cramer <[email protected]>; +Cc: Tom Lane <[email protected]>; Jelte Fennema-Nio <[email protected]>; Jacob Champion <[email protected]>; PostgreSQL Hackers <[email protected]>; Heikki Linnakangas <[email protected]>
On Fri, Jan 9, 2026 at 1:20 PM Dave Cramer <[email protected]> wrote:
> For the particular case of adding the ability to create holdable cursors at the protocol level which is what my patch is concerned with I don't think it's even necessary to bump the protocol or create a protocol extension.
> The change in the message is backward compatible and clients only need to know that after version 18 they can use the message to create a holdable cursor. I would argue that this patch is just rectifying an oversight in the original protocol.
I'm not sure what I think about the patch itself, but I find myself
somewhat in agreement with this logic. If the server is supposed to
start sending something different to the client, or the client must
send something different to the server, that's clearly got to be
negotiated. But I wonder whether we should just consider slipping
things like this into the protocol without bumping the version at all.
That makes me a bit nervous because it kind of makes a mockery of the
idea of a version number, but it's also not entirely without
precedent. For example, COPY BOTH mode didn't use to exist, and now it
does, and the protocol version number didn't change in the process.
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Proposal to allow setting cursor options on Portals
2026-01-08 00:39 Re: Proposal to allow setting cursor options on Portals Jacob Champion <[email protected]>
2026-01-08 19:22 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
2026-01-09 18:20 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-14 19:24 ` Re: Proposal to allow setting cursor options on Portals Robert Haas <[email protected]>
@ 2026-01-14 23:12 ` Jelte Fennema-Nio <[email protected]>
2026-01-14 23:30 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Jelte Fennema-Nio @ 2026-01-14 23:12 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Dave Cramer <[email protected]>; Tom Lane <[email protected]>; Jacob Champion <[email protected]>; PostgreSQL Hackers <[email protected]>; Heikki Linnakangas <[email protected]>
On Wed, 14 Jan 2026 at 20:24, Robert Haas <[email protected]> wrote:
> But I wonder whether we should just consider slipping
> things like this into the protocol without bumping the version at all.
I feel like I've said this many times already, but I really do not
understand why there's such a hesitation on bumping the minor protocol
version. Bumping the minor protocol version has zero downsides to me.
IMO we could bump it every PG release even if we don't make any
changes to the protocol. Nothing would break. The only thing that
breaks things is requesting anything other than 3.0, because most
proxies haven't implemented NegotiateProtocolVersion. But hopefully
that will be a concern of the past once Jacob merges the grease
stuff[1]. After that it doesn't matter what minor version is
requested, if it's 3.2, 3.3, or 3.456. Requesting any of those will
not cause breakage.
> That makes me a bit nervous because it kind of makes a mockery of the
> idea of a version number, but it's also not entirely without
> precedent. For example, COPY BOTH mode didn't use to exist, and now it
> does, and the protocol version number didn't change in the process.
I think CopyBoth is very special, because it's only used in logical
replication. In a sense it's practically a protocol extension, because
it is only sent by the server if the client explicitly requested it
through a keyword in the StartupMessage (replication=database).
As a maintainer of a proxy, I'd be pretty annoyed if clients start
sending fields or messages that I don't expect, without *something* in
the StartupMessage advertising that that will happen. This specific
patch would be fine for pgbouncer, because it only parses the first
few fields of the Bind message. But e.g pgcat seems to parse the whole
Bind message[2]
[1]: https://www.postgresql.org/message-id/flat/[email protected]
[2]: https://github.com/postgresml/pgcat/blob/5b038813eb14f181434ab7b5509e74d9b1fe123b/src/messages.rs#L1...
P.S. This *really* is my final on-list message on the topic of bumping
protocol version numbers . Off-list I'm happy to explain to people and
maybe I'll reconsider re-opening the discussion in a few years. But
the current discussion is only going in circles, and I'd rather spend
my energy on something that is useful.
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Proposal to allow setting cursor options on Portals
2026-01-08 00:39 Re: Proposal to allow setting cursor options on Portals Jacob Champion <[email protected]>
2026-01-08 19:22 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
2026-01-09 18:20 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-14 19:24 ` Re: Proposal to allow setting cursor options on Portals Robert Haas <[email protected]>
2026-01-14 23:12 ` Re: Proposal to allow setting cursor options on Portals Jelte Fennema-Nio <[email protected]>
@ 2026-01-14 23:30 ` Tom Lane <[email protected]>
2026-01-15 10:33 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Tom Lane @ 2026-01-14 23:30 UTC (permalink / raw)
To: Jelte Fennema-Nio <[email protected]>; +Cc: Robert Haas <[email protected]>; Dave Cramer <[email protected]>; Jacob Champion <[email protected]>; PostgreSQL Hackers <[email protected]>; Heikki Linnakangas <[email protected]>
Jelte Fennema-Nio <[email protected]> writes:
> I feel like I've said this many times already, but I really do not
> understand why there's such a hesitation on bumping the minor protocol
> version. Bumping the minor protocol version has zero downsides to me.
I think you have that backwards. The right way to think about it
is that bumping the minor version has zero upside. What we actually
want is for the client and server to agree on what specific optional
features they will use, and we have a design that allows doing that
in a fine-grained, extensible way. We don't need to change the
protocol version number ever again, as long as we use protocol
options correctly.
Having said that, I share Robert's distaste for "silent" protocol
bumps that change the behavior without any negotiation.
regards, tom lane
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Proposal to allow setting cursor options on Portals
2026-01-08 00:39 Re: Proposal to allow setting cursor options on Portals Jacob Champion <[email protected]>
2026-01-08 19:22 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
2026-01-09 18:20 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-14 19:24 ` Re: Proposal to allow setting cursor options on Portals Robert Haas <[email protected]>
2026-01-14 23:12 ` Re: Proposal to allow setting cursor options on Portals Jelte Fennema-Nio <[email protected]>
2026-01-14 23:30 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
@ 2026-01-15 10:33 ` Dave Cramer <[email protected]>
2026-01-15 19:00 ` Re: Proposal to allow setting cursor options on Portals Robert Haas <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Dave Cramer @ 2026-01-15 10:33 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Jelte Fennema-Nio <[email protected]>; Robert Haas <[email protected]>; Jacob Champion <[email protected]>; PostgreSQL Hackers <[email protected]>; Heikki Linnakangas <[email protected]>
Dave Cramer
On Wed, 14 Jan 2026 at 18:30, Tom Lane <[email protected]> wrote:
> Jelte Fennema-Nio <[email protected]> writes:
> > I feel like I've said this many times already, but I really do not
> > understand why there's such a hesitation on bumping the minor protocol
> > version. Bumping the minor protocol version has zero downsides to me.
>
> I think you have that backwards. The right way to think about it
> is that bumping the minor version has zero upside. What we actually
> want is for the client and server to agree on what specific optional
> features they will use, and we have a design that allows doing that
> in a fine-grained, extensible way. We don't need to change the
> protocol version number ever again, as long as we use protocol
> options correctly.
>
I would argue in the case of "cursor with hold" this should have been in
the original protocol.
This is not an added feature this just enables an existing feature in the
server. This is not unlike widening the cancel key.
Something like encryption would be a feature that I could see using the
extension mechanism
>
> Having said that, I share Robert's distaste for "silent" protocol
> bumps that change the behavior without any negotiation.
>
My understanding reading his message he was in favour of it
As for proxies or "middleboxes" I will concede that not advertising that we
are going to change that message is a non-starter
Dave
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Proposal to allow setting cursor options on Portals
2026-01-08 00:39 Re: Proposal to allow setting cursor options on Portals Jacob Champion <[email protected]>
2026-01-08 19:22 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
2026-01-09 18:20 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-14 19:24 ` Re: Proposal to allow setting cursor options on Portals Robert Haas <[email protected]>
2026-01-14 23:12 ` Re: Proposal to allow setting cursor options on Portals Jelte Fennema-Nio <[email protected]>
2026-01-14 23:30 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
2026-01-15 10:33 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
@ 2026-01-15 19:00 ` Robert Haas <[email protected]>
2026-01-15 19:11 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Robert Haas @ 2026-01-15 19:00 UTC (permalink / raw)
To: Dave Cramer <[email protected]>; +Cc: Tom Lane <[email protected]>; Jelte Fennema-Nio <[email protected]>; Jacob Champion <[email protected]>; PostgreSQL Hackers <[email protected]>; Heikki Linnakangas <[email protected]>
On Thu, Jan 15, 2026 at 5:33 AM Dave Cramer <[email protected]> wrote:
> I would argue in the case of "cursor with hold" this should have been in the original protocol.
> This is not an added feature this just enables an existing feature in the server. This is not unlike widening the cancel key.
> Something like encryption would be a feature that I could see using the extension mechanism
I agree that encryption is a better fit for the extension mechanism.
But I don't really agree that this isn't a new feature. Exposing
existing functionality in new ways counts as a new feature in my book.
>> Having said that, I share Robert's distaste for "silent" protocol
>> bumps that change the behavior without any negotiation.
>
> My understanding reading his message he was in favour of it
Well, my feelings are mixed on that point, honestly. If the server
needs to know whether the client supports something, you pretty much
have to have a protocol negotiation of some kind, whether that's a
version bump or an extension. Otherwise, the server simply has no way
to find out what the client supports. In the opposite direction, it's
more fuzzy: the client can see the server version number, and so can
draw some conclusions on that basis. We have used this for protocol
changes in the past. However, something like changing the cancel key
or adding cursor with hold affects a lot more clients than adding
CopyBoth that is only triggered by a very specific command in a
special mode. And it's not that theoretically appealing to conflate
the *protocol* version number with the *server* version number.
Sometimes things that are not theoretically appealing are nonetheless
pragmatically appealing. But it is not really clear to me whether this
is one of those cases, and it sounds like Tom and Jacob think it
isn't. I could probably be persuaded either way on the best thing to
do here in a vacuum, but I'm strongly disinclined to argue against two
committers who have a firm position on the topic already.
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.
> As for proxies or "middleboxes" I will concede that not advertising that we are going to change that message is a non-starter
Yeah.
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Proposal to allow setting cursor options on Portals
2026-01-08 00:39 Re: Proposal to allow setting cursor options on Portals Jacob Champion <[email protected]>
2026-01-08 19:22 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
2026-01-09 18:20 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-14 19:24 ` Re: Proposal to allow setting cursor options on Portals Robert Haas <[email protected]>
2026-01-14 23:12 ` Re: Proposal to allow setting cursor options on Portals Jelte Fennema-Nio <[email protected]>
2026-01-14 23:30 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
2026-01-15 10:33 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-15 19:00 ` Re: Proposal to allow setting cursor options on Portals Robert Haas <[email protected]>
@ 2026-01-15 19:11 ` Dave Cramer <[email protected]>
2026-01-15 21:06 ` Re: Proposal to allow setting cursor options on Portals Hannu Krosing <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Dave Cramer @ 2026-01-15 19:11 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Tom Lane <[email protected]>; Jelte Fennema-Nio <[email protected]>; Jacob Champion <[email protected]>; PostgreSQL Hackers <[email protected]>; Heikki Linnakangas <[email protected]>
On Thu, 15 Jan 2026 at 14:00, Robert Haas <[email protected]> wrote:
> On Thu, Jan 15, 2026 at 5:33 AM Dave Cramer <[email protected]> wrote:
> > I would argue in the case of "cursor with hold" this should have been in
> the original protocol.
> > This is not an added feature this just enables an existing feature in
> the server. This is not unlike widening the cancel key.
> > Something like encryption would be a feature that I could see using the
> extension mechanism
>
> I agree that encryption is a better fit for the extension mechanism.
> But I don't really agree that this isn't a new feature. Exposing
> existing functionality in new ways counts as a new feature in my book.
>
> >> Having said that, I share Robert's distaste for "silent" protocol
> >> bumps that change the behavior without any negotiation.
> >
> > My understanding reading his message he was in favour of it
>
> Well, my feelings are mixed on that point, honestly. If the server
> needs to know whether the client supports something, you pretty much
> have to have a protocol negotiation of some kind, whether that's a
> version bump or an extension. Otherwise, the server simply has no way
> to find out what the client supports. In the opposite direction, it's
> more fuzzy: the client can see the server version number, and so can
> draw some conclusions on that basis. We have used this for protocol
> changes in the past. However, something like changing the cancel key
> or adding cursor with hold affects a lot more clients than adding
> CopyBoth that is only triggered by a very specific command in a
> special mode. And it's not that theoretically appealing to conflate
> the *protocol* version number with the *server* version number.
> Sometimes things that are not theoretically appealing are nonetheless
> pragmatically appealing. But it is not really clear to me whether this
> is one of those cases, and it sounds like Tom and Jacob think it
> isn't. I could probably be persuaded either way on the best thing to
> do here in a vacuum, but I'm strongly disinclined to argue against two
> committers who have a firm position on the topic already.
>
> 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.
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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Proposal to allow setting cursor options on Portals
2026-01-08 00:39 Re: Proposal to allow setting cursor options on Portals Jacob Champion <[email protected]>
2026-01-08 19:22 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
2026-01-09 18:20 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-14 19:24 ` Re: Proposal to allow setting cursor options on Portals Robert Haas <[email protected]>
2026-01-14 23:12 ` Re: Proposal to allow setting cursor options on Portals Jelte Fennema-Nio <[email protected]>
2026-01-14 23:30 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
2026-01-15 10:33 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-15 19:00 ` Re: Proposal to allow setting cursor options on Portals Robert Haas <[email protected]>
2026-01-15 19:11 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
@ 2026-01-15 21:06 ` Hannu Krosing <[email protected]>
2026-03-04 14:26 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Hannu Krosing @ 2026-01-15 21:06 UTC (permalink / raw)
To: Dave Cramer <[email protected]>; +Cc: Robert Haas <[email protected]>; Tom Lane <[email protected]>; Jelte Fennema-Nio <[email protected]>; Jacob Champion <[email protected]>; PostgreSQL Hackers <[email protected]>; Heikki Linnakangas <[email protected]>
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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Proposal to allow setting cursor options on Portals
2026-01-08 00:39 Re: Proposal to allow setting cursor options on Portals Jacob Champion <[email protected]>
2026-01-08 19:22 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
2026-01-09 18:20 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-14 19:24 ` Re: Proposal to allow setting cursor options on Portals Robert Haas <[email protected]>
2026-01-14 23:12 ` Re: Proposal to allow setting cursor options on Portals Jelte Fennema-Nio <[email protected]>
2026-01-14 23:30 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
2026-01-15 10:33 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-15 19:00 ` Re: Proposal to allow setting cursor options on Portals Robert Haas <[email protected]>
2026-01-15 19:11 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-15 21:06 ` Re: Proposal to allow setting cursor options on Portals Hannu Krosing <[email protected]>
@ 2026-03-04 14:26 ` Dave Cramer <[email protected]>
2026-03-04 15:35 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Dave Cramer @ 2026-03-04 14:26 UTC (permalink / raw)
To: Hannu Krosing <[email protected]>; +Cc: Robert Haas <[email protected]>; Tom Lane <[email protected]>; Jelte Fennema-Nio <[email protected]>; Jacob Champion <[email protected]>; PostgreSQL Hackers <[email protected]>; Heikki Linnakangas <[email protected]>
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
See new version attached
Attachments:
[application/octet-stream] 0001-wip-holdable-portals.patch (23.6K, 3-0001-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)
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Proposal to allow setting cursor options on Portals
2026-01-08 00:39 Re: Proposal to allow setting cursor options on Portals Jacob Champion <[email protected]>
2026-01-08 19:22 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
2026-01-09 18:20 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-14 19:24 ` Re: Proposal to allow setting cursor options on Portals Robert Haas <[email protected]>
2026-01-14 23:12 ` Re: Proposal to allow setting cursor options on Portals Jelte Fennema-Nio <[email protected]>
2026-01-14 23:30 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
2026-01-15 10:33 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-15 19:00 ` Re: Proposal to allow setting cursor options on Portals Robert Haas <[email protected]>
2026-01-15 19:11 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-15 21:06 ` Re: Proposal to allow setting cursor options on Portals Hannu Krosing <[email protected]>
2026-03-04 14:26 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
@ 2026-03-04 15:35 ` Dave Cramer <[email protected]>
2026-03-17 14:41 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Dave Cramer @ 2026-03-04 15:35 UTC (permalink / raw)
To: Hannu Krosing <[email protected]>; +Cc: Robert Haas <[email protected]>; Tom Lane <[email protected]>; Jelte Fennema-Nio <[email protected]>; Jacob Champion <[email protected]>; PostgreSQL Hackers <[email protected]>; Heikki Linnakangas <[email protected]>
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)
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Proposal to allow setting cursor options on Portals
2026-01-08 00:39 Re: Proposal to allow setting cursor options on Portals Jacob Champion <[email protected]>
2026-01-08 19:22 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
2026-01-09 18:20 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-14 19:24 ` Re: Proposal to allow setting cursor options on Portals Robert Haas <[email protected]>
2026-01-14 23:12 ` Re: Proposal to allow setting cursor options on Portals Jelte Fennema-Nio <[email protected]>
2026-01-14 23:30 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
2026-01-15 10:33 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-15 19:00 ` Re: Proposal to allow setting cursor options on Portals Robert Haas <[email protected]>
2026-01-15 19:11 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-15 21:06 ` Re: Proposal to allow setting cursor options on Portals Hannu Krosing <[email protected]>
2026-03-04 14:26 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-04 15:35 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
@ 2026-03-17 14:41 ` Dave Cramer <[email protected]>
2026-03-18 18:00 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Dave Cramer @ 2026-03-17 14:41 UTC (permalink / raw)
To: Hannu Krosing <[email protected]>; +Cc: Robert Haas <[email protected]>; Tom Lane <[email protected]>; Jelte Fennema-Nio <[email protected]>; Jacob Champion <[email protected]>; PostgreSQL Hackers <[email protected]>; Heikki Linnakangas <[email protected]>
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)
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Proposal to allow setting cursor options on Portals
2026-01-08 00:39 Re: Proposal to allow setting cursor options on Portals Jacob Champion <[email protected]>
2026-01-08 19:22 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
2026-01-09 18:20 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-14 19:24 ` Re: Proposal to allow setting cursor options on Portals Robert Haas <[email protected]>
2026-01-14 23:12 ` Re: Proposal to allow setting cursor options on Portals Jelte Fennema-Nio <[email protected]>
2026-01-14 23:30 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
2026-01-15 10:33 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-15 19:00 ` Re: Proposal to allow setting cursor options on Portals Robert Haas <[email protected]>
2026-01-15 19:11 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-15 21:06 ` Re: Proposal to allow setting cursor options on Portals Hannu Krosing <[email protected]>
2026-03-04 14:26 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-04 15:35 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-17 14:41 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
@ 2026-03-18 18:00 ` Dave Cramer <[email protected]>
2026-03-19 00:52 ` Re: Proposal to allow setting cursor options on Portals Sami Imseih <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Dave Cramer @ 2026-03-18 18:00 UTC (permalink / raw)
To: Hannu Krosing <[email protected]>; +Cc: Robert Haas <[email protected]>; Tom Lane <[email protected]>; Jelte Fennema-Nio <[email protected]>; Jacob Champion <[email protected]>; PostgreSQL Hackers <[email protected]>; Heikki Linnakangas <[email protected]>
On Tue, 17 Mar 2026 at 10:41, Dave Cramer <[email protected]> wrote:
> 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
>>
>
Apparently that patch does not apply. Checked this one against master
Dave
>
Attachments:
[application/octet-stream] 0002-holdable-portals.patch (23.8K, 3-0002-holdable-portals.patch)
download | inline diff:
From db126429a87a2849b9e0d3d78ed24505a1787571 Mon Sep 17 00:00:00 2001
From: Dave Cramer <[email protected]>
Date: Fri, 5 Dec 2025 18:20:23 -0500
Subject: [PATCH] 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);
---
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 | 13 +
src/interfaces/libpq/fe-exec.c | 222 ++++++++++++++++++
src/interfaces/libpq/fe-protocol3.c | 4 +
src/interfaces/libpq/libpq-fe.h | 8 +
src/interfaces/libpq/libpq-int.h | 2 +
.../modules/libpq_pipeline/libpq_pipeline.c | 90 +++++++
11 files changed, 431 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 5abf276c898..b551f3470e6 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 b3563113219..a228a2f625e 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -1634,6 +1634,7 @@ exec_bind_message(StringInfo input_message)
int numParams;
int numRFormats;
int16 *rformats = NULL;
+ int cursorOptions = 0;
CachedPlanSource *psrc;
CachedPlan *cplan;
Portal portal;
@@ -2010,6 +2011,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);
/*
@@ -2058,6 +2066,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.
@@ -4943,7 +4971,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 1e3d5bd5867..572e46db7b4 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
+PQsendQueryPreparedWithCursorOptions 212
+PQsendBindWithCursorOptions 213
diff --git a/src/interfaces/libpq/fe-connect.c b/src/interfaces/libpq/fe-connect.c
index db9b4c8edbf..93b1ca521ad 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}
@@ -3732,6 +3736,10 @@ keep_going: /* We will come back to here until there is
* proceed without.
*/
+ /* Set holdable_portal_enabled flag based on connection parameter */
+ if (conn->holdable_portal && conn->holdable_portal[0] == '1')
+ conn->holdable_portal_enabled = true;
+
/* Build the startup packet. */
startpacket = pqBuildStartupPacket3(conn, &packetlen,
EnvironmentOptions);
@@ -8369,6 +8377,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..985f209d915 100644
--- a/src/interfaces/libpq/fe-protocol3.c
+++ b/src/interfaces/libpq/fe-protocol3.c
@@ -2521,6 +2521,10 @@ 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");
+
/* 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..c75bcf1b804 100644
--- a/src/interfaces/libpq/libpq-fe.h
+++ b/src/interfaces/libpq/libpq-fe.h
@@ -535,6 +535,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)
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Proposal to allow setting cursor options on Portals
2026-01-08 00:39 Re: Proposal to allow setting cursor options on Portals Jacob Champion <[email protected]>
2026-01-08 19:22 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
2026-01-09 18:20 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-14 19:24 ` Re: Proposal to allow setting cursor options on Portals Robert Haas <[email protected]>
2026-01-14 23:12 ` Re: Proposal to allow setting cursor options on Portals Jelte Fennema-Nio <[email protected]>
2026-01-14 23:30 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
2026-01-15 10:33 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-15 19:00 ` Re: Proposal to allow setting cursor options on Portals Robert Haas <[email protected]>
2026-01-15 19:11 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-15 21:06 ` Re: Proposal to allow setting cursor options on Portals Hannu Krosing <[email protected]>
2026-03-04 14:26 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-04 15:35 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-17 14:41 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-18 18:00 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
@ 2026-03-19 00:52 ` Sami Imseih <[email protected]>
2026-03-24 01:59 ` Re: Proposal to allow setting cursor options on Portals Sami Imseih <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Sami Imseih @ 2026-03-19 00:52 UTC (permalink / raw)
To: Dave Cramer <[email protected]>; +Cc: Hannu Krosing <[email protected]>; Robert Haas <[email protected]>; Tom Lane <[email protected]>; Jelte Fennema-Nio <[email protected]>; Jacob Champion <[email protected]>; PostgreSQL Hackers <[email protected]>; Heikki Linnakangas <[email protected]>
Hi,
> Apparently that patch does not apply. Checked this one against master
I spent some time today reviewing the discussion and testing the patch. I have
some comments:
# 1. Remove protocol 3.3 version bump, since we will be using the protocol
extension mechanism. Right?
+ * 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));
+
+ if (strcmp(value, "3.3") == 0)
+ {
+ *result = PG_PROTOCOL(3, 3);
+ return true;
+ }
# 2. Handle case where _pq_.holdable_portal is rejected in
pqGetNegotiateProtocolVersion3
I was testing on a patched client and unpatched server with a
connection string of:
"host=localhost dbname=postgres holdable_portal=1"
I received this error:
```
failed: received invalid protocol negotiation message: server reported
an unsupported parameter that was not requested
("_pq_.holdable_portal")
```
The patch only checks _pq_.test_protocol_negotiation (the grease test
parameter), but we also need to deal
with the rejected extension parameters, rather than fall through to the error.
@@ -1544,6 +1547,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_.holdable_portal") == 0)
+ {
+ conn->holdable_portal_enabled = false;
}
else
{
The above is the change I tested with.
This will be the pattern for other protocol extensions that will be
added in the future as well.
Also, should there also be an API such `PQholdablePortalEnabled` which
takes a connection and
returns if conn->holdable_portal_enabled is enabled? This lets the
client detect at
runtime whether the extension was enabled, or disabled because the
server rejected it.
# 3. Remove PQsendQueryPreparedWithCursorOptions
This was also asked earlier [1], but I don't see an answer. Why do we need this?
Wouldn't PQsendBindWithCursorOption() be sufficient?
# 4. PQsendBindWithCursorOptions
a. Looking at other PQSend patterns, it looking like
PQsendBindWithCursorOptions/PQsendQueryPreparedWithCursorOptions are
missing
`pqAppendCmdQueueEntry(conn, entry)` before returning.
b. The last message type sent in `PQsendBindWithCursorOptions` is a
describe, so the query class should be PGQUERY_DESCRIBE, right?
c. Require a named portal (not just for the HOLD case). An unnamed
portal will not be executed in this API, so
subsequent calls to this API with an unnamed portal will just
overwrite the last one.
# 5. Replace hex with constants defined in libpq-fe.h which mirror parsenodes.h
for example 0x0020 → PQ_CURSOR_OPT_HOLD, etc.
Although I am not sure how these could remain in sync with core.
# 6. In the test_holdable_cursor, can we use PQsendClosePortal() instead of
"CLOSE"? Not for this patch, but it will be good to also have an API
for FETCH.
[1] [https://www.postgresql.org/message-id/CADK3HH%2B9V58vJzCkgvMwk2fyaCtYwr-Dv5em7rXzgUiVrnpuFA%40mail.g...]
--
Sami Imseih
Amazon Web Services (AWS)
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Proposal to allow setting cursor options on Portals
2026-01-08 00:39 Re: Proposal to allow setting cursor options on Portals Jacob Champion <[email protected]>
2026-01-08 19:22 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
2026-01-09 18:20 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-14 19:24 ` Re: Proposal to allow setting cursor options on Portals Robert Haas <[email protected]>
2026-01-14 23:12 ` Re: Proposal to allow setting cursor options on Portals Jelte Fennema-Nio <[email protected]>
2026-01-14 23:30 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
2026-01-15 10:33 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-15 19:00 ` Re: Proposal to allow setting cursor options on Portals Robert Haas <[email protected]>
2026-01-15 19:11 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-15 21:06 ` Re: Proposal to allow setting cursor options on Portals Hannu Krosing <[email protected]>
2026-03-04 14:26 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-04 15:35 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-17 14:41 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-18 18:00 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-19 00:52 ` Re: Proposal to allow setting cursor options on Portals Sami Imseih <[email protected]>
@ 2026-03-24 01:59 ` Sami Imseih <[email protected]>
2026-03-24 10:01 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Sami Imseih @ 2026-03-24 01:59 UTC (permalink / raw)
To: Dave Cramer <[email protected]>; +Cc: Hannu Krosing <[email protected]>; Robert Haas <[email protected]>; Tom Lane <[email protected]>; Jelte Fennema-Nio <[email protected]>; Jacob Champion <[email protected]>; PostgreSQL Hackers <[email protected]>; Heikki Linnakangas <[email protected]>
Hi,
One thing that just occurred to me is why must we limit
the new protocol extension to holdable cursors?
PQsendBindWithCursorOptions() will work with any cursor option,
Maybe it should be called "_pq_.named_portal" or perhaps better "_pq_.cursor"?
--
Sami Imseih
Amazon Web Services (AWS)
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Proposal to allow setting cursor options on Portals
2026-01-08 00:39 Re: Proposal to allow setting cursor options on Portals Jacob Champion <[email protected]>
2026-01-08 19:22 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
2026-01-09 18:20 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-14 19:24 ` Re: Proposal to allow setting cursor options on Portals Robert Haas <[email protected]>
2026-01-14 23:12 ` Re: Proposal to allow setting cursor options on Portals Jelte Fennema-Nio <[email protected]>
2026-01-14 23:30 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
2026-01-15 10:33 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-15 19:00 ` Re: Proposal to allow setting cursor options on Portals Robert Haas <[email protected]>
2026-01-15 19:11 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-15 21:06 ` Re: Proposal to allow setting cursor options on Portals Hannu Krosing <[email protected]>
2026-03-04 14:26 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-04 15:35 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-17 14:41 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-18 18:00 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-19 00:52 ` Re: Proposal to allow setting cursor options on Portals Sami Imseih <[email protected]>
2026-03-24 01:59 ` Re: Proposal to allow setting cursor options on Portals Sami Imseih <[email protected]>
@ 2026-03-24 10:01 ` Dave Cramer <[email protected]>
2026-03-24 20:54 ` Re: Proposal to allow setting cursor options on Portals Sami Imseih <[email protected]>
2026-03-25 07:46 ` Re: Proposal to allow setting cursor options on Portals Jelte Fennema-Nio <[email protected]>
0 siblings, 2 replies; 24+ messages in thread
From: Dave Cramer @ 2026-03-24 10:01 UTC (permalink / raw)
To: Sami Imseih <[email protected]>; +Cc: Hannu Krosing <[email protected]>; Robert Haas <[email protected]>; Tom Lane <[email protected]>; Jelte Fennema-Nio <[email protected]>; Jacob Champion <[email protected]>; PostgreSQL Hackers <[email protected]>; Heikki Linnakangas <[email protected]>
On Mon, 23 Mar 2026 at 21:59, Sami Imseih <[email protected]> wrote:
> Hi,
>
> One thing that just occurred to me is why must we limit
> the new protocol extension to holdable cursors?
>
> PQsendBindWithCursorOptions() will work with any cursor option,
> Maybe it should be called "_pq_.named_portal" or perhaps better
> "_pq_.cursor"?
>
Well there are currently ways to name a portal with V3 so named portal
doesn't make sense.
_pq_.cursor would be fine.
Dave
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Proposal to allow setting cursor options on Portals
2026-01-08 00:39 Re: Proposal to allow setting cursor options on Portals Jacob Champion <[email protected]>
2026-01-08 19:22 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
2026-01-09 18:20 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-14 19:24 ` Re: Proposal to allow setting cursor options on Portals Robert Haas <[email protected]>
2026-01-14 23:12 ` Re: Proposal to allow setting cursor options on Portals Jelte Fennema-Nio <[email protected]>
2026-01-14 23:30 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
2026-01-15 10:33 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-15 19:00 ` Re: Proposal to allow setting cursor options on Portals Robert Haas <[email protected]>
2026-01-15 19:11 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-15 21:06 ` Re: Proposal to allow setting cursor options on Portals Hannu Krosing <[email protected]>
2026-03-04 14:26 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-04 15:35 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-17 14:41 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-18 18:00 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-19 00:52 ` Re: Proposal to allow setting cursor options on Portals Sami Imseih <[email protected]>
2026-03-24 01:59 ` Re: Proposal to allow setting cursor options on Portals Sami Imseih <[email protected]>
2026-03-24 10:01 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
@ 2026-03-24 20:54 ` Sami Imseih <[email protected]>
2026-03-25 07:27 ` Re: Proposal to allow setting cursor options on Portals Jelte Fennema-Nio <[email protected]>
1 sibling, 1 reply; 24+ messages in thread
From: Sami Imseih @ 2026-03-24 20:54 UTC (permalink / raw)
To: Dave Cramer <[email protected]>; +Cc: Hannu Krosing <[email protected]>; Robert Haas <[email protected]>; Tom Lane <[email protected]>; Jelte Fennema-Nio <[email protected]>; Jacob Champion <[email protected]>; PostgreSQL Hackers <[email protected]>; Heikki Linnakangas <[email protected]>
> Well there are currently ways to name a portal with V3 so named portal doesn't make sense.
>
> _pq_.cursor would be fine.
I spent some time today on this, and implemented this as a new protocol
extension called _pq_.protocol_cursor, and the libpq option will also have
the same name. Maybe someone else has a better name, but I like this
as it differentiates from a sql-level cursor. Maybe someone has a better
suggestion.
All the cursor options can be passed, though CURSOR_OPT_BINARY is
irrelevant in the extended query protocol as noted here [1]. Binary output is
controlled by the result format codes on the FETCH instead. So,
CURSOR_OPT_BINARY can be passed as a cursor option, but will be
silently ignored.
I added a new test module libpq_protocol_cursor that expands the
tests to cover all options, except for cursor sensitivity. Also, there
are tests for multiple flags, enabling/disabling the extension and
unnamed portal being used ( which should not be allowed ).
A few other comments to earlier points:
> Also, should there also be an API such `PQholdablePortalEnabled` which
> takes a connection and
I did not implement this and instead rely on
if (cursorOptions != 0 && !conn->protocol_cursor_enabled) to reject the
usage of the API. So, if the API is called with cursor options but
protocol_cursor is not enabled, we reject the attempt. So, I am
not sure this Enabled() API has any value after thinking about it a bit more.
> # 5. Replace hex with constants defined in libpq-fe.h which mirror parsenodes.h
> for example 0x0020 → PQ_CURSOR_OPT_HOLD, etc.
Also, did not implement this since as mentioned earlier, I am not sure
how to keep these in sync with core. The docs do mention that
parsenodes.h is to be referenced for the bitmasks that are defined
as CURSOR_OPT_*. That may be good enough.
See v3 attached.
[1] https://www.postgresql.org/docs/18/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY
Attachments:
[application/octet-stream] v3-0001-Add-_pq_.cursor-protocol-extension-for-cursor-opt.patch (48.8K, 2-v3-0001-Add-_pq_.cursor-protocol-extension-for-cursor-opt.patch)
download | inline diff:
From 94394e02ed1a542c2cdb20784d988ee7671e3907 Mon Sep 17 00:00:00 2001
From: Dave Cramer <[email protected]>
Date: Fri, 5 Dec 2025 18:20:23 -0500
Subject: [PATCH v3 1/1] Add _pq_.cursor protocol extension for cursor options
Add a protocol extension, _pq_.cursor, that allows clients
to pass CURSOR_OPT_* flags in Bind messages, enabling HOLD,
SCROLL, and NO_SCROLL on named portals without bumping the
protocol version. The extension appends an optional Int32
field to the Bind message when negotiated during connection
startup.
Add PQsendBindWithCursorOptions() to libpq, which sends
Bind+Describe to create a named portal with cursor options.
Non-zero options require the extension; zero options always
succeed. The cursor_protocol connection parameter controls
negotiation.
Also, a new test module is added.
---
doc/src/sgml/libpq.sgml | 63 +-
doc/src/sgml/protocol.sgml | 31 +-
src/backend/tcop/backend_startup.c | 21 +-
src/backend/tcop/postgres.c | 4 +
src/include/libpq/libpq-be.h | 1 +
src/interfaces/libpq/exports.txt | 1 +
src/interfaces/libpq/fe-connect.c | 11 +
src/interfaces/libpq/fe-exec.c | 131 +++
src/interfaces/libpq/fe-protocol3.c | 14 +
src/interfaces/libpq/libpq-fe.h | 4 +
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 | 799 ++++++++++++++++++
.../modules/libpq_protocol_cursor/meson.build | 32 +
.../t/001_libpq_protocol_cursor.pl | 42 +
src/test/modules/meson.build | 1 +
18 files changed, 1177 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..29a71cd7c06 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -5331,8 +5331,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 +5531,58 @@ 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
+ <symbol>CURSOR_OPT_*</symbol> flags defined in
+ <filename>src/include/nodes/parsenodes.h</filename>.
+ Note that <symbol>CURSOR_OPT_BINARY</symbol> has no effect here;
+ binary output is controlled by <literal>resultFormat</literal> on
+ the subsequent FETCH call instead.
+ </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 +5597,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 +5974,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..07fd2b3b659 100644
--- a/doc/src/sgml/protocol.sgml
+++ b/doc/src/sgml/protocol.sgml
@@ -346,9 +346,14 @@
<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 may include
+ an optional cursor options field to control portal behavior.
+ See <xref linkend="protocol-message-formats-Bind"/> for details.
+ </entry>
</row>
</tbody>
</tgroup>
@@ -1101,6 +1106,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 +4419,23 @@ psql "dbname=postgres replication=database" -c "IDENTIFY_SYSTEM;"
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term>Int32 (optional)</term>
+ <listitem>
+ <para>
+ Cursor options, only present if <literal>_pq_.protocol_cursor</literal>
+ is enabled. A bitmask of flags that control the behavior of
+ the portal being created. The supported flags are defined as
+ <symbol>CURSOR_OPT_*</symbol> in
+ <filename>src/include/nodes/parsenodes.h</filename>.
+ Note that <symbol>CURSOR_OPT_BINARY</symbol> has no effect here;
+ binary output is controlled by the result format codes in the
+ Bind message itself.
+ If this field is not present, no cursor options are applied.
+ </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..4347f1d2ff9 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -2010,6 +2010,10 @@ exec_bind_message(StringInfo input_message)
rformats[i] = pq_getmsgint(input_message, 2);
}
+ /* Get cursor options if present (_pq_.protocol_cursor enabled) */
+ if (MyProcPort->protocol_cursor_enabled &&
+ input_message->cursor < input_message->len)
+ portal->cursorOptions = pq_getmsgint(input_message, 4);
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..6464ce8bd51 100644
--- a/src/interfaces/libpq/exports.txt
+++ b/src/interfaces/libpq/exports.txt
@@ -211,3 +211,4 @@ PQdefaultAuthDataHook 208
PQfullProtocolVersion 209
appendPQExpBufferVA 210
PQgetThreadLock 211
+PQsendBindWithCursorOptions 212
diff --git a/src/interfaces/libpq/fe-connect.c b/src/interfaces/libpq/fe-connect.c
index db9b4c8edbf..feff1507408 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);
diff --git a/src/interfaces/libpq/fe-exec.c b/src/interfaces/libpq/fe-exec.c
index 203d388bdbf..2c72de2baef 100644
--- a/src/interfaces/libpq/fe-exec.c
+++ b/src/interfaces/libpq/fe-exec.c
@@ -1682,6 +1682,137 @@ 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
+ * (CURSOR_OPT_* from src/include/nodes/parsenodes.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 always succeeds and creates a plain named portal.
+ *
+ * Note: CURSOR_OPT_BINARY has no effect here; binary output is controlled
+ * by the resultFormat parameter on the subsequent FETCH call.
+ */
+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;
+ }
+
+ 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..16add226a6f 100644
--- a/src/interfaces/libpq/libpq-fe.h
+++ b/src/interfaces/libpq/libpq-fe.h
@@ -535,6 +535,10 @@ 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 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..33e7d21e596
--- /dev/null
+++ b/src/test/modules/libpq_protocol_cursor/libpq_protocol_cursor.c
@@ -0,0 +1,799 @@
+/*-------------------------------------------------------------------------
+ *
+ * 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"
+#include "port/pg_bswap.h"
+
+/*
+ * Cursor option flags from src/include/nodes/parsenodes.h.
+ * Duplicated here because frontend code cannot include server headers.
+ */
+#define CURSOR_OPT_SCROLL 0x0002
+#define CURSOR_OPT_NO_SCROLL 0x0004
+#define CURSOR_OPT_HOLD 0x0020
+
+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 CURSOR_OPT_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", CURSOR_OPT_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 binary cursor: create a portal with CURSOR_OPT_BINARY and verify
+ * results come back in binary format.
+ */
+static void
+test_binary_cursor(PGconn *conn)
+{
+ PGresult *res;
+
+ fprintf(stderr, "test_binary_cursor... ");
+
+ res = PQexec(conn, "BEGIN");
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ pg_fatal("BEGIN failed: %s", PQerrorMessage(conn));
+ PQclear(res);
+
+ res = PQprepare(conn, "binstmt", "SELECT 42::int4", 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));
+
+ /*
+ * Create portal without cursor options — binary output is controlled by
+ * resultFormat on the FETCH, not by CURSOR_OPT_BINARY.
+ */
+ if (PQsendBindWithCursorOptions(conn, "binstmt", 0, NULL, NULL, NULL, 0,
+ "binportal", 0) != 1)
+ pg_fatal("PQsendBindWithCursorOptions failed: %s", PQerrorMessage(conn));
+
+ /* FETCH with resultFormat=1 to request binary output */
+ if (PQsendQueryParams(conn, "FETCH ALL FROM binportal", 0, NULL, NULL, NULL, NULL, 1) != 1)
+ pg_fatal("FETCH failed: %s", PQerrorMessage(conn));
+
+ if (PQsendClosePortal(conn, "binportal") != 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 result */
+ res = confirm_result_status(conn, PGRES_TUPLES_OK);
+ if (PQntuples(res) != 1)
+ pg_fatal("expected 1 row, got %d", PQntuples(res));
+ if (PQfformat(res, 0) != 1)
+ pg_fatal("expected binary format (1), got %d", PQfformat(res, 0));
+ if (PQgetlength(res, 0, 0) != 4)
+ pg_fatal("expected 4-byte int4, got %d bytes", PQgetlength(res, 0, 0));
+ {
+ int32 val;
+
+ memcpy(&val, PQgetvalue(res, 0, 0), 4);
+ val = (int32) pg_ntoh32(val);
+ if (val != 42)
+ pg_fatal("expected value 42, got %d", val);
+ }
+ 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 scroll cursor: create a portal with CURSOR_OPT_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", CURSOR_OPT_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 CURSOR_OPT_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", CURSOR_OPT_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_hold_scroll_cursor(PGconn *conn)
+{
+ PGresult *res;
+
+ fprintf(stderr, "test_hold_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",
+ CURSOR_OPT_HOLD | CURSOR_OPT_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", CURSOR_OPT_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,
+ "", CURSOR_OPT_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, CURSOR_OPT_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... ");
+
+ 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", CURSOR_OPT_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);
+}
+
+static void
+print_test_list(void)
+{
+ printf("binary_cursor\n");
+ printf("dml_with_cursor_options\n");
+ printf("hold_scroll_cursor\n");
+ printf("holdable_cursor\n");
+ printf("no_scroll_cursor\n");
+ printf("scroll_cursor\n");
+ printf("unnamed_portal_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, "binary_cursor") == 0)
+ test_binary_cursor(conn);
+ else 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, "hold_scroll_cursor") == 0)
+ test_hold_scroll_cursor(conn);
+ else if (strcmp(testname, "holdable_cursor") == 0)
+ test_holdable_cursor(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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Proposal to allow setting cursor options on Portals
2026-01-08 00:39 Re: Proposal to allow setting cursor options on Portals Jacob Champion <[email protected]>
2026-01-08 19:22 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
2026-01-09 18:20 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-14 19:24 ` Re: Proposal to allow setting cursor options on Portals Robert Haas <[email protected]>
2026-01-14 23:12 ` Re: Proposal to allow setting cursor options on Portals Jelte Fennema-Nio <[email protected]>
2026-01-14 23:30 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
2026-01-15 10:33 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-15 19:00 ` Re: Proposal to allow setting cursor options on Portals Robert Haas <[email protected]>
2026-01-15 19:11 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-15 21:06 ` Re: Proposal to allow setting cursor options on Portals Hannu Krosing <[email protected]>
2026-03-04 14:26 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-04 15:35 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-17 14:41 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-18 18:00 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-19 00:52 ` Re: Proposal to allow setting cursor options on Portals Sami Imseih <[email protected]>
2026-03-24 01:59 ` Re: Proposal to allow setting cursor options on Portals Sami Imseih <[email protected]>
2026-03-24 10:01 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-24 20:54 ` Re: Proposal to allow setting cursor options on Portals Sami Imseih <[email protected]>
@ 2026-03-25 07:27 ` Jelte Fennema-Nio <[email protected]>
0 siblings, 0 replies; 24+ messages in thread
From: Jelte Fennema-Nio @ 2026-03-25 07:27 UTC (permalink / raw)
To: Sami Imseih <[email protected]>; +Cc: Dave Cramer <[email protected]>; Hannu Krosing <[email protected]>; Robert Haas <[email protected]>; Tom Lane <[email protected]>; Jacob Champion <[email protected]>; PostgreSQL Hackers <[email protected]>; Heikki Linnakangas <[email protected]>
On Tue, 24 Mar 2026 at 21:54, Sami Imseih <[email protected]> wrote:
> All the cursor options can be passed, though CURSOR_OPT_BINARY is
> irrelevant in the extended query protocol as noted here [1]. Binary output is
> controlled by the result format codes on the FETCH instead. So,
> CURSOR_OPT_BINARY can be passed as a cursor option, but will be
> silently ignored.
This is what this patch originally did in one of the earlier versions.
And if I understand correctly it was changed after this feedback from
me:
On Sun, 14 Dec 2025 at 14:41, Jelte Fennema-Nio <[email protected]> wrote:
> As mentioned upthread, I'm not sure BINARY makes sense. For any other
> options, the protocol docs should specify which ones are allowed and
> what their bits are. Looking at the DECLARE docs[2].
> 1. I think supporting ASENSITVE/INSENSITIVE/SENSITIVE bits is
> unnecessary, since postgres cursors are always INSENSITIVE.
> 2. For SCROLL vs NO SCROLL, it would be nice if we could get rid of
> the intermediate mode where if neither SCROLL or NO SCROLL is
> specified, it's still SCROLL sometimes. I'm not sure backwards
> compatibility would allow that, i.e. can you currently sometimes do a
> BACKWARD scan on a portal created with Bind. I guess we could make it
> so that if you specify the portal flags, then you have to be explicit
> abuot specifying SCROLL or NO SCROLL
> 3. All the flags with no SQL variant probably shouldn't be
> configurable through the protocol too (e.g. CURSOR_OPT_FAST_PLAN)
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Proposal to allow setting cursor options on Portals
2026-01-08 00:39 Re: Proposal to allow setting cursor options on Portals Jacob Champion <[email protected]>
2026-01-08 19:22 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
2026-01-09 18:20 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-14 19:24 ` Re: Proposal to allow setting cursor options on Portals Robert Haas <[email protected]>
2026-01-14 23:12 ` Re: Proposal to allow setting cursor options on Portals Jelte Fennema-Nio <[email protected]>
2026-01-14 23:30 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
2026-01-15 10:33 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-15 19:00 ` Re: Proposal to allow setting cursor options on Portals Robert Haas <[email protected]>
2026-01-15 19:11 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-15 21:06 ` Re: Proposal to allow setting cursor options on Portals Hannu Krosing <[email protected]>
2026-03-04 14:26 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-04 15:35 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-17 14:41 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-18 18:00 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-19 00:52 ` Re: Proposal to allow setting cursor options on Portals Sami Imseih <[email protected]>
2026-03-24 01:59 ` Re: Proposal to allow setting cursor options on Portals Sami Imseih <[email protected]>
2026-03-24 10:01 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
@ 2026-03-25 07:46 ` Jelte Fennema-Nio <[email protected]>
2026-03-25 14:34 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
1 sibling, 1 reply; 24+ messages in thread
From: Jelte Fennema-Nio @ 2026-03-25 07:46 UTC (permalink / raw)
To: Dave Cramer <[email protected]>; +Cc: Sami Imseih <[email protected]>; Hannu Krosing <[email protected]>; Robert Haas <[email protected]>; Tom Lane <[email protected]>; Jacob Champion <[email protected]>; PostgreSQL Hackers <[email protected]>; Heikki Linnakangas <[email protected]>
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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Proposal to allow setting cursor options on Portals
2026-01-08 00:39 Re: Proposal to allow setting cursor options on Portals Jacob Champion <[email protected]>
2026-01-08 19:22 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
2026-01-09 18:20 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-14 19:24 ` Re: Proposal to allow setting cursor options on Portals Robert Haas <[email protected]>
2026-01-14 23:12 ` Re: Proposal to allow setting cursor options on Portals Jelte Fennema-Nio <[email protected]>
2026-01-14 23:30 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
2026-01-15 10:33 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-15 19:00 ` Re: Proposal to allow setting cursor options on Portals Robert Haas <[email protected]>
2026-01-15 19:11 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-15 21:06 ` Re: Proposal to allow setting cursor options on Portals Hannu Krosing <[email protected]>
2026-03-04 14:26 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-04 15:35 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-17 14:41 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-18 18:00 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-19 00:52 ` Re: Proposal to allow setting cursor options on Portals Sami Imseih <[email protected]>
2026-03-24 01:59 ` Re: Proposal to allow setting cursor options on Portals Sami Imseih <[email protected]>
2026-03-24 10:01 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-25 07:46 ` Re: Proposal to allow setting cursor options on Portals Jelte Fennema-Nio <[email protected]>
@ 2026-03-25 14:34 ` Dave Cramer <[email protected]>
2026-04-06 09:37 ` Re: Proposal to allow setting cursor options on Portals Jelte Fennema-Nio <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Dave Cramer @ 2026-03-25 14:34 UTC (permalink / raw)
To: Jelte Fennema-Nio <[email protected]>; +Cc: Sami Imseih <[email protected]>; Hannu Krosing <[email protected]>; Robert Haas <[email protected]>; Tom Lane <[email protected]>; Jacob Champion <[email protected]>; PostgreSQL Hackers <[email protected]>; Heikki Linnakangas <[email protected]>
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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Proposal to allow setting cursor options on Portals
2026-01-08 00:39 Re: Proposal to allow setting cursor options on Portals Jacob Champion <[email protected]>
2026-01-08 19:22 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
2026-01-09 18:20 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-14 19:24 ` Re: Proposal to allow setting cursor options on Portals Robert Haas <[email protected]>
2026-01-14 23:12 ` Re: Proposal to allow setting cursor options on Portals Jelte Fennema-Nio <[email protected]>
2026-01-14 23:30 ` Re: Proposal to allow setting cursor options on Portals Tom Lane <[email protected]>
2026-01-15 10:33 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-15 19:00 ` Re: Proposal to allow setting cursor options on Portals Robert Haas <[email protected]>
2026-01-15 19:11 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-01-15 21:06 ` Re: Proposal to allow setting cursor options on Portals Hannu Krosing <[email protected]>
2026-03-04 14:26 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-04 15:35 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-17 14:41 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-18 18:00 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-19 00:52 ` Re: Proposal to allow setting cursor options on Portals Sami Imseih <[email protected]>
2026-03-24 01:59 ` Re: Proposal to allow setting cursor options on Portals Sami Imseih <[email protected]>
2026-03-24 10:01 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
2026-03-25 07:46 ` Re: Proposal to allow setting cursor options on Portals Jelte Fennema-Nio <[email protected]>
2026-03-25 14:34 ` Re: Proposal to allow setting cursor options on Portals Dave Cramer <[email protected]>
@ 2026-04-06 09:37 ` Jelte Fennema-Nio <[email protected]>
0 siblings, 0 replies; 24+ messages in thread
From: Jelte Fennema-Nio @ 2026-04-06 09:37 UTC (permalink / raw)
To: Dave Cramer <[email protected]>; +Cc: Sami Imseih <[email protected]>; Hannu Krosing <[email protected]>; Robert Haas <[email protected]>; Tom Lane <[email protected]>; Jacob Champion <[email protected]>; PostgreSQL Hackers <[email protected]>; Heikki Linnakangas <[email protected]>
On Wed, 25 Mar 2026 at 15:34, Dave Cramer <[email protected]> wrote:
> Attached is v4 of the patch
> Co-Authored by Sami Imseih
>
> Adds docs and test module
It's looking much more finished
> The portal can
> later be operated on with cursor commands such as FETCH, MOVE,
> and CLOSE.
This made me realize that, adding the Bind side of cursors is only
half of the equation. The "Execute" message should also gain new
behaviour to support all the same functionality as FETCH and MOVE. I
think we can do that fairly easily by adding similar flags to Execute.
I think we'd need three flags:
1. MOVE
2. BACKWARD
3. ABSOLUTE
I do realize the scope creep of this, but it feels that without
addressing Execute we have a half-finished feature. That could be
fine, but then I don't think we should call the option
_pq_.protocol_cursor. Because that sounds like it solves the whole
half-baked protocol-level cursor implemention that we currently have.
Maybe _pq_.cursor_bind instead.
I think the "protocol_" part in _pq_.protocol_cursor is duplicative.
The _pq_ part already indicates that it's a protocol option, so I'd
leave that out.
> <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>.
and
> <para>
> Bitmap set by protocol extensions.
> </para>
I think the Message Formats page should list the actual flag values
that are valid. The protocol docs should not require you to look at
the postgres source code.
/*
* 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.
*/
What is the difference between setting cursorOptions = 0 and the
CreatePortal defaults?
> {"protocol_cursor", NULL, "0", NULL,
> "Protocol-Cursor", "", 1,
> offsetof(struct pg_conn, protocol_cursor)},
In my GoAway patchset I linked enabling the protocol extension to the
user requesting protocol v3.2 (or higher).
> /* Reject any bits we don't recognize */
> if (bind_ext_flags & ~0x0007)
Let's use PQ_BIND_CURSOR_VALID_FLAGS here too instead of this magic number.
> src/test/modules/libpq_protocol_cursor/libpq_protocol_cursor.c
I think it'd be better to put these tests in the libpq_pipeline test
file. Then we can keep all the libpq tests together so they can share
the helper logic.
^ permalink raw reply [nested|flat] 24+ messages in thread
end of thread, other threads:[~2026-04-06 09:37 UTC | newest]
Thread overview: 24+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-01-08 00:39 Re: Proposal to allow setting cursor options on Portals Jacob Champion <[email protected]>
2026-01-08 02:51 ` Tom Lane <[email protected]>
2026-01-08 07:38 ` Jelte Fennema-Nio <[email protected]>
2026-01-08 19:22 ` Tom Lane <[email protected]>
2026-01-09 18:20 ` Dave Cramer <[email protected]>
2026-01-14 19:24 ` Robert Haas <[email protected]>
2026-01-14 23:12 ` Jelte Fennema-Nio <[email protected]>
2026-01-14 23:30 ` Tom Lane <[email protected]>
2026-01-15 10:33 ` Dave Cramer <[email protected]>
2026-01-15 19:00 ` Robert Haas <[email protected]>
2026-01-15 19:11 ` Dave Cramer <[email protected]>
2026-01-15 21:06 ` Hannu Krosing <[email protected]>
2026-03-04 14:26 ` Dave Cramer <[email protected]>
2026-03-04 15:35 ` Dave Cramer <[email protected]>
2026-03-17 14:41 ` Dave Cramer <[email protected]>
2026-03-18 18:00 ` Dave Cramer <[email protected]>
2026-03-19 00:52 ` Sami Imseih <[email protected]>
2026-03-24 01:59 ` Sami Imseih <[email protected]>
2026-03-24 10:01 ` Dave Cramer <[email protected]>
2026-03-24 20:54 ` Sami Imseih <[email protected]>
2026-03-25 07:27 ` Jelte Fennema-Nio <[email protected]>
2026-03-25 07:46 ` Jelte Fennema-Nio <[email protected]>
2026-03-25 14:34 ` Dave Cramer <[email protected]>
2026-04-06 09:37 ` Jelte Fennema-Nio <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox