[Pgpool-general] Unable to accept new connection after terminating pgpool backend process

[Tatsuo Ishii]

> Hi,
> 
> I have a question regarding terminating pgpool backend processes. We are
> running one Postgres 8.4.4 node (with 2 databases on it) and using pgpool-II
> 2.3.3 (max_pool = 2) as a connection pool.
> 
>>From this e-mail thread (
> http://www.mail-archive.com/pgpool-general@pgfoundry.org/msg01941.html), I
> understand that when a backend process is killed (kill the Postgres process
> manually, or using pg_terminate_backend), pgpool gets the same error code as
> database down, so it initiates the failover process. However, in our
> environment, we do not have a failover node. Database is unlikely to go
> offline, and it is more likely that we need to manually kill a long running
> query.
> 
> What we like to have is the ability to:
> - Treat the error code got back from pg_terminate_backend (or the kill
> command) as a regular disconnect

It isn't possible without changing PostgreSQL itself.

> - Safely disconnect this particular pgpool child from the killed backend
> process. If this child has more than one connections, only close the one
> connects to the killed process. The other connection is not being affected.
> - Other pgpool child processes are not being affected.

I think it would be possible to add new PCP command which kill
particular pgpool child to and restart new one. Actually same thing
occurs when a pgpool child accidentaly killed by segfault or something
bad. Normal termination such as sigterm does not count on this, since
it's a part of shutdown process of pgpool and thus no new child is
started.

One downside with this is the existing connection pool to backend is
discarded. However trying to keep the connection pool is not worth the
trouble IMO.

> 1) Is there a patch that we can apply to achieve this?
> 2) Is there a plan to introduce this as a configuration option in future
> releases? I saw in the 3.0.0 release note: "- If there's only one DB node
> and it triggers failover, pgpool-II will automatically connects if the DB
> node coming up(Tatsuo)". Is this the same as I described? I tested 3.0.1,
> but still seeing the same problem.
> 
> Btw, when I tested 3.0.1, I applied this md5 authentication patch:
> http://abdulyadi.wordpress.com/2010/09/25/pgpool-ii-3-0-bug-fixes/
> 
> Thanks.
> -Arthur Chang