Обсуждение: Adding an extra byte to ReadyForQuery (B) to indicate HOLD cursors

Hi PostgreSQL Hackers

I started to look around for a solution of current pgJDBC shortcoming
of having to emulate WITH HOLD cursors and found this discussion
(https://github.com/pgjdbc/pgjdbc/discussions/3323) mentioning
connection pooling.

I did not find anything in the wire protocol documentation about
indicating the presence of cursos held over transactions, but this
info seems essential to properly handling them in case of transaction
pooling.

Would it make sense to add one more byte ('H' ?) at the end of the
ReadyForQuery backend message to indicate that there are cursors that
live across transactions, so thet the connection pooler knows to not
give the database connections to other clients while this is happening
?

Or should we keep the current structure of just one byte and add
another value to indicate "idle but with a cursor on hold" as the
cursor only matters if outside a transaction.

So the full set of values for Current backend transaction status
indicator becomes:
 -  'I' if idle (not in a transaction block);
 -  'T' if in a transaction block;
 -  'E' if in a failed transaction block (queries will be rejected
until block is ended)
 + 'H' if idle but with a held cursor.

The second option, maybe together with a flag activating/disabling the
'H' option seems to be the least disruptive

Thoughts?

---
Hannu

On Fri, 28 Nov 2025 at 14:48, Hannu Krosing <hannuk@google.com> wrote:
>
> Would it make sense to add one more byte ('H' ?) at the end of the
> ReadyForQuery backend message to indicate that there are cursors that
> live across transactions, so thet the connection pooler knows to not
> give the database connections to other clients while this is happening
> ?

This part is actually interesting when speaking about connection
proxying (pooling, e.g. pgbouncer, odyssey, pgpool-II)
If we can inform the client that his postgresql backend has some
session-level objects (like prepared statement, temp table, advisory
lock or cursor), this will be very helpful.
On the pooler side, you can reuse or not reuse connections between
different clients based on this single byte value.



-- 
Best regards,
Kirill Reshke

> On 28 Nov 2025, at 18:28, Kirill Reshke <reshkekirill@gmail.com> wrote:
>
> If we can inform the client that his postgresql backend has some
> session-level objects (like prepared statement, temp table, advisory
> lock or cursor), this will be very helpful.

+1.

Roman was starting discussion with very similar topic [0]. Not only drivers need this, proxies too.


Best regards, Andrey Borodin.

[0] https://www.postgresql.org/message-id/82741750755647@mail.yandex-team.ru

There are some differences in what kind of session-level objects ar e there.

For example pg_bouncer now supports prepared statements as these are
essentially "read-only" can be easily re-created without disturbing
their existing copies.

Temp tables, hold cursors and advisory locks are supposed to be unique
and also tied to a specific client.

Most GUCs are also supposed to be re-creatable and could be handled
like prepared statements, but one can have GUCs with side effects
which may fall in either category.


Having these things to keep in mind, the proper way forward could be
having a flag that enables "Rich ReadyForQuery" message which is more
than 5 bytes and could be then even be defined as something where you
specify the info you want in the flag and are expected to be able to
ignore any other info you are not interested in.

So to recap the proposal for Rich ReadyForQuery

A GUC which has a list of optionswe want the database to keep track of
and signal us in the ReadyForQuery nmessage

So for example when we set

SET backend_track_session_objects=cursors,temptables;

We would add the optional presence of both in the message [*]

ReadyForQuery (B)
    Byte1('Z')
        Identifies the message type. ReadyForQuery is sent whenever
the backend is ready for a new query cycle.
    Int32(7)
        Length of message contents in bytes, including self.
    Byte1
        Current backend transaction status indicator. Possible values
are 'I' if idle (not in a transaction block); 'T' if in a transaction
block; or 'E' if in a failed transaction block (queries will be
rejected until block is ended).
    Byte2
        'C' if hold cursors are active
    Byte3
        'T' if temp tables are active

The values after byte 1 should be unique across the options and
present only when there are requested session objects.


To extend this further we might also add a hook which can add its own values.


Maybe we can even integrate COMMIT LSN tracking here @Andres Freund ,
@Heikki Linnakangas

The LSN could have a representation like L followed by 8 bytes of
<uint64> LSN value. We do not want to add serialization here.


[*] https://www.postgresql.org/docs/current/protocol-message-formats.html#PROTOCOL-MESSAGE-FORMATS-READYFORQUERY








On Fri, Nov 28, 2025 at 2:45 PM Andrey Borodin <x4mmm@yandex-team.ru> wrote:
>
>
>
> > On 28 Nov 2025, at 18:28, Kirill Reshke <reshkekirill@gmail.com> wrote:
> >
> > If we can inform the client that his postgresql backend has some
> > session-level objects (like prepared statement, temp table, advisory
> > lock or cursor), this will be very helpful.
>
> +1.
>
> Roman was starting discussion with very similar topic [0]. Not only drivers need this, proxies too.
>
>
> Best regards, Andrey Borodin.
>
> [0] https://www.postgresql.org/message-id/82741750755647@mail.yandex-team.ru