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.

It works fine so long as synchronization is guaranteed. We use CORBA
(omniORB) which presents problems in the face of a traditional
factory pattern where each client gets a server-side, multi-threaded
object. For example, we want the following characteristics:

1) Single app server process - muli-threaded access
2) Single-threaded access to each session object
3) Garbage collection of server threads
4) Garbage collection of server objects
5) Single transactions across multiple RPCs

#1 comes automatically with CORBA unless you use the
SINGLE_THREAD_POLICY for the POA in which the object is created.

#2 does not come automatically with CORBA unless you use the
SINGLE_THREAD_POLICY and create each session object in its own POA,
which is frowned upon. Therefore, all exposed API calls must be
wrapped in code which synchronizes on a thread-lock attribute of the
session object.

#3 happens automatically, depending upon ORB configuration. This
means that you cannot simply use TLS for access to the libpq
database handle.

#4 requires manual coding.

#5 requires some voodoo as well, since a multi-threaded client could
interleave synchronized RPC calls that screw up another thread's
transaction.

Of course, if you're just talking about a traditional multi-threaded
application (non-CORBA), then storing the libpq handle in TLS would
seem like the best solution to me, so long as thread lifetime =
database session lifetime.

The other annoyance is that the ability to debug multi-threaded
applications has progressed slowly over the years:

a. older versions of g++ had problems throwing exceptions in
multi-threaded applications

b. older gdb versions couldn't debug multi-threaded applications

c. kernels couldn't dump the core image of multi-threaded applications

d. later kernels dumped the core image of the thread which caused
the violation, not all threads

Depending upon compiler and kernel, all of the above may have been
addressed, either partially or completely. I know newer linux
kernels can dump the image of each thread when the process dies, but
as of RedHat 8, d. was the behavior.

HTH,

Mike Mascari

> Many TIA!
> Mark