BackendKeyData is mandatory?

Tatsuo Ishii <ishii@postgresql.org> writes:

In the Frontend/Backend protocol, it is explained that after
successful authentication following messages can be sent from backend
to frontend[1]:

BackendKeyData
ParameterStatus
ReadyForQuery
ErrorResponse
NoticeResponse

My question is, BackendKeyData is mandatory or not. Currently
Pgpool-II raises a fatal error if BackendKeyData is not sent before
ReadyForQuery arrives. This is because without the message, frontend
cannot send a CancelRequest message later on, as there's no secret
key.

As you say, without BackendKeyData it's impossible to send a query
cancel, so we expect the server will always send that.

I heard that some "PostgreSQL compatible" servers do not send
BackendKeyData message to frontend. I wonder if this is a protocol
violation.

I'd say so. Maybe whoever that is doesn't care to support query
cancel. They're within their rights to do that I guess, but
Pgpool-II does not have to support the case. (A less incompatible
way of not supporting query cancel is to send dummy BackendKeyData
values and then just ignore cancel requests. So I don't see that
you need to do anything towards this goal, if it is a goal and
not merely a broken implementation.)

regards, tom lane

On Monday, June 16, 2025, Tatsuo Ishii <ishii@postgresql.org> wrote:

My question is, BackendKeyData is mandatory or not. Currently
Pgpool-II raises a fatal error if BackendKeyData is not sent before
ReadyForQuery arrives. This is because without the message, frontend
cannot send a CancelRequest message later on, as there's no secret
key.

I wouldn’t expect a proxy to make a judgement here; but to simply forward
what does show up and otherwise stay silent. If there is proxy layer code
needed to deal with its absence ignoring the cancel attempt with a log
warning would be sufficient. Otherwise, the user has made their choices
and this is an optional feature in practice (though resorting to
pg_cancel_query make be required for truly hung processes).

David J.

My question is, BackendKeyData is mandatory or not. Currently
Pgpool-II raises a fatal error if BackendKeyData is not sent before
ReadyForQuery arrives. This is because without the message, frontend
cannot send a CancelRequest message later on, as there's no secret
key.

As you say, without BackendKeyData it's impossible to send a query
cancel, so we expect the server will always send that.

That's my understanding too.

I heard that some "PostgreSQL compatible" servers do not send
BackendKeyData message to frontend. I wonder if this is a protocol
violation.

I'd say so. Maybe whoever that is doesn't care to support query
cancel. They're within their rights to do that I guess, but
Pgpool-II does not have to support the case. (A less incompatible
way of not supporting query cancel is to send dummy BackendKeyData
values and then just ignore cancel requests. So I don't see that
you need to do anything towards this goal, if it is a goal and
not merely a broken implementation.)

Agreed.
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

On 17.06.25 03:10, Tatsuo Ishii wrote:

My question is, BackendKeyData is mandatory or not. Currently
Pgpool-II raises a fatal error if BackendKeyData is not sent before
ReadyForQuery arrives. This is because without the message, frontend
cannot send a CancelRequest message later on, as there's no secret
key.

I think that's fine, if the server does not want to support query
cancellation. The current protocol description certainly does not
support the idea that it is a hard error *not* to send BackendKeyData.

It's also worth thinking about the new protocol 3.2 longer key data. A
paranoid server might choose to send key data only if protocol >=3.2 is
chosen and not if a lower, notionally less secure version is chosen.

I think that's fine, if the server does not want to support query
cancellation. The current protocol description certainly does not
support the idea that it is a hard error *not* to send BackendKeyData.

Isn't it scary if the server does not allow a query cancel? For
example, if the server charge you per query duration and if you
accidentally send a long running query, the only escape exit is the
query cancellation.

It's also worth thinking about the new protocol 3.2 longer key data.
A paranoid server might choose to send key data only if protocol >=3.2
is chosen and not if a lower, notionally less secure version is
chosen.

I would say the server does wrong a decision. I think even if the key
is not long, it's still useful than nothing.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

On 19/06/2025 13:03, Tatsuo Ishii wrote:

I think that's fine, if the server does not want to support query
cancellation. The current protocol description certainly does not
support the idea that it is a hard error *not* to send BackendKeyData.

Isn't it scary if the server does not allow a query cancel? For
example, if the server charge you per query duration and if you
accidentally send a long running query, the only escape exit is the
query cancellation.

