pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedmultiple threads using one connection
4+ messages / 4 participants
[nested] [flat]
* multiple threads using one connection
@ 2006-02-28 12:35 Robert Gruszka <rgruszka@power.com.pl>
0 siblings, 1 reply; 4+ messages in thread
From: Robert Gruszka @ 2006-02-28 12:35 UTC (permalink / raw)
To: pgsql-interfaces
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;
}
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: multiple threads using one connection
@ 2006-02-28 15:09 Alvaro Herrera <alvherre@commandprompt.com>
parent: Robert Gruszka <rgruszka@power.com.pl>
0 siblings, 1 reply; 4+ messages in thread
From: Alvaro Herrera @ 2006-02-28 15:09 UTC (permalink / raw)
To: Robert Gruszka <rgruszka@power.com.pl>; +Cc: pgsql-interfaces
Robert Gruszka wrote:
> 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.
The libpq API is not really intended to be used by multiple threads
simultaneously (unless you make sure only one thread is submitting a
query and waiting for the result at any time).
The --enable-thread-safety flag is merely to ensure that the calls to
other libraries (Kerberos, etc) are handled in a thread-safe manner. It
doesn't have any effect on the thread safety of the libpq API itself.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: multiple threads using one connection
@ 2006-02-28 18:17 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 4+ messages in thread
From: Bruce Momjian @ 2006-02-28 18:17 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: Robert Gruszka <rgruszka@power.com.pl>; pgsql-interfaces
Alvaro Herrera wrote:
> Robert Gruszka wrote:
> > 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.
>
> The libpq API is not really intended to be used by multiple threads
> simultaneously (unless you make sure only one thread is submitting a
> query and waiting for the result at any time).
>
> The --enable-thread-safety flag is merely to ensure that the calls to
> other libraries (Kerberos, etc) are handled in a thread-safe manner. It
> doesn't have any effect on the thread safety of the libpq API itself.
Just to clarify, libpq is thread-safe is each thread uses its own
connection object.
--
Bruce Momjian http://candle.pha.pa.us
SRA OSS, Inc. http://www.sraoss.com
+ If your life is a hard drive, Christ can be your backup. +
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: multiple threads using one connection
@ 2006-03-01 00:18 Philip Yarra <philip@utiba.com>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Philip Yarra @ 2006-03-01 00:18 UTC (permalink / raw)
To: pgsql-interfaces; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; Alvaro Herrera <alvherre@commandprompt.com>; Robert Gruszka <rgruszka@power.com.pl>
On Wed, 1 Mar 2006 05:17 am, Bruce Momjian wrote:
> Just to clarify, libpq is thread-safe is each thread uses its own
> connection object.
Hope it's okay if I clarify this a little further, Bruce...
Robert, I think you *should* be able to use the same connection from multiple
threads so long as you do your own work to avoid concurrent use of the
connection. I'm sorry, I know less than nothing about Ada, but in C terms
you'd be using a mutex, acquiring it before your thread issues a statement on
the connection, reading back all data etc., then releasing the mutex. Other
threads can then acquire the mutex and use the connection. I assume Ada
provides some similar sort of concurrency control.
On a side note, the preferred way is to use a connection per thread... and
given the overhead of thread creation, ISTM the cost of a connection wouldn't
be that high... but I assume you have some good reason for doing it this way.
I suppose a connection pool might be another way you could handle this
requirement.
I say it *should* work because I know this works with ECPG, and AFAIK it uses
libpq calls, so should be similar for your language binding, but please test
it with a sample app before assuming that I know what I'm talking about. Can
someone correct me if I have this wrong? It's been a while since I had to
think about this stuff...
Regards, Philip.
--
"Debugging is twice as hard as writing the code in the first place.
Therefore, if you write the code as cleverly as possible, you are,
by definition, not smart enough to debug it." - Brian W. Kernighan
-----------------
Utiba Pty Ltd
This message has been scanned for viruses and
dangerous content by Utiba mail server and is
believed to be clean.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-03-01 00:18 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-28 12:35 multiple threads using one connection Robert Gruszka <rgruszka@power.com.pl>
2006-02-28 15:09 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-02-28 18:17 ` Bruce Momjian <pgman@candle.pha.pa.us>
2006-03-01 00:18 ` Philip Yarra <philip@utiba.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox