Re: any experience with multithreaded pg apps?

Mark Harrison wrote:
> I'm looking for feedback from anybody who has used pg in a
> multi-threaded program, particularly one in which several
> threads each open a database connection.
>
> It's documented to work in that scenario, but I'm interested
> in anybody who can share some real-world with that.

I've done some real-world work like this.

The application is a SOAP server written in C using the gsoap library to
query a PostgreSQL database.  The nature of the server is that individual
queries are very fast (generally less than .1 sec) and the possibility of
multiple queries in rapid succession is likely.  The possibility of multiple
simultaneious queries also exists, but is unlikely.

I built the app thusly:
The bugger fires up and opens a connection to Postgres, and a listening
socket.  A thread mutex is used to get exclusive access to the Postgres
database connection.  When a connection arrives, an attempt is made to
lock the mutex, if it succeeds, a thread is fired off to handle that
connection and the loop returns to wait for additional connections.  When
the handling of the connection is complete, the mutex is released and the
thread ends.
If a new connection arrives and the mutex is already held by a previous
thread, a new connection is established and a flag is set to alert the
thread that it should close the connection to Postgres apon completion.
When the thread completes, it closes the connection instead of releasing
the mutex.

The results meet pretty much what we wanted:
1) Processing of a single connection is _very_ fast, since the connection
    To Postgres is already established, and the query and processing of the
    results occur very quickly.
2) The daemon _can_ handle multiple simultaneous connections.  Performance
    degrades a bit (in my tests, it's about half as fast when it has to
    establish a connection prior to the query).  Since the nature of the
    application lends itself more to rapid sequential queries from a single
    host than to multiple simultaneous queries, this is acceptable for us.

We've considered moving this to a pre-threaded model, but so far there hasn't
been a need, and there is other work to do :)  I do have another SOAP app that
does pre-forking and pre-establishes database connections, but that's not
threaded so it doesn't really apply to your question.

Reliability and performance have been good in this application.  Haven't had a
lick of trouble with Postgres or the client libraries.  We developed this
initially on 7.3, and it now runs on 7.4.

Hope this helps.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com