Horton

Thread

  1. request for database identifier in the startup packet

    Dave Cramer <davecramer@gmail.com> — 2024-05-09T12:06:11Z

    Greetings,

The JDBC driver is currently keeping a per connection cache of types in the
driver. We are seeing cases where the number of columns is quite high. In
one case Prevent fetchFieldMetaData() from being run when unnecessary. ·
Issue #3241 · pgjdbc/pgjdbc (github.com)
<https://github.com/pgjdbc/pgjdbc/issues/3241> 2.6 Million columns.

If we knew that we were connecting to the same database we could use a
single cache across connections.

I think we would require a server/database identifier in the startup
message.

Dave Cramer

  2. Re: request for database identifier in the startup packet

    David G. Johnston <david.g.johnston@gmail.com> — 2024-05-09T13:55:27Z

    On Thursday, May 9, 2024, Dave Cramer <davecramer@gmail.com> wrote:

> Greetings,
>
> The JDBC driver is currently keeping a per connection cache of types in
> the driver. We are seeing cases where the number of columns is quite high.
> In one case Prevent fetchFieldMetaData() from being run when unnecessary.
> · Issue #3241 · pgjdbc/pgjdbc (github.com)
> <https://github.com/pgjdbc/pgjdbc/issues/3241> 2.6 Million columns.
>
> If we knew that we were connecting to the same database we could use a
> single cache across connections.
>
> I think we would require a server/database identifier in the startup
> message.
>

I feel like pgbouncer ruins this plan.

But maybe you can construct a lookup key from some combination of data
provided by these functions:
https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-SESSION

David J.

  3. Re: request for database identifier in the startup packet

    Robert Haas <robertmhaas@gmail.com> — 2024-05-09T16:22:37Z

    On Thu, May 9, 2024 at 8:06 AM Dave Cramer <davecramer@gmail.com> wrote:
> The JDBC driver is currently keeping a per connection cache of types in the driver. We are seeing cases where the number of columns is quite high. In one case Prevent fetchFieldMetaData() from being run when unnecessary. · Issue #3241 · pgjdbc/pgjdbc (github.com) 2.6 Million columns.
>
> If we knew that we were connecting to the same database we could use a single cache across connections.
>
> I think we would require a server/database identifier in the startup message.

I understand the desire to share the cache, but not why that would
require any kind of change to the wire protocol.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

  4. Re: request for database identifier in the startup packet

    Dave Cramer <davecramer@gmail.com> — 2024-05-09T18:20:49Z

    Dave Cramer


On Thu, 9 May 2024 at 12:22, Robert Haas <robertmhaas@gmail.com> wrote:

> On Thu, May 9, 2024 at 8:06 AM Dave Cramer <davecramer@gmail.com> wrote:
> > The JDBC driver is currently keeping a per connection cache of types in
> the driver. We are seeing cases where the number of columns is quite high.
> In one case Prevent fetchFieldMetaData() from being run when unnecessary. ·
> Issue #3241 · pgjdbc/pgjdbc (github.com) 2.6 Million columns.
> >
> > If we knew that we were connecting to the same database we could use a
> single cache across connections.
> >
> > I think we would require a server/database identifier in the startup
> message.
>
> I understand the desire to share the cache, but not why that would
> require any kind of change to the wire protocol.
>
> The server identity is actually useful for many things such as knowing
which instance of a cluster you are connected to.
For the cache however we can't use the IP address to determine which server
we are connected to as we could be connected to a pooler.
Knowing exactly which server/database makes it relatively easy to have a
common cache across connections. Getting that in the startup message seems
like a good place

Dave

  5. Re: request for database identifier in the startup packet

    Andres Freund <andres@anarazel.de> — 2024-05-09T19:14:55Z

    Hi,

On 2024-05-09 14:20:49 -0400, Dave Cramer wrote:
> On Thu, 9 May 2024 at 12:22, Robert Haas <robertmhaas@gmail.com> wrote:
> > On Thu, May 9, 2024 at 8:06 AM Dave Cramer <davecramer@gmail.com> wrote:
> > > The JDBC driver is currently keeping a per connection cache of types in
> > the driver. We are seeing cases where the number of columns is quite high.
> > In one case Prevent fetchFieldMetaData() from being run when unnecessary. ·
> > Issue #3241 · pgjdbc/pgjdbc (github.com) 2.6 Million columns.
> > >
> > > If we knew that we were connecting to the same database we could use a
> > single cache across connections.
> > >
> > > I think we would require a server/database identifier in the startup
> > message.
> >
> > I understand the desire to share the cache, but not why that would
> > require any kind of change to the wire protocol.
> >
> > The server identity is actually useful for many things such as knowing
> which instance of a cluster you are connected to.
> For the cache however we can't use the IP address to determine which server
> we are connected to as we could be connected to a pooler.
> Knowing exactly which server/database makes it relatively easy to have a
> common cache across connections. Getting that in the startup message seems
> like a good place

