Thread: libpq enhancement for multi-process application

Hi all, and mainly postresql developpers,

I've been reading old posts about the libpq interface related to multi-process
application. The main problem being that after a fork, each process has a DB
connexion, actually the same. If one closes it, the other one remains in a
unknown or not stable state.

This is a real problem when writing a highly loaded daemon. Let's consider my
example : a main daemon is receiving network requests, and makes heavy use of
the DB. It thus have a permanent connexion to the DB. Sometimes, this main
daemon forks, just to serve a couple of request. This child process doesn't
need a permanent connexion. But closing it would destroy his parent's one.

There is actually one very easy, but awful, solution : closing the database
connexion before forking, and reopening when needed in each process. But
that's really awful, cause the main daemon will always close, fork, and then
just after reopen. What a waste !

A second solution would be a clone of the PQfinish function which does NOT
send the disconnexion sequence to the backend but just does everything else
(release memory, close the socket, and so on).

The big frustration being that this clone actually exists in the library, but
is a private function. It's named freePGconn, and is called from PQfinish
besides closePGconn (which sends the disconnexion sequence to the backend).

So I guess you've understood my request. Great folks from postresql, would it
be possible to kinda export a nice version of freePGconn ? It would really,
really, help people writing multi-process application without having to manage
a single connexion with shared memory and other tricks, as suggested a few
months ago.

In the meantime, I use the ugly solution : freeing the pointer returned by
PQconnectdb in the child process, knowing some memory hasn't been released.
Hopefully, these child processes don't last long, and the garbage collector is
working fine !


Comments / other solutions are welcome !

Regards,

-- 
Sébastien Bonnet bonseb@free.fr                                 http://bonseb.free.fr/

Sébastien Bonnet wrote:
> 
> Hi all, and mainly postresql developpers,
> 
> I've been reading old posts about the libpq interface related to multi-process
> application. The main problem being that after a fork, each process has a DB
> connexion, actually the same. If one closes it, the other one remains in a
> unknown or not stable state.

Uhm... I always thought that sharing the same socket between processes
is wrong.

My multi-process daemon works like apache with a pool of processes
everyone with its own connection to the DB. The connection is only
opened AFTER the fork and remains open as long as the process lives just
to avoid a new connection for each accept.

Bye!

> Uhm... I always thought that sharing the same socket between
> processes is wrong.

Well, I've never thought about it before this problem, but it definitely
appears to me like something not to do. Sharing remote object doesn't sound
right :-(

> My multi-process daemon works like apache with a pool of processes
> everyone with its own connection to the DB. The connection is only
> opened AFTER the fork and remains open as long as the process lives just
> to avoid a new connection for each accept.

When you can do it this way, that's nice'n'easy. In my case, I have to have a
connection before the fork, and keep it after in both parent and child,
eventhough it will be closed a few seconds later in the child. So, for now on,
the only-almost-clean-solution is to free the pgconn structure in the child
and reconnect when needed. This way, the parent process keeps its own
connexion. No other process is using it. Sounds safe, but kinda
"do-it-yourself" :-(

-- 
Sébastien Bonnet bonseb@free.fr                                 http://bonseb.free.fr/