Обсуждение: Threads in PSQL

Dear postgreSQL experts,
could you explain how should I run correctly multiple threads that
manipulate data from the same database?
In my program, I have main thread that SELECT  large object's id and
starts a thread that should export this lo to some file. Each thread
creates it's own PGconn connection, and starts lo_export(conn, loid,
filename) procedure. The problem is: after starting 2-3 threads SELECT
query of the main program  fails because "another command is already in
progress". Since main program does SELECT queries only, then it means
that this query conflicts with a command issued by one of the threads.
Again, I use individual PGconn in each thread, but the line for
PQconnectStart(const char* line) is the same for all threads as well as
for the main thread. Should I create connections using individual port
number? Or there is some other way to make my threads really independent?
Thank you, Sergey.

Sergey Belikov <belikov@bnl.gov> writes:
> ... Each thread
> creates it's own PGconn connection, and starts lo_export(conn, loid,
> filename) procedure. The problem is: after starting 2-3 threads SELECT
> query of the main program  fails because "another command is already in
> progress".

I'll bet lunch that you have not actually created a PGconn for each
thread --- or else that one of the threads is using the wrong PGconn
object.

            regards, tom lane

Tom Lane wrote:

>Sergey Belikov <belikov@bnl.gov> writes:
>
>>... Each thread
>>creates it's own PGconn connection, and starts lo_export(conn, loid,
>>filename) procedure. The problem is: after starting 2-3 threads SELECT
>>query of the main program  fails because "another command is already in
>>progress".
>>
>
>I'll bet lunch that you have not actually created a PGconn for each
>thread --- or else that one of the threads is using the wrong PGconn
>object.
>
>            regards, tom lane
>
Hi Tom!
You won! The probelm was not PGConns, but my main thread - I'm using
non-blocking psql  functions - I have to implement setQueryTimeout
mechanism myself because it is ignored (by now) by PSQL/ODBC driver. And
it looks like I was sending two queries without waiting while the first
one completes.
At least now my program works properly.
Thank you, Sergey.