Thread: libpq in a multithreaded environment

libpq in a multithreaded environment

From
Eugene Bregman
Date:
Hi,
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.

Thank you in advance for your advice,
Eugene


__________________________________________________
Do You Yahoo!?
Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month.
http://geocities.yahoo.com/ps/info1

Re: libpq in a multithreaded environment

From
Tom Lane
Date:
Eugene Bregman <eugenebregman@yahoo.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