Or disconnect. Or pg_cancel_backend().

You can also easily have a stray psql session where the user has gone
out for lunch. Or an application that doesn't have a timeout. Many other
scenarios like that.

It's also worth thinking about the new protocol 3.2 longer key data.
A paranoid server might choose to send key data only if protocol >=3.2
is chosen and not if a lower, notionally less secure version is
chosen.

I would say the server does wrong a decision. I think even if the key
is not long, it's still useful than nothing.

I tend to agree, but people have different priorities. It's also
reasonable that you'd want to only support long cancellation keys. Or
maybe you have a proxy that doesn't implement query cancellation, or
only supports it with long keys because it embeds routing information in
the key, or something like that.

FWIW my reading of the protocol docs is that BackendKeyData is optional.
If I was writing a client today, I would accept it missing. But of
course all PostgreSQL versions today do send it, and I wouldn't be
surprised if there are clients out there that get confused if they don't
see it.

- Heikki

Or disconnect.

You cannot disconnect without canceling the query at least using psql.
You can kill psql to disconnect but it's possible that the backend
keeps on running the query.

Or pg_cancel_backend().

In order to issue pg_cancel_backend() the user needs to know the
backend pid which was supposed to be provided by a BackendKeyData
message. Of course you could search and find the backend pid from
other source, but I think it's less user friendly.

I would say the server does wrong a decision. I think even if the key
is not long, it's still useful than nothing.

I tend to agree, but people have different priorities. It's also
reasonable that you'd want to only support long cancellation keys. Or
maybe you have a proxy that doesn't implement query cancellation, or
only supports it with long keys because it embeds routing information
in the key, or something like that.

Agreed. All PostgreSQL "compatible" servers have their own
priority. In Pgpool-II case, the priority is compatibility with
PostgreSQL (I am not saying Pgpool-II is 100% compatible with
PostgreSQL as of today, but it's a implementation limitation which I
want to eliminate someday).

FWIW my reading of the protocol docs is that BackendKeyData is
optional.

If majority of developers think so, do we want to update the protocol
docs? For me the docs is not clear enough.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

On Thu, 19 Jun 2025 at 13:51, Tatsuo Ishii <ishii@postgresql.org> wrote:

FWIW my reading of the protocol docs is that BackendKeyData is
optional.

If majority of developers think so, do we want to update the protocol
docs? For me the docs is not clear enough.

I think the docs currently definitely suggests that BackendKeyData
will always be sent (emphasis mine):

After having received AuthenticationOk, the frontend must wait for further messages from the server. In this phase a backend process is being started, and the frontend is just an interested bystander. It is still possible for the startup attempt to fail (ErrorResponse) or the server to decline support for the requested minor protocol version (NegotiateProtocolVersion), *but in the normal case the backend will send some ParameterStatus messages, BackendKeyData, and finally ReadyForQuery.*

I'd be surprised if many clients handle it correctly if it is not
sent. Looking quickly at the code for pgbouncer and libpq for PG17
(and lower) they definitely don't. They won't throw an error, but
instead of doing nothing when the user tries to cancel a query they
will instead send a cancel message with all zeros to the server. Since
PG18 libpq handles a cancel call nicely as a no-op, when no cancel key
was received.

I agree that it would be good to explicitly call out that the server
not sending BackendKeyData is an option if we don't want the server to
require sending this message.

On Thursday, June 19, 2025, Tatsuo Ishii <ishii@postgresql.org> wrote:

FWIW my reading of the protocol docs is that BackendKeyData is
optional.

If majority of developers think so, do we want to update the protocol
docs? For me the docs is not clear enough.

At this point why does it matter what the docs says? You know what exists
in reality and you can either change or not.

That said, the documentation makes it clear that absent an error the server
shall send:

2 or more (some) ParameterStatus messages
Followed by
1 BackendKeyData message
Followed by
1 ReadyForQuery message

So the protocol is violated if the BackendKeyData is absent. I don’t see
why we should change that since we adhere to it and it’s our protocol; but
also its absence is non-fatal to the typical operation of the server…

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

At this point why does it matter what the docs says?

Yeah, I cannot get excited about changing this. The current protocol
spec accurately documents what we do. It is not incumbent on us to
document what non-Postgres implementations of this protocol could
hypothetically do --- especially when we're just guessing in a vacuum
as to whether this second-hand-reported behavior is intentional or
a bug.

