Thread: PGconn thread safety

PGconn thread safety

From
ahoward
Date:
does anyone know if PGconns are safe to use from multiple threads of control?

-a

--

 ====================================
 | Ara Howard
 | NOAA Forecast Systems Laboratory
 | Information and Technology Services
 | Data Systems Group
 | R/FST 325 Broadway
 | Boulder, CO 80305-3328
 | Email: ahoward@fsl.noaa.gov
 | Phone:  303-497-7238
 | Fax:    303-497-7259
 ====================================


Re: PGconn thread safety

From
Neil Conway
Date:
On Thu, 2003-02-06 at 21:19, ahoward wrote:
> does anyone know if PGconns are safe to use from multiple threads of control?

http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/libpq-threading.html

"libpq is thread-safe as of PostgreSQL 7.0, so long as no two threads
attempt to manipulate the same PGconn object at the same time."

Cheers,

Neil
--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC




Re: PGconn thread safety

From
Tom Lane
Date:
Neil Conway <neilc@samurai.com> writes:
> On Thu, 2003-02-06 at 21:19, ahoward wrote:
>> does anyone know if PGconns are safe to use from multiple threads of control?

> http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/libpq-threading.html

> "libpq is thread-safe as of PostgreSQL 7.0, so long as no two threads
> attempt to manipulate the same PGconn object at the same time."

That's the theory anyway.  I believe it actually is free of unsafe uses
of static variables.  However, someone recently pointed out that it uses
some libc routines that probably aren't thread-safe; so there's some
cleanup yet to do before we can claim real thread safety.

            regards, tom lane

Re: PGconn thread safety

From
Shridhar Daithankar
Date:
On Friday 07 February 2003 12:44 pm, you wrote:
> Neil Conway <neilc@samurai.com> writes:
> That's the theory anyway.  I believe it actually is free of unsafe uses
> of static variables.  However, someone recently pointed out that it uses
> some libc routines that probably aren't thread-safe; so there's some
> cleanup yet to do before we can claim real thread safety.

Well, I ran a mutlithreaded test where around 30 connections were hammered in
a mutlihtreaded servers using libpq for 100,000 transactions. I didn't notice
any data inconsistency.

Only noticable thing was that postgresql was not returning even simplest
result in less than 200 ms. So in order to achieve a good throughput, I had
to up the number of connections. The max throughput. was 200ms/no. of
connections.

but that is a different issue..

 Shridhar





Re: PGconn thread safety

From
Lee Kindness
Date:
Well the areas I have patched would have /potentially/ caused
inaccurate error messages (using global errno). Also potential
corruption on connect to database if 2+ threads were trying to do this
at once - a non-threadsafe fucntion was being used to retrieve all
local usernames.

I've been a bit busy of late, the code itself is submitted to
pgsql-patches but configure work is still to be done.

L.

Shridhar Daithankar writes:
 > On Friday 07 February 2003 12:44 pm, you wrote:
 > > Neil Conway <neilc@samurai.com> writes:
 > > That's the theory anyway.  I believe it actually is free of unsafe uses
 > > of static variables.  However, someone recently pointed out that it uses
 > > some libc routines that probably aren't thread-safe; so there's some
 > > cleanup yet to do before we can claim real thread safety.
 >
 > Well, I ran a mutlithreaded test where around 30 connections were hammered in
 > a mutlihtreaded servers using libpq for 100,000 transactions. I didn't notice
 > any data inconsistency.
 >
 > Only noticable thing was that postgresql was not returning even simplest
 > result in less than 200 ms. So in order to achieve a good throughput, I had
 > to up the number of connections. The max throughput. was 200ms/no. of
 > connections.
 >
 > but that is a different issue..
 >
 >  Shridhar

Re: PGconn thread safety

From
ahoward
Date:
On Fri, 7 Feb 2003, Shridhar Daithankar wrote:

> On Friday 07 February 2003 12:44 pm, you wrote:
> > Neil Conway <neilc@samurai.com> writes:
> > That's the theory anyway.  I believe it actually is free of unsafe uses
> > of static variables.  However, someone recently pointed out that it uses
> > some libc routines that probably aren't thread-safe; so there's some
> > cleanup yet to do before we can claim real thread safety.
>


