Re: Threads With Libpq - Mailing list pgsql-general

On Wed, Aug 1, 2012 at 8:09 AM, Alban Hertroys <haramrae@gmail.com> wrote:
> On 1 Aug 2012, at 14:32, dinesh kumar wrote:
>
>> Respected All,
>>
>> This is my first request/post in PG-Generals. If it is not the place for these kind of queries, then please guide me where i need to be.
>>
>> I have a quick question regarding "pthread" with PostgreSQL 9.0 Libpq. I'm facing a problem with "Pthread" and libpq. Please find the below program behavoiur.
>>
>> Connection_To_PG()
>> {
>> /* Making a connection to PG 9.0 */
>> }
>>
>> void* Independent_Thread1()
>> {
>> while(1)
>> {
>> sleep(5);
>> /* Doing 1 Insert Operation on Table A*/
>> }
>> }
>>
>> void* Independent_Thread2()
>> {
>> while(1)
>> {
>> sleep(5);
>> /*Doing 1 Insert Operation on Table B*/
>> }
>>
>> main()
>> {
>> pthread Ind1,Ind2;
>> Connection_TO_PG();
>> pthread_create(&Ind1,NULL,&Independent_Thread1,NULL);
>> pthread_create(&Ind2,NULL,&Independent_Thread2,NULL);
>> if(pthread_join(Ind1,NULL)<0)
>> {
>> printf("Ind1 is completed");
>> }
>> if(pthread_join(Ind2,NULL)<0)
>> {
>> printf("Ind2 is completed");
>> }
>> }
>
> You need a separate connection per thread or you need to synchronise your queries onto the single central connection, meaning that other threads need to be blocked (from performing queries) while any thread is performing a query.
>
> Alban Hertroys
>
> --
> Screwing up is an excellent way to attach something to the ceiling.

Yeah.  Also, OP left out the most important detail, namely where and
how the connection object stored.  If the objective is to try and make
two concurrent actions on the database, I'd consider giving
asynchronous queries a whirl:
http://www.postgresql.org/docs/8.1/static/libpq-async.html.  Basically
you pair a PQsendQuery with a PQgetResult.  It's a lot easier to code
than multi-threaded libpq and tends to be more robust in my
experience.  If you must use threads, you'll want to keep a connection
with each thread instance -- I'd avoid any temptation to use a client
side connection pool.

merlin