ISTM that you could just as well query the information you'd like after
connecting. And that's going to be a lot more flexible than having to have
precisely the right information in the startup message, and most clients not
needing it.

Greetings,

Andres Freund

  6. Re: request for database identifier in the startup packet

    Robert Haas <robertmhaas@gmail.com> — 2024-05-09T19:19:00Z

    On Thu, May 9, 2024 at 3:14 PM Andres Freund <andres@anarazel.de> wrote:
> ISTM that you could just as well query the information you'd like after
> connecting. And that's going to be a lot more flexible than having to have
> precisely the right information in the startup message, and most clients not
> needing it.

I agree with this.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

  7. Re: request for database identifier in the startup packet

    Dave Cramer <davecramer@gmail.com> — 2024-05-09T19:33:40Z

    On Thu, 9 May 2024 at 15:19, Robert Haas <robertmhaas@gmail.com> wrote:

> On Thu, May 9, 2024 at 3:14 PM Andres Freund <andres@anarazel.de> wrote:
> > ISTM that you could just as well query the information you'd like after
> > connecting. And that's going to be a lot more flexible than having to
> have
> > precisely the right information in the startup message, and most clients
> not
> > needing it.
>
> I agree with this.
>
> Well other than the extra round trip.

Thanks,
Dave

  8. Re: request for database identifier in the startup packet

    Robert Haas <robertmhaas@gmail.com> — 2024-05-09T19:39:20Z

    On Thu, May 9, 2024 at 3:33 PM Dave Cramer <davecramer@gmail.com> wrote:
> On Thu, 9 May 2024 at 15:19, Robert Haas <robertmhaas@gmail.com> wrote:
>> On Thu, May 9, 2024 at 3:14 PM Andres Freund <andres@anarazel.de> wrote:
>> > ISTM that you could just as well query the information you'd like after
>> > connecting. And that's going to be a lot more flexible than having to have
>> > precisely the right information in the startup message, and most clients not
>> > needing it.
>>
>> I agree with this.
>>
> Well other than the extra round trip.

I mean, sure, but we can't avoid that for everyone for everything.
There might be some way of doing something like this with, for
example, the infrastructure that was proposed to dynamically add stuff
to the list of PGC_REPORT GUCs, if the values you need are GUCs
already, or were made so. But I think it's just not workable to
unconditionally add a bunch of things to the startup packet. It'll
just grow and grow.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

  9. Re: request for database identifier in the startup packet

    Dave Cramer <davecramer@gmail.com> — 2024-05-09T19:51:57Z

    On Thu, 9 May 2024 at 15:39, Robert Haas <robertmhaas@gmail.com> wrote:

> On Thu, May 9, 2024 at 3:33 PM Dave Cramer <davecramer@gmail.com> wrote:
> > On Thu, 9 May 2024 at 15:19, Robert Haas <robertmhaas@gmail.com> wrote:
> >> On Thu, May 9, 2024 at 3:14 PM Andres Freund <andres@anarazel.de>
> wrote:
> >> > ISTM that you could just as well query the information you'd like
> after
> >> > connecting. And that's going to be a lot more flexible than having to
> have
> >> > precisely the right information in the startup message, and most
> clients not
> >> > needing it.
> >>
> >> I agree with this.
> >>
> > Well other than the extra round trip.
>
> I mean, sure, but we can't avoid that for everyone for everything.
> There might be some way of doing something like this with, for
> example, the infrastructure that was proposed to dynamically add stuff
> to the list of PGC_REPORT GUCs, if the values you need are GUCs
> already, or were made so. But I think it's just not workable to
> unconditionally add a bunch of things to the startup packet. It'll
> just grow and grow.
>

I don't think this is unconditional. These are real world situations where
having this information is useful.
That said, adding them everytime I ask for them would end up growing
uncontrollably. This seems like a decent discussion to have with others.

Dave