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

[Tatsuo Ishii]

> In the docs for the connection_cache parameter, it states that:
> "connections to template0, template1, postgres and regression databases are
> not cached even if connection_cache is on."
> 
> This behavior appears to date from commit 32a44be0e8 in 2015, which fixed a
> client connection leak (
> https://www.pgpool.net/pipermail/pgpool-general/2015-January/003438.html),
> but looking at the mailing list archives there was not really any
> discussion of whether it was appropriate to exempt the "postgres" database
> from connection caching, and this behavior came as a bit of an unpleasant
> surprise when I ran across it.

Actually connection to postgres database has not been cached since
2008 (commit c78517c2). The code was accidentally removed in commit
0c955633 and restored in commit 32a44be0e8.

> The Postgresql docs at
> https://www.postgresql.org/docs/current/app-initdb.html state:
> 
> "The postgres database is a default database meant for use by users,
> utilities and third party applications. template1 and template0 are meant
> as source databases to be copied by later CREATE DATABASE commands."
> 
> It makes sense that the template databases would be treated specially here,
> but I think a hardcoded exemption for the default user database is
> non-intuitive and I'm curious to know what the reasoning was behind this
> decision?

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?

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