[BUGS] BUG #1569: Threads don't see ecpg default connection

The following bug has been logged online:

Bug reference: 1569
Logged by: Gavin Scott
Email address: gavin (AT) ipalsoftware (DOT) com
PostgreSQL version: 8.0.1
Operating system: Fedora Core 3
Description: Threads don't see ecpg default connection
Details:

When using postgresql 8.0 compiled with --enable-thread-safety, new threads
no longer see the default ecpg connection. That was not the case in 7.4.x
compiled with or without --enable-thread-safety.

TEST CASE

The program at the end of this mail sets up a database table named
"dbthreadtest" in the default database. It then spawns 5 threads which each
do a select from that table using the default connection.

If the program is saved as dbthreadtest.pgc, compile with:

ecpg -t -o dbthreadtest.c dbthreadtest.pgc
gcc -Wall -o dbthreadtest dbthreadtest.c -lecpg -lpthread

Results under 7.4.x / 8.0 without --enable-thread-safety:

[gavin@hesse protocol_lib]$ psql --version
psql (PostgreSQL) 7.4.7
contains support for command-line editing
[gavin@hesse protocol_lib]$ ./dbthreadtest
got id = 1
got id = 1
got id = 1
got id = 1
got id = 1

Results under 8.0 with --enable-thread-safety:

[gavin@marquez protocol_lib]$ psql --version
psql (PostgreSQL) 8.0.1
contains support for command-line editing
[gavin@marquez protocol_lib]$ ./dbthreadtest
'No such connection NULL in line 76.', sqlcode = -220 select id

TEST PROGRAM

/* -*-C-*- */

#include <stdlib.h>
#include <pthread.h>

