[pgpool-general: 9019] Re: exemption of "postgres" default database from connection caching

[Nathan Mehl]

On Mon, Feb 12, 2024 at 5:39 AM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:

>
> If my memory is correct, the reasoning behind this is that postgres
> database is used by some critical tools including createdb, vacuumdb
> (and perhaps pgAdmin) by default as a connecting database (not a
> template database). In theory, pgpool resets connection property using
> DISCARD ALL and caching the connection to postgres database should be
> safe. BUT:
>
> 1) These tools may not assume that the connection to database is
>    reused.
>
> 2) Pgpool admin could change configuration to not use DISCARD ALL in
>    reset_query_list. In this case, those tools may or may not be
>    confused because previous connection property still remains.
>
> 3) Some tools may use custom options. The custom option cannot be
>    removed by DISCARD ALL. In my understanding, it remains until the
>    session ends. This may or may not cause trouble in cached
>    connections. Probably this is not a problem for ordinary user
>    databases (I never heard complains in the wild). But I am sure
>    about postgres database.
>
> By these reasons I hesitate to make postgres database to be cached.
> Maybe we could add new option to allow postgres database to be cached
> in the future?
>

This makes sense, it's just very much not our use case -- we primarily use
postgres inside a cloud provider (Google CloudSQL) and manage table
creation with migration tools executing sql directly.

Ideally, the list of databases to disable connection caching for would be
configurable in the same way that the reset query list is, e.g.:

    backend_disable_connection_caching_databases0 =
'postgres;template0;template1;regression'

-n
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20240212/908a1b01/attachment.htm>