Thread: 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.
Attachment
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