There's an argument based on the ancient principle of "be conservative
in what you send and liberal in what you accept" that Pgpool ought
to survive not getting BackendKeyData, or at least not complain until
such time as it's asked to send a query cancel. But that principle
is not about what it says in the protocol spec.

regards, tom lane

On Thu, Jun 19, 2025 at 5:12 AM Jelte Fennema-Nio <postgres@jeltef.nl> wrote:

I'd be surprised if many clients handle it correctly if it is not
sent. Looking quickly at the code for pgbouncer and libpq for PG17
(and lower) they definitely don't. They won't throw an error, but
instead of doing nothing when the user tries to cancel a query they
will instead send a cancel message with all zeros to the server. Since
PG18 libpq handles a cancel call nicely as a no-op, when no cancel key
was received.

If anyone today is relying on "backend-key-less" connection, this is
potentially a breaking change. For example, psycopg2 now complains:

psycopg2.OperationalError: can't get cancellation key

whereas before, it would be able to connect, and sending a cancel
would do something. (Whether or not that "something" was useful was up
to the server implementation, I guess.)

--Jacob

On Mon, 23 Jun 2025 at 18:02, Jacob Champion
<jacob.champion@enterprisedb.com> wrote:

If anyone today is relying on "backend-key-less" connection, this is
potentially a breaking change. For example, psycopg2 now complains:

psycopg2.OperationalError: can't get cancellation key

It's not super clear what you're referring to with "this" in your
first sentence.

To be clear, I'm not saying we should start throwing errors for things
in libpq that weren't errors before. But more as: I don't expect many
client authors to have considered the scenario of no BackendKeyData.
Because either an author believes the BackendKeyData is optional, in
which case they shouldn't send a CancelRequest for those connections.
Or they think it is required, in which case throwing an error seems
like the sensible thing to do. And the implementations in PgBouncer
and PG17 is neither, i.e. it won't throw an error, but instead of
doing nothing it will do something clearly useless.

On Mon, Jun 23, 2025 at 9:24 AM Jelte Fennema-Nio <postgres@jeltef.nl> wrote:

On Mon, 23 Jun 2025 at 18:02, Jacob Champion
<jacob.champion@enterprisedb.com> wrote:

If anyone today is relying on "backend-key-less" connection, this is
potentially a breaking change. For example, psycopg2 now complains:

psycopg2.OperationalError: can't get cancellation key

It's not super clear what you're referring to with "this" in your
first sentence.

The change in behavior in 18, if the server doesn't send a cancellation key.

To be clear, I'm not saying we should start throwing errors for things
in libpq that weren't errors before.

But that is _exactly_ what we've started doing now, in 18. We return
NULL instead of an "empty" cancellation object, and at least one
existing client fails because of it. Whether that's a problem in
practice is, I guess, part of the open question of this thread.

But more as: I don't expect many
client authors to have considered the scenario of no BackendKeyData.

I agree.

Because either an author believes the BackendKeyData is optional, in
which case they shouldn't send a CancelRequest for those connections.
Or they think it is required, in which case throwing an error seems
like the sensible thing to do. And the implementations in PgBouncer
and PG17 is neither, i.e. it won't throw an error, but instead of
doing nothing it will do something clearly useless.

From reading this thread, I'm not convinced that's "clear". I wouldn't
have chosen the existing behavior, for sure, but any existing servers
that don't send a key must be doing _something_ with that cancel
request, right? Even if it's just ignored?

Do we know which implementations aren't sending keys?

--Jacob

On Mon, 23 Jun 2025 at 18:42, Jacob Champion
<jacob.champion@enterprisedb.com> wrote:

To be clear, I'm not saying we should start throwing errors for things
in libpq that weren't errors before.

But that is _exactly_ what we've started doing now, in 18. We return
NULL instead of an "empty" cancellation object, and at least one
existing client fails because of it.

Ugh, I seemed to have totally misread that code when I wrote my
initial note about the PG18 behaviour. Indeed PQgetCancel returning
NULL obviously means that an error occurred. Also in the
PQcancelCreate case, we're even setting the error message ourselves.
(I did search for the error message you mentioned, but did so in
psycopg (aka psycopg3), not psycopg2)

Whether that's a problem in
practice is, I guess, part of the open question of this thread.

I'd love to hear what database actually has this problem. Without a
specific (and non-experimental) database being impacted by this, I'm
not entirely convinced that we need to do anything.

