Re: libpq in a multithreaded environment

Eugene Bregman <eugenebregman(at)yahoo(dot)com> writes:
> We try to use libpq in a multi-threaded environment.
> Connection pool creates a group of connections to the
> database and serves PGconn to threads as they ask. One
> thread get a connection from the pool, takes a
> transaction, then releases connection back to the
> pool. Another thread will pick up the same connection
> from the pool and take another transaction. Our code
> warrants that on same connection, all transactions are
> executed sequentially by different threads.
> Is such design save enough? I made some archive search
> and could not find any discouraging information.

Should work fine, as long as you're careful that no thread releases
a connection with an open transaction.

It might be worth the cycles to have the controlling code send an
explicit ROLLBACK down each connection each time a connection is
returned to the pool; then there's no chance of cross-thread
transactional problems.

Another thing you'd want to mandate is that threads not issue SET
commands that would change the state of the connection. But none
of these are really libpq problems.

regards, tom lane