Обсуждение: Possible bug in ECPGlib thread-safety (Postgres 7.4)...

Dear All,

trying to run some tests for the thread-safety of the ecpg library, I
modified the test_thread.pgc to be like the one attached (test_thread1.pgc).

The changes are as follows:
1) All the connections used for insertions, begin in main function (and not
inside each thread)
2) The statement "EXEC SQL SET CONNECTION <conn> followed by the inserts is
used, instead of the"EXEC SQL AT <conn> ..." statement.
3) A new line for ECPGlog was added.

The results of executing the code are unexpected. Either a core dump
results, or one of the two threads inserts the rows in the table and the
process never returns to OS prompt, while the other thread seems to insert
the rows, but never commits.

A log file produced by ECPGlog is also attached.

Platforms tested: SunOS 5.8 and Linux RH9 (with the same results)

Any suggestions appreciated.

PS. I've found out that somebody else had about the same problem, but that
was during testing phase and I assumed that it could have been solved by
now. The links found are:
http://archives.postgresql.org/pgsql-hackers/2003-06/msg00513.php
http://archives.postgresql.org/pgsql-hackers/2003-06/msg00792.php

Demetres Pantermalis
Intracom SA
Network Support Systems.

Hi Demetres, I'm the guy whose emails you quoted below! 

You *must* always use the AT conn_name in a multi-threaded application... 
using SET CONNECTION will not work.

I assume your test results work if you use the thread test application as 
included (I wrote it, so I'd be interested to know if it works, as I have 
not tested RH9 and SunOS5.8 (though I tested RH7.3 and SunOS2.6)

Regards, Philip Yarra.

On Thu, 4 Dec 2003, Demetres Pantermalis wrote:

> Dear All,
> 
> trying to run some tests for the thread-safety of the ecpg library, I
> modified the test_thread.pgc to be like the one attached (test_thread1.pgc).
> 
> The changes are as follows:
> 1) All the connections used for insertions, begin in main function (and not
> inside each thread)
> 2) The statement "EXEC SQL SET CONNECTION <conn> followed by the inserts is
> used, instead of the"EXEC SQL AT <conn> ..." statement.
> 3) A new line for ECPGlog was added.
> 
> The results of executing the code are unexpected. Either a core dump
> results, or one of the two threads inserts the rows in the table and the
> process never returns to OS prompt, while the other thread seems to insert
> the rows, but never commits.
> 
> A log file produced by ECPGlog is also attached.
> 
> Platforms tested: SunOS 5.8 and Linux RH9 (with the same results)
> 
> Any suggestions appreciated.
> 
> PS. I've found out that somebody else had about the same problem, but that
> was during testing phase and I assumed that it could have been solved by
> now. The links found are:
> http://archives.postgresql.org/pgsql-hackers/2003-06/msg00513.php
> http://archives.postgresql.org/pgsql-hackers/2003-06/msg00792.php
> 
> Demetres Pantermalis
> Intracom SA
> Network Support Systems.
> 

Hi Philip and thanks for your *instant* reply.

Indeed the test_thread.pgc compiles and runs successfully in both mentioned
platforms.
I've tried it many times with many rows (100000 rows).

The reason I started this issue, is because in the documentation it is not
clear that the "SET CONNECTION" should not be used in multi-threaded
applications.

Anyway, are there any plans to change the behavior of the "SET CONNECTION"
to be as I have expected, in the near future???

Much obliged!!!

Demetres Pantermalis
Intracom SA
Network Support Systems

-----Original Message-----
From: Philip Yarra [mailto:philip@utiba.com]
Sent: Thursday, December 04, 2003 2:08 PM
To: Demetres Pantermalis
Cc: pgsql-interfaces@postgresql.org
Subject: Re: [INTERFACES] Possible bug in ECPGlib thread-safety (Postgres
7.4)...


Hi Demetres, I'm the guy whose emails you quoted below!

You *must* always use the AT conn_name in a multi-threaded application...
using SET CONNECTION will not work.

I assume your test results work if you use the thread test application as
included (I wrote it, so I'd be interested to know if it works, as I have
not tested RH9 and SunOS5.8 (though I tested RH7.3 and SunOS2.6)

Regards, Philip Yarra.

On Thu, 4 Dec 2003, Demetres Pantermalis wrote:

> Dear All,
>
> trying to run some tests for the thread-safety of the ecpg library, I
> modified the test_thread.pgc to be like the one attached
(test_thread1.pgc).
>
> The changes are as follows:
> 1) All the connections used for insertions, begin in main function (and
not
> inside each thread)
> 2) The statement "EXEC SQL SET CONNECTION <conn> followed by the inserts
is
> used, instead of the"EXEC SQL AT <conn> ..." statement.
> 3) A new line for ECPGlog was added.
>
> The results of executing the code are unexpected. Either a core dump
> results, or one of the two threads inserts the rows in the table and the
> process never returns to OS prompt, while the other thread seems to insert
> the rows, but never commits.
>
> A log file produced by ECPGlog is also attached.
>
> Platforms tested: SunOS 5.8 and Linux RH9 (with the same results)
>
> Any suggestions appreciated.
>
> PS. I've found out that somebody else had about the same problem, but that
> was during testing phase and I assumed that it could have been solved by
> now. The links found are:
> http://archives.postgresql.org/pgsql-hackers/2003-06/msg00513.php
> http://archives.postgresql.org/pgsql-hackers/2003-06/msg00792.php
>
> Demetres Pantermalis
> Intracom SA
> Network Support Systems.
>

Hi Demetres,

> Indeed the test_thread.pgc compiles and runs successfully in both mentioned
> platforms.

Okay, thanks.

> The reason I started this issue, is because in the documentation it is not
> clear that the "SET CONNECTION" should not be used in multi-threaded
> applications.

You're right, the current documentation is not clear on this point... I
submitted a patch recently to address this issue (unfortunately not in time
for the 7.4 release).

In light of your question, I might add some detail related to SET CONNECTION.

> Anyway, are there any plans to change the behavior of the "SET CONNECTION"
> to be as I have expected, in the near future???

I don't think so... the behaviour of SET CONNECTION is a bit confusing, but I
think documenting it clearly would suffice.

The reason for this is below (I'm going from memory here, if I get this wrong
can someone correct me please?)

Unlike the libpq C interface, there is no "connection" variable maintained in
ECPG... instead, ECPG internally maintains a global list of open connections.
When a client application issues an EXEC SQL, a connection is retrieved from
the list, and the SQL statement is executed on that connection. The first
connection in the list is the "default" connection, so if you don't specify a
named connection using "AT conn_name" ECPG grabs the first connection in the
list.

I'm pretty sure what happens when you issue EXEC SQL SET CONNECTION is that it
sets the the default connection, so each of your threads would simply have
shuffled the default connection. Then when each thread attempts to EXEC SQL
they grab the same connection... whichever connection was made the default by
the last thread to issue EXEC SQL SET CONNECTION.

I believe this behaviour is consistent with other DBMS implementations of
embedded SQL, but I'm shooting from the hip.

Let me know if there are any other specific things you think ought to be in
the documentation too, and I will try to add them.

Regards, Philip Yarra.

On Fri, Dec 05, 2003 at 09:44:19AM +1100, Philip Yarra wrote:
> The reason for this is below (I'm going from memory here, if I get this wrong 
> can someone correct me please?)
> ...
> I'm pretty sure what happens when you issue EXEC SQL SET CONNECTION is that it 
> sets the the default connection, so each of your threads would simply have 
> shuffled the default connection. Then when each thread attempts to EXEC SQL 
> they grab the same connection... whichever connection was made the default by 
> the last thread to issue EXEC SQL SET CONNECTION.

This is correct. SET CONNECTION just defines the default.

Michael
-- 
Michael Meskes
Email: Michael at Fam-Meskes dot De
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

Philip Yarra writes:> Unlike the libpq C interface, there is no "connection" variable maintained in > ECPG... instead,
ECPGinternally maintains a global list of open connections. > When a client application issues an EXEC SQL, a
connectionis retrieved from > the list, and the SQL statement is executed on that connection. The first > connection in
thelist is the "default" connection, so if you don't specify a > named connection using "AT conn_name" ECPG grabs the
firstconnection in the > list.> > I'm pretty sure what happens when you issue EXEC SQL SET CONNECTION is that it > sets
thethe default connection, so each of your threads would simply have > shuffled the default connection. Then when each
threadattempts to EXEC SQL > they grab the same connection... whichever connection was made the default by > the last
threadto issue EXEC SQL SET CONNECTION.> > I believe this behaviour is consistent with other DBMS implementations of >
embeddedSQL, but I'm shooting from the hip.
 

Not sure on the consistency, that's one thing ESQL/C rarely is!

Anyway you're right regarding the SET CONNECTION behaviour. However
it'd be fairly simple to change things such that each thread keeps
track of its "current" connection (set by connecting and SET
CONNECTION) by using thread local storage.

It'd be a definite improvement over having to specify the connection
on each call!

I'll probably do this at some point, but since i'm off to India on
Monday for the rest of December it'll not be till the New Year at the
earliest!

L.

I have updated the 7.4.X does and CVS head docs to mention that ecpg SET
CONNECTION is not thread-aware, and I have added this to the ecpg TODO:
       o Make SET CONNECTION thread-aware

---------------------------------------------------------------------------

Philip Yarra wrote:
> Hi Demetres, I'm the guy whose emails you quoted below! 
> 
> You *must* always use the AT conn_name in a multi-threaded application... 
> using SET CONNECTION will not work.
> 
> I assume your test results work if you use the thread test application as 
> included (I wrote it, so I'd be interested to know if it works, as I have 
> not tested RH9 and SunOS5.8 (though I tested RH7.3 and SunOS2.6)
> 
> Regards, Philip Yarra.
> 
> On Thu, 4 Dec 2003, Demetres Pantermalis wrote:
> 
> > Dear All,
> > 
> > trying to run some tests for the thread-safety of the ecpg library, I
> > modified the test_thread.pgc to be like the one attached (test_thread1.pgc).
> > 
> > The changes are as follows:
> > 1) All the connections used for insertions, begin in main function (and not
> > inside each thread)
> > 2) The statement "EXEC SQL SET CONNECTION <conn> followed by the inserts is
> > used, instead of the"EXEC SQL AT <conn> ..." statement.
> > 3) A new line for ECPGlog was added.
> > 
> > The results of executing the code are unexpected. Either a core dump
> > results, or one of the two threads inserts the rows in the table and the
> > process never returns to OS prompt, while the other thread seems to insert
> > the rows, but never commits.
> > 
> > A log file produced by ECPGlog is also attached.
> > 
> > Platforms tested: SunOS 5.8 and Linux RH9 (with the same results)
> > 
> > Any suggestions appreciated.
> > 
> > PS. I've found out that somebody else had about the same problem, but that
> > was during testing phase and I assumed that it could have been solved by
> > now. The links found are:
> > http://archives.postgresql.org/pgsql-hackers/2003-06/msg00513.php
> > http://archives.postgresql.org/pgsql-hackers/2003-06/msg00792.php
> > 
> > Demetres Pantermalis
> > Intracom SA
> > Network Support Systems.
> > 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

Lee Kindness wrote:
> Philip Yarra writes:
>  > Unlike the libpq C interface, there is no "connection" variable maintained in 
>  > ECPG... instead, ECPG internally maintains a global list of open connections. 
>  > When a client application issues an EXEC SQL, a connection is retrieved from 
>  > the list, and the SQL statement is executed on that connection. The first 
>  > connection in the list is the "default" connection, so if you don't specify a 
>  > named connection using "AT conn_name" ECPG grabs the first connection in the 
>  > list.
>  > 
>  > I'm pretty sure what happens when you issue EXEC SQL SET CONNECTION is that it 
>  > sets the the default connection, so each of your threads would simply have 
>  > shuffled the default connection. Then when each thread attempts to EXEC SQL 
>  > they grab the same connection... whichever connection was made the default by 
>  > the last thread to issue EXEC SQL SET CONNECTION.
>  > 
>  > I believe this behaviour is consistent with other DBMS implementations of 
>  > embedded SQL, but I'm shooting from the hip.
> 
> Not sure on the consistency, that's one thing ESQL/C rarely is!
> 
> Anyway you're right regarding the SET CONNECTION behaviour. However
> it'd be fairly simple to change things such that each thread keeps
> track of its "current" connection (set by connecting and SET
> CONNECTION) by using thread local storage.
> 
> It'd be a definite improvement over having to specify the connection
> on each call!
> 
> I'll probably do this at some point, but since i'm off to India on
> Monday for the rest of December it'll not be till the New Year at the
> earliest!

Added to TODO and documented.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

On Tue, 16 Dec 2003 12:08 pm, Bruce Momjian wrote:
> I have updated the 7.4.X does and CVS head docs to mention that ecpg SET
> CONNECTION is not thread-aware, and I have added this to the ecpg TODO:
>
>         o Make SET CONNECTION thread-aware

One thing that concerns me: I doubt this is supported in other database
systems... people who relied on this feature in ECPG would likely encounter
threading bugs if they port their code to other DBs. And threading bugs can
be nasty to find. Does anyone know how other DBs handle this issue?

I thinks it's a worthy feature, but perhaps the pre-compiler should warn
people that they are relying on non-standard behaviour?

Philip.

Philip Yarra wrote:
> On Tue, 16 Dec 2003 12:08 pm, Bruce Momjian wrote:
> > I have updated the 7.4.X does and CVS head docs to mention that ecpg SET
> > CONNECTION is not thread-aware, and I have added this to the ecpg TODO:
> > 
> >         o Make SET CONNECTION thread-aware
> 
> One thing that concerns me: I doubt this is supported in other database 
> systems... people who relied on this feature in ECPG would likely encounter 
> threading bugs if they port their code to other DBs. And threading bugs can 
> be nasty to find. Does anyone know how other DBs handle this issue?
> 
> I thinks it's a worthy feature, but perhaps the pre-compiler should warn 
> people that they are relying on non-standard behaviour?