> Well, I ran a mutlithreaded test where around 30 connections were hammered =
> in=20
> a mutlihtreaded servers using libpq for 100,000 transactions. I didn't noti=
> ce=20
> any data inconsistency.=20

meaning your connections had no semaphore (or other) type thread protection?

-a


>
> Only noticable thing was that postgresql was not returning even simplest=20
> result in less than 200 ms. So in order to achieve a good throughput, I had=
> =20
> to up the number of connections. The max throughput. was 200ms/no. of=20
> connections.
>
> but that is a different issue..
>
>  Shridhar
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--

 ====================================
 | Ara Howard
 | NOAA Forecast Systems Laboratory
 | Information and Technology Services
 | Data Systems Group
 | R/FST 325 Broadway
 | Boulder, CO 80305-3328
 | Email: ahoward@fsl.noaa.gov
 | Phone:  303-497-7238
 | Fax:    303-497-7259
 ====================================


Re: PGconn thread safety

From
ahoward
Date:
On Fri, 7 Feb 2003, Lee Kindness wrote:

> Well the areas I have patched would have /potentially/ caused
> inaccurate error messages (using global errno). Also potential
> corruption on connect to database if 2+ threads were trying to do this
> at once - a non-threadsafe fucntion was being used to retrieve all
> local usernames.
>
> I've been a bit busy of late, the code itself is submitted to
> pgsql-patches but configure work is still to be done.
>
> L.


in my connection pool, all connections are made _before_ any thread access
takes place.  the pool will be used mostly for cgi programs so i really don't
care about error messages too much (seems like using res = PGExec might fix
this issue?) so it seems like my application may not need thread protection.

-a


>
> Shridhar Daithankar writes:
>  > On Friday 07 February 2003 12:44 pm, you wrote:
>  > > Neil Conway <neilc@samurai.com> writes:
>  > > That's the theory anyway.  I believe it actually is free of unsafe uses
>  > > of static variables.  However, someone recently pointed out that it uses
>  > > some libc routines that probably aren't thread-safe; so there's some
>  > > cleanup yet to do before we can claim real thread safety.
>  >
>  > Well, I ran a mutlithreaded test where around 30 connections were hammered in
>  > a mutlihtreaded servers using libpq for 100,000 transactions. I didn't notice
>  > any data inconsistency.
>  >
>  > Only noticable thing was that postgresql was not returning even simplest
>  > result in less than 200 ms. So in order to achieve a good throughput, I had
>  > to up the number of connections. The max throughput. was 200ms/no. of
>  > connections.
>  >
>  > but that is a different issue..
>  >
>  >  Shridhar
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--

 ====================================
 | Ara Howard
 | NOAA Forecast Systems Laboratory
 | Information and Technology Services
 | Data Systems Group
 | R/FST 325 Broadway
 | Boulder, CO 80305-3328
 | Email: ahoward@fsl.noaa.gov
 | Phone:  303-497-7238
 | Fax:    303-497-7259
 ====================================


Re: PGconn thread safety

From
"Shridhar Daithankar"
Date:
On 7 Feb 2003 at 14:40, ahoward wrote:

> On Fri, 7 Feb 2003, Shridhar Daithankar wrote:
>
> > On Friday 07 February 2003 12:44 pm, you wrote:
> > > Neil Conway <neilc@samurai.com> writes:
> > > That's the theory anyway.  I believe it actually is free of unsafe uses
> > > of static variables.  However, someone recently pointed out that it uses
> > > some libc routines that probably aren't thread-safe; so there's some
> > > cleanup yet to do before we can claim real thread safety.
> > Well, I ran a mutlithreaded test where around 30 connections were hammered =
> > in=20
> > a mutlihtreaded servers using libpq for 100,000 transactions. I didn't noti=
> > ce=20
> > any data inconsistency.=20
> meaning your connections had no semaphore (or other) type thread protection?

I had. But each pgConn object was used in a separate thread. All connections
were created before any threads. So that issue of non-thread safe function to
fetch local user names did not arise, I guess..

Bye
 Shridhar

--
The sight of death frightens them [Earthers].        -- Kras the Klingon, "Friday's
Child", stardate 3497.2