[Pgpool-general] Issue with connection queuing

[Tatsuo Ishii]

> > > If a client which connects directly to postgresql wants to 
> > establish more
> > > connections than the maximum configured in postgresql, an 
> > error is generated
> > > and the connection attempt is aborted. On the other hand, 
> > if a client which
> > > connects to pgpool tries to make more connections than pgpool childs
> > > available, it blocks until a connection is freed. No error 
> > is generated. In
> > > some cases, this may be an issue (e.g: when using checkers 
> > in order to know
> > > if the database is up or down).
> > > My question is: Can I tell pgpool not to queue incoming 
> > connections if there
> > > is no free pgpool childs available?.
> > 
> > The queue length is limited by OS. So you will get errors if there are
> > too many incoming connection requests.
> 
> That is the problem. If all the pgpool childs are busy, instead of getting
> an error message, the application blocks until a connection is freed.
> 
> > 
> > > some cases, this may be an issue (e.g: when using checkers 
> > in order to know
> > > if the database is up or down).
> > 
> > superuser_reserved_connections will help you. This is default to 2, so
> > that should not be an issue if direct connecting clients do not use
> > PostgreSQL super user account AND pgpool's checker uses super user
> > account.
> 
> Yes, you are right. This would help in order to know whether the database is
> up or down, but there are 2 problems about using a superuser account in the
> checker: 
> 	a) Is insecure (the checker must have super user password)

AFAIK the checker does not require password.

> 	b) the checker will detect the database is up, but the application
> may not work because of the blocking. This would not happen if connecting
> directly to postgresql.
> 
> A workaround is to configure more pgpool childs than the maximum application
> (jboss) pool size. This will work unless database (via pgpool) were accessed
> from other sources.
--
Tatsuo Ishii
SRA OSS, Inc. Japan