It would cause people moving _from_ PostgreSQL, but no one does that, of
course.  :-)

I thought the standard wasn't clear so we could do it.  You have a good
point, but that AT clause is annoying people.   Other comments?  

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

Philip Yarra wrote:
> On Tue, 16 Dec 2003 12:08 pm, Bruce Momjian wrote:
> > I have updated the 7.4.X does and CVS head docs to mention that ecpg SET
> > CONNECTION is not thread-aware, and I have added this to the ecpg TODO:
> > 
> >         o Make SET CONNECTION thread-aware
> 
> One thing that concerns me: I doubt this is supported in other database 
> systems... people who relied on this feature in ECPG would likely encounter 
> threading bugs if they port their code to other DBs. And threading bugs can 
> be nasty to find. Does anyone know how other DBs handle this issue?
> 
> I thinks it's a worthy feature, but perhaps the pre-compiler should warn 
> people that they are relying on non-standard behaviour?

Maybe we could just document its thread-safety as non-standard.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

On Tue, 16 Dec 2003 12:28 pm, Bruce Momjian wrote:
> > I thinks it's a worthy feature, but perhaps the pre-compiler should warn
> > people that they are relying on non-standard behaviour?
>
> Maybe we could just document its thread-safety as non-standard.

Yes, but my concern is that poor suckers who try to port their code to other
DBs won't be going and reading the PostgreSQL doco to figure out why their
code goes suddenly goes "thud!"

Maybe an additional flag for the pre-compiler to turn the warning off (how
about '--fast-and-loose-thread-behaviour', '-f' for short ?) would stop it
driving regular users mad, while alerting newer ECPG users that they are
relying on an extension that might cause portability issues.

> It would cause people moving _from_ PostgreSQL, but no one does that, of
> course.  :-)

Not voluntarily, anyway. Off-topic: having suffered the ravages of Sybase's
isql lately, I've been sorely tempted to add a Sybase-compatibility layer to
psql. I don't know what would be involved (a lot, I imagine) but it's
tempting.

> that AT clause is annoying people

Yes, it's a pain, but multi-threaded apps are more work anyway - I don't think
"AT conn_name" is as onerous as pthread_mutex_lock()ing, which they will
(hopefully) be doing a fair bit of.

Don't get me wrong, the thread-safe extension would be nice, I'd just hate to
see people rely on it without being aware of the portability cost.

Philip.

Philip Yarra wrote:
> On Tue, 16 Dec 2003 12:28 pm, Bruce Momjian wrote:
> > > I thinks it's a worthy feature, but perhaps the pre-compiler should warn 
> > > people that they are relying on non-standard behaviour?
> > 
> > Maybe we could just document its thread-safety as non-standard.
> 
> Yes, but my concern is that poor suckers who try to port their code to other 
> DBs won't be going and reading the PostgreSQL doco to figure out why their 
> code goes suddenly goes "thud!"
> 
> Maybe an additional flag for the pre-compiler to turn the warning off (how 
> about '--fast-and-loose-thread-behaviour', '-f' for short ?) would stop it 
> driving regular users mad, while alerting newer ECPG users that they are 
> relying on an extension that might cause portability issues. 

I don't think we are going to accept annoying our users on every
compile.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

On Tue, 16 Dec 2003 12:58 pm, Bruce Momjian wrote:
> I don't think we are going to accept annoying our users on every
> compile.

Wouldn't people generally be using Makefiles or similar?
ECPG_FLAGS = -t -f
would do the trick. i can't imagine many people would seriously be typing
"ecpg -t test.pgc && cc -I/usr/local/pgsql/include -L/usr/local/pgsql/lib
-pthread -lecpg -o test test.c" and baulk at an extra -f flag!

Having to specify a couple of flags for the desired behaviour is not uncommon
- Sybase's cpre is unbelievably verbose unless told otherwise. We just put
the "--shut-up" flags in the Makefile. Point is, we read the manual to find
out what the flags meant.

Philip.

Philip Yarra wrote:
> On Tue, 16 Dec 2003 12:58 pm, Bruce Momjian wrote:
> > I don't think we are going to accept annoying our users on every
> > compile.
> 
> Wouldn't people generally be using Makefiles or similar? 
> ECPG_FLAGS = -t -f
> would do the trick. i can't imagine many people would seriously be typing
> "ecpg -t test.pgc && cc -I/usr/local/pgsql/include -L/usr/local/pgsql/lib 
> -pthread -lecpg -o test test.c" and baulk at an extra -f flag!
> 
> Having to specify a couple of flags for the desired behaviour is not uncommon 
> - Sybase's cpre is unbelievably verbose unless told otherwise. We just put 
> the "--shut-up" flags in the Makefile. Point is, we read the manual to find 
> out what the flags meant.

That has never been our style, and I don't think we want to start now.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073