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
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