#define CHECK_SQL(fmt, args...) \
do \
{ \
if (sqlca.sqlcode != ECPG_NO_ERROR) \
{ \
fprintf (stderr, "'%s', sqlcode = %ld " fmt "\n", \
sqlca.sqlerrm.sqlerrmc, \
sqlca.sqlcode, ## args); \
exit (1); \
} \
} \
while (0)

#define FATAL(fmt, args...) \
do \
{ \
fprintf (stderr, fmt "\n", ## args); \
exit (1); \
} \
while (0)

pthread_mutex_t global_lock;
pthread_t global_threads[5];

void
setup_db ()
{
exec sql begin declare section;
const char *_user;
exec sql end declare section;

pthread_mutex_lock (&global_lock);

_user = getenv ("LOGNAME");

exec sql connect to :_user;
CHECK_SQL ("connect");

exec sql create table dbthreadtest (id int);
CHECK_SQL ("create dbthreadtest");

exec sql insert into dbthreadtest (id) values (1);
CHECK_SQL ("insert 1");

pthread_mutex_unlock (&global_lock);
}

void
teardown_db ()
{
pthread_mutex_lock (&global_lock);

exec sql drop table dbthreadtest;
CHECK_SQL ("drop dbthreadtest");

exec sql disconnect;
CHECK_SQL ("disconnect");

pthread_mutex_unlock (&global_lock);
}

void *query_db (void *ignorep)
{
exec sql begin declare section;
int _id;
exec sql end declare section;

pthread_mutex_lock (&global_lock);
exec sql select id into :_id from dbthreadtest;
CHECK_SQL ("select id");

fprintf (stdout, "got id = %d\n", _id);
pthread_mutex_unlock (&global_lock);

return NULL;
}

int
main ()
{
int i;

pthread_mutex_init (&global_lock, NULL);

setup_db ();

for (i = 0; i < sizeof (global_threads) / sizeof (global_threads[0]);
++i)
{
if (pthread_create (&global_threads[i], NULL, query_db, NULL))
FATAL ("pthread_create %d failed", i);
}

for (i = 0; i < sizeof (global_threads) / sizeof (global_threads[0]);
++i)
{
if (pthread_join (global_threads[i], NULL))
FATAL ("pthread_join %d failed", i);
}

teardown_db ();

return 0;
}

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Are you still seeing a failure in ecpg? I ran your test here and got:

got id = 0
got id = 0
got id = 0
got id = 0
got id = 0

This is with thread safety enabled and CVS HEAD, which matches 8.0.X.

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

Gavin Scott wrote:

The following bug has been logged online:

Bug reference: 1569
Logged by: Gavin Scott
Email address: gavin (AT) ipalsoftware (DOT) com
PostgreSQL version: 8.0.1
Operating system: Fedora Core 3
Description: Threads don't see ecpg default connection
Details:

When using postgresql 8.0 compiled with --enable-thread-safety, new threads
no longer see the default ecpg connection. That was not the case in 7.4.x
compiled with or without --enable-thread-safety.

TEST CASE

The program at the end of this mail sets up a database table named
"dbthreadtest" in the default database. It then spawns 5 threads which each
do a select from that table using the default connection.

If the program is saved as dbthreadtest.pgc, compile with:

ecpg -t -o dbthreadtest.c dbthreadtest.pgc
gcc -Wall -o dbthreadtest dbthreadtest.c -lecpg -lpthread

Results under 7.4.x / 8.0 without --enable-thread-safety:

[gavin@hesse protocol_lib]$ psql --version
psql (PostgreSQL) 7.4.7
contains support for command-line editing
[gavin@hesse protocol_lib]$ ./dbthreadtest
got id = 1
got id = 1
got id = 1
got id = 1
got id = 1

Results under 8.0 with --enable-thread-safety:

[gavin@marquez protocol_lib]$ psql --version
psql (PostgreSQL) 8.0.1
contains support for command-line editing
[gavin@marquez protocol_lib]$ ./dbthreadtest
'No such connection NULL in line 76.', sqlcode = -220 select id

TEST PROGRAM

/* -*-C-*- */

#include <stdlib.h
#include <pthread.h

#define CHECK_SQL(fmt, args...) \
do \
{ \
if (sqlca.sqlcode != ECPG_NO_ERROR) \
{ \
fprintf (stderr, "'%s', sqlcode = %ld " fmt "\n", \
sqlca.sqlerrm.sqlerrmc, \
sqlca.sqlcode, ## args); \
exit (1); \
} \
} \
while (0)

#define FATAL(fmt, args...) \
do \
{ \
fprintf (stderr, fmt "\n", ## args); \
exit (1); \
} \
while (0)

pthread_mutex_t global_lock;
pthread_t global_threads[5];

void
setup_db ()
{
exec sql begin declare section;
const char *_user;
exec sql end declare section;

pthread_mutex_lock (&global_lock);

_user = getenv ("LOGNAME");

exec sql connect to :_user;
CHECK_SQL ("connect");

exec sql create table dbthreadtest (id int);
CHECK_SQL ("create dbthreadtest");

exec sql insert into dbthreadtest (id) values (1);
CHECK_SQL ("insert 1");

pthread_mutex_unlock (&global_lock);
}

void
teardown_db ()
{
pthread_mutex_lock (&global_lock);

exec sql drop table dbthreadtest;
CHECK_SQL ("drop dbthreadtest");

exec sql disconnect;
CHECK_SQL ("disconnect");

pthread_mutex_unlock (&global_lock);
}

void *query_db (void *ignorep)
{
exec sql begin declare section;
int _id;
exec sql end declare section;

pthread_mutex_lock (&global_lock);
exec sql select id into :_id from dbthreadtest;
CHECK_SQL ("select id");

fprintf (stdout, "got id = %d\n", _id);
pthread_mutex_unlock (&global_lock);

return NULL;
}

int
main ()
{
int i;

pthread_mutex_init (&global_lock, NULL);

setup_db ();

for (i = 0; i < sizeof (global_threads) / sizeof (global_threads[0]);
++i)
{
if (pthread_create (&global_threads[i], NULL, query_db, NULL))
FATAL ("pthread_create %d failed", i);
}

for (i = 0; i < sizeof (global_threads) / sizeof (global_threads[0]);
++i)
{
if (pthread_join (global_threads[i], NULL))
FATAL ("pthread_join %d failed", i);
}

teardown_db ();

return 0;
}

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On Sat, May 07, 2005 at 12:37:24PM -0400, Bruce Momjian wrote:

Are you still seeing a failure in ecpg? I ran your test here and got:
...

Bruce,

this is fixed AFAIK. I just never saw the original post on -bugs and
thus did not answer there either.

Michael

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

On Sat, 2005-05-07 at 12:37 -0400, Bruce Momjian wrote:

Are you still seeing a failure in ecpg? I ran your test here and got:

got id = 0
got id = 0
got id = 0
got id = 0
got id = 0

This is with thread safety enabled and CVS HEAD, which matches 8.0.X.

Hi Bruce,

This was fixed by r1.25 of pgsql/src/interfaces/ecpg/ecpglib/connect.c :

----------------------------
revision 1.25
date: 2005/04/14 10:08:57; author: meskes; state: Exp; lines: +24 -7
Added patch by Philip Yarra <philip.yarra (AT) internode (DOT) on.net> for a bug in
thread
support.
----------------------------

--
Gavin Scott
gavin (AT) ipalsoftware (DOT) com


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster