Message-ID: <440443A2.5080907@power.com.pl>
Date: Tue, 28 Feb 2006 13:35:46 +0100
From: Robert Gruszka <rgruszka@power.com.pl>
User-Agent: Mozilla Thunderbird 1.0.7 (X11/20051017)
MIME-Version: 1.0
To: pgsql-interfaces@postgresql.org
Subject: multiple threads using one connection
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

I have multi-thread application written in Ada which uses APQ. All 
threads use the same connection (can't afford one connection per 
thread). I compiled libpq with --enable-thread-safty option.
When two threads try to execute a query at the same time one of them is 
blocked and even when the other one finishes it is not unblocked.

I wrote simple test in c to find out is it APQ fault. But the result was 
the same. Am I doing something wrong?

My libpq version is 8.0.4 and here's the code of this test:

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sys/types.h>
#include "libpq-fe.h"
#include <pthread.h>

/* for ntohl/htonl */
#include <netinet/in.h>
#include <arpa/inet.h>

PGconn     *conn;

  static void
exit_nicely(PGconn *conn)
{
  //PQfinish(conn);
}


void *run(void *ptr)
{
  char *message;
  PGresult   *res;
  message = (char *)ptr;
  res = PQexec(conn, "select * from foo;");

  if (PQresultStatus(res) != PGRES_TUPLES_OK)
  {
    fprintf(stderr, "select failed: %s\n", PQerrorMessage(conn));
    PQclear(res);
    exit_nicely(conn);
    return;
  }

  PQclear(res);
  printf ("%s completed\n", message);

}

  int
main(int argc, char **argv)
{
  const char *conninfo;
  pthread_t thread1, thread2;
  char *message1 = "Thread 1";
  char *message2 = "Thread 2";
  int  iret1, iret2;

    //TODO
  conninfo = "dbname =  user =  password = ";

  /* Make a connection to the database */
  conn = PQconnectdb(conninfo);

  /* Check to see that the backend connection was successfully made */
  if (PQstatus(conn) != CONNECTION_OK)
  {
    fprintf(stderr, "Connection to database failed: %s",
        PQerrorMessage(conn));
    exit_nicely(conn);
  }
  else
  {
    fprintf(stderr, "Connected.\n");
  }

  PGresult   *res;
  res = PQexec(conn, "drop table foo;create table foo(id int);");

  if (PQresultStatus(res) != PGRES_COMMAND_OK)
  {
    fprintf(stderr, "create table failed: %s\n", PQerrorMessage(conn));
    PQclear(res);
    exit_nicely(conn);
    return;
  }

  iret1 = pthread_create( &thread1, NULL, run, (void*) message1);
  iret2 = pthread_create( &thread2, NULL, run, (void*) message2);

  pthread_join( thread1, NULL);
  pthread_join( thread2, NULL);
  /* close the connection to the database and cleanup */
  PQfinish(conn);

  return 0;
}