If we do think it's worth it for libpq to continue to handle such
systems, then I'd say we change the behaviour to only throw an error
at a later point: When actually initiating the cancellation. Because
it seems pretty useful to know that their attempt to cancel a query
could not be serviced (just like in case of network issues). However,
throwing an error in PQgetCancel or PQcancelCreate seems premature,
because some clients (e.g. psycopg2) call PQgetCancel right after
initializing the Postgres connection, and will thus fail the
connection attempt. Effectively meaning that that client with libpq
PG18 won't be able to connect to the type of server in question.

From reading this thread, I'm not convinced that's "clear". I wouldn't
have chosen the existing behavior, for sure, but any existing servers
that don't send a key must be doing _something_ with that cancel
request, right? Even if it's just ignored?

I mean if the only thing a server can do is ignore it, ISTM that it's
clearly useless to send it anyway. Sending nothing seems a much better
choice in that case.

Do we know which implementations aren't sending keys?

Nope, that's totally unclear. It would be very nice knowing which
database this is, and if it's at all a production system.

Jelte Fennema-Nio <postgres@jeltef.nl> writes:

On Mon, 23 Jun 2025 at 18:42, Jacob Champion
<jacob.champion@enterprisedb.com> wrote:

From reading this thread, I'm not convinced that's "clear". I wouldn't
have chosen the existing behavior, for sure, but any existing servers
that don't send a key must be doing _something_ with that cancel
request, right? Even if it's just ignored?

I mean if the only thing a server can do is ignore it, ISTM that it's
clearly useless to send it anyway. Sending nothing seems a much better
choice in that case.

It could be that the server has some independent way of knowing which
session to cancel. (As a reductio-ad-absurdum case, maybe it only
supports one session.)

Do we know which implementations aren't sending keys?

Nope, that's totally unclear. It would be very nice knowing which
database this is, and if it's at all a production system.

Yeah, I'm very hesitant to spend any effort here without having
a more concrete use-case.

regards, tom lane

Do we know which implementations aren't sending keys?

Nope, that's totally unclear. It would be very nice knowing which
database this is, and if it's at all a production system.

Yeah, I'm very hesitant to spend any effort here without having
a more concrete use-case.

One example is Amazon RDS Proxy.
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-proxy.html

See the section:
"Additional limitations for RDS for PostgreSQL"

I haven't tried Amazon RDS Proxy myself but I heard about it in a
conversation with a Pgpool-II user. He tried to use Pgpool-II with
Amazon RDS Proxy and failed.

https://github.com/pgpool/pgpool2/issues/111

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

On Tue, 24 Jun 2025 at 01:44, Tatsuo Ishii <ishii@postgresql.org> wrote:

One example is Amazon RDS Proxy.

Okay, that sounds widely used enough to continue that we should
probably change the new PG18 behaviour of PQgetCancel and
PQcancelCreate like I suggested. Failing all psycopg2 connection
attempts against AWS its proxy service doesn't seem like something
we'd want to do.

On Tue, Jun 24, 2025 at 1:36 AM Jelte Fennema-Nio <postgres@jeltef.nl> wrote:

Okay, that sounds widely used enough to continue that we should
probably change the new PG18 behaviour of PQgetCancel and
PQcancelCreate like I suggested. Failing all psycopg2 connection
attempts against AWS its proxy service doesn't seem like something
we'd want to do.

So that's
1) return an (empty) cancellation object even if the server has not
sent a key, and
2) error out when trying to cancel with an empty object?

That sounds reasonable to me.

Are there any reading along who want us to continue sending an
all-zeroes CancelRequest if the server has not sent a key? Personally,
I don't feel a need to push for that without evidence that it's
actually used, and both RDS Proxy and Cockroach [1]https://github.com/cockroachdb/cockroach/issues/32973 seem to fall in
the "don't support cancellation at all" bucket.

Thanks!
--Jacob

[1]: https://github.com/cockroachdb/cockroach/issues/32973

On Tue, 24 Jun 2025 at 17:07, Jacob Champion
<jacob.champion@enterprisedb.com> wrote:

So that's
1) return an (empty) cancellation object even if the server has not
sent a key, and
2) error out when trying to cancel with an empty object?

Yes (and empty being non-NULL obviously)

That sounds reasonable to me.

Alright, let's do that then. I could probably write a patch for that tomorrow.