Thread safe connection-name mapping in ECPG. Is it required?
Hi all,
I was just going thr. the ecpg sources checking for thread safety.
It looks like the mutex protects the connections list in connection.c. I do
not like that from a application developers perspective.
If I am developing an application and using multiple connections in multiple
threads, I have to store a connection name for each connection as C string.
Of course, I also have to protect it across thread so that I can rightly tell
ecpg what connection I would be talking to next.
If an application can take care of a C string, it can also take care of a
connection structure. On top of it, it eliminates the list lookup. The
potential performance gain could be worth it if there are hundreds of
connections and a busy website/application server.
What I wonder is, do we really need to maintain that level of lookup? Can't we
just say a connection is a 'struct connection *' which should be opaque and
should not be touched or poked inside, just like PGConn.
Just a thought...
Shridhar
On Mon, Feb 23, 2004 at 09:27:40PM +0530, Shridhar Daithankar wrote:
It looks like the mutex protects the connections list in connection.c. I do
not like that from a application developers perspective.If I am developing an application and using multiple connections in multiple
threads, I have to store a connection name for each connection as C string.
Of course, I also have to protect it across thread so that I can rightly tell
ecpg what connection I would be talking to next.If an application can take care of a C string, it can also take care of a
connection structure. On top of it, it eliminates the list lookup. The
potential performance gain could be worth it if there are hundreds of
connections and a busy website/application server.What I wonder is, do we really need to maintain that level of lookup? Can't we
just say a connection is a 'struct connection *' which should be opaque and
should not be touched or poked inside, just like PGConn.
I'm not sure I understand you correctly. The SQL standard says you can
call your statement as this:
exec sql at CONNECTION select 1;
Here CONNECTION of course is a string, the name of the connection. So,
yes, we have to maintain that list to make sure we get the right
connection.
Or what were you asking?
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!
I'm not sure I understand you correctly. The SQL standard says you can
call your statement as this:
exec sql at CONNECTION select 1;Here CONNECTION of course is a string, the name of the connection. So,
yes, we have to maintain that list to make sure we get the right
connection.
I thought the main problem was an "exec sql set connection :hostvar",
where hostvar is a string determined at runtime. Else above could be translated at
precompile time to anything more efficient than a string search.
Oh, just noticed above is not standard, but sure is very useful.
Andreas
Import Notes
Resolved by subject fallback
Michael Meskes wrote:
On Mon, Feb 23, 2004 at 09:27:40PM +0530, Shridhar Daithankar wrote:
What I wonder is, do we really need to maintain that level of lookup? Can't we
just say a connection is a 'struct connection *' which should be opaque and
should not be touched or poked inside, just like PGConn.I'm not sure I understand you correctly. The SQL standard says you can
call your statement as this:
exec sql at CONNECTION select 1;Here CONNECTION of course is a string, the name of the connection. So,
yes, we have to maintain that list to make sure we get the right
connection.Or what were you asking?
I am asking for CONNECTION being a variable of data type 'connection *' rather
than 'const char *'. That would avoid name lookups.
Is that out of spec?
Shridhar
I am asking for CONNECTION being a variable of data type 'connection *' rather
than 'const char *'. That would avoid name lookups.Is that out of spec?
Yes, but the preprocessor could still add an optimization ala 'connection *' for
the hardcoded cases (exec sql set connection 'myconn1'; exec sql at 'myconn1' ...).
It needs to maintain the string list for the non hardcoded cases though.
Andreas
Import Notes
Resolved by subject fallback
Zeugswetter Andreas SB SD wrote:
I am asking for CONNECTION being a variable of data type 'connection *' rather
than 'const char *'. That would avoid name lookups.Is that out of spec?
Yes, but the preprocessor could still add an optimization ala 'connection *' for
the hardcoded cases (exec sql set connection 'myconn1'; exec sql at 'myconn1' ...).
It needs to maintain the string list for the non hardcoded cases though.
How about, allowing 'connection *'? If somebody puts a 'connection *' there it
is used. If it is a string a name search is performed. Best of both worlds.
Can that be an acceptable compromise?
Shridhar
On Fri, Feb 27, 2004 at 04:22:33PM +0530, Shridhar Daithankar wrote:
How about, allowing 'connection *'? If somebody puts a 'connection *' there
it is used. If it is a string a name search is performed. Best of both
worlds.
How shall anyone put a pointer to a connection struct inside the SQL
statement?
It would help me a lot if you'd be able to give some examples.
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!
On Friday 27 February 2004 20:54, Michael Meskes wrote:
On Fri, Feb 27, 2004 at 04:22:33PM +0530, Shridhar Daithankar wrote:
How about, allowing 'connection *'? If somebody puts a 'connection *'
there it is used. If it is a string a name search is performed. Best of
both worlds.How shall anyone put a pointer to a connection struct inside the SQL
statement?It would help me a lot if you'd be able to give some examples.
EXEC SQL BEGIN DECLARE SECTION;
connect *connectionPtr;
EXEC SQL END DECLARE SECTION;
EXEC SQL CONNECT TO db AS connectionPtr;
EXEC SQL AT connectionPtr SELECT 1;
After all, it is matter of parsing some code and emitting equivalent C code,
isn't it?
Shridhar
Sort of related, I was thinking about adding some more thread-related
code such that if a connection wasn't explicitely specified then the
last connection SET or CONNECTed to for the current thread is used,
rather than just the "last connection".
But yeah, specifying the connection by variable (be it string or
connection ptr) would be a definite step forward. Currently you cannot
write a generic function like:
int getit(char *using_connection)
{
EXEC SQL BEGIN DECLARE SECTION;
char *s_connection = using_connection;
int s_it;
EXEC SQL END DECLARE SECTION;
EXEC SQL AT :s_connection SELECT it INTO :s_it FROM some_table;
return( s_it );
}
which could be run concurrently by multiple threads.
L.
Shridhar Daithankar writes:
Show quoted text
On Friday 27 February 2004 20:54, Michael Meskes wrote:
On Fri, Feb 27, 2004 at 04:22:33PM +0530, Shridhar Daithankar wrote:
How about, allowing 'connection *'? If somebody puts a 'connection *'
there it is used. If it is a string a name search is performed. Best of
both worlds.How shall anyone put a pointer to a connection struct inside the SQL
statement?It would help me a lot if you'd be able to give some examples.
EXEC SQL BEGIN DECLARE SECTION;
connect *connectionPtr;
EXEC SQL END DECLARE SECTION;EXEC SQL CONNECT TO db AS connectionPtr;
EXEC SQL AT connectionPtr SELECT 1;After all, it is matter of parsing some code and emitting equivalent C code,
isn't it?Shridhar
On Friday 27 February 2004 22:24, Lee Kindness wrote:
Sort of related, I was thinking about adding some more thread-related
code such that if a connection wasn't explicitely specified then the
last connection SET or CONNECTed to for the current thread is used,
rather than just the "last connection".But yeah, specifying the connection by variable (be it string or
connection ptr) would be a definite step forward. Currently you cannot
write a generic function like:int getit(char *using_connection)
{
EXEC SQL BEGIN DECLARE SECTION;
char *s_connection = using_connection;
int s_it;
EXEC SQL END DECLARE SECTION;EXEC SQL AT :s_connection SELECT it INTO :s_it FROM some_table;
return( s_it );
}which could be run concurrently by multiple threads.
Consider another scenario. In a C++ class you want to contain a database
connection. The class needs to make n resources thread safe, including
database connection. Now each instance of class would be referring to
differnet database connection with same code.
Doing same with char strings, is just clean enough IMHO..
Shridhar
Should I add this to the TODO list?
---------------------------------------------------------------------------
Lee Kindness wrote:
Sort of related, I was thinking about adding some more thread-related
code such that if a connection wasn't explicitely specified then the
last connection SET or CONNECTed to for the current thread is used,
rather than just the "last connection".But yeah, specifying the connection by variable (be it string or
connection ptr) would be a definite step forward. Currently you cannot
write a generic function like:int getit(char *using_connection)
{
EXEC SQL BEGIN DECLARE SECTION;
char *s_connection = using_connection;
int s_it;
EXEC SQL END DECLARE SECTION;EXEC SQL AT :s_connection SELECT it INTO :s_it FROM some_table;
return( s_it );
}which could be run concurrently by multiple threads.
L.
Shridhar Daithankar writes:
On Friday 27 February 2004 20:54, Michael Meskes wrote:
On Fri, Feb 27, 2004 at 04:22:33PM +0530, Shridhar Daithankar wrote:
How about, allowing 'connection *'? If somebody puts a 'connection *'
there it is used. If it is a string a name search is performed. Best of
both worlds.How shall anyone put a pointer to a connection struct inside the SQL
statement?It would help me a lot if you'd be able to give some examples.
EXEC SQL BEGIN DECLARE SECTION;
connect *connectionPtr;
EXEC SQL END DECLARE SECTION;EXEC SQL CONNECT TO db AS connectionPtr;
EXEC SQL AT connectionPtr SELECT 1;After all, it is matter of parsing some code and emitting equivalent C code,
isn't it?Shridhar
---------------------------(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, Pennsylvania 19073Oh.. By all means..Please do..
The reason I posted it because I didn't wanted to work on it if core is not
going to accept it on account of non-compliance with spec.
Is this fine?
* Allow a 'connection *' pointer to be specified instead of a string to denote
a connection.
I plan to work on it whenever possible. What I would like to do is eliminate the
locks around name->connection mapping as we would be directly using the
connection instead of a name.
I think we can also add the SQL-CA to connection structure so that each
connection gets it's own SQL-CA. That way ECPG is as thread-safe as the calling
application gets.
And on the plus side we don't have to worry about platform specific threading
models either.
Thoughts?
Shridhar
Bruce Momjian wrote:
Show quoted text
Should I add this to the TODO list?
---------------------------------------------------------------------------
Lee Kindness wrote:
Sort of related, I was thinking about adding some more thread-related
code such that if a connection wasn't explicitely specified then the
last connection SET or CONNECTed to for the current thread is used,
rather than just the "last connection".But yeah, specifying the connection by variable (be it string or
connection ptr) would be a definite step forward. Currently you cannot
write a generic function like:int getit(char *using_connection)
{
EXEC SQL BEGIN DECLARE SECTION;
char *s_connection = using_connection;
int s_it;
EXEC SQL END DECLARE SECTION;EXEC SQL AT :s_connection SELECT it INTO :s_it FROM some_table;
return( s_it );
}which could be run concurrently by multiple threads.
L.
Shridhar Daithankar writes:
On Friday 27 February 2004 20:54, Michael Meskes wrote:
On Fri, Feb 27, 2004 at 04:22:33PM +0530, Shridhar Daithankar wrote:
How about, allowing 'connection *'? If somebody puts a 'connection *'
there it is used. If it is a string a name search is performed. Best of
both worlds.How shall anyone put a pointer to a connection struct inside the SQL
statement?It would help me a lot if you'd be able to give some examples.
EXEC SQL BEGIN DECLARE SECTION;
connect *connectionPtr;
EXEC SQL END DECLARE SECTION;EXEC SQL CONNECT TO db AS connectionPtr;
EXEC SQL AT connectionPtr SELECT 1;After all, it is matter of parsing some code and emitting equivalent C code,
isn't it?Shridhar
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Shridhar Daithankar <shridhar@frodo.hserus.net> writes:
The reason I posted it because I didn't wanted to work on it if core is not
going to accept it on account of non-compliance with spec.
When it comes to ecpg, Michael Meskes is the man you have to convince,
not any of the core committee.
regards, tom lane
Tom Lane wrote:
Shridhar Daithankar <shridhar@frodo.hserus.net> writes:
The reason I posted it because I didn't wanted to work on it if core is not
going to accept it on account of non-compliance with spec.When it comes to ecpg, Michael Meskes is the man you have to convince,
not any of the core committee.
Michael doesn't own ecpg any more than the core owns the backend code.
I would get an opinion from Michael and see what others think of the
idea. You can always ask for a vote too.
--
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, Pennsylvania 19073Shridhar, want to discuss this off list a bit to work through the various
options and then revent back to the list with a suitable to-do (for
discussion)?
L.
----- Original Message -----
From: "Shridhar Daithankar" <shridhar@frodo.hserus.net>
To: "Bruce Momjian" <pgman@candle.pha.pa.us>
Cc: "PostgreSQL-development" <pgsql-hackers@postgresql.org>
Sent: Wednesday, March 03, 2004 2:10 PM
Subject: Re: Thread safe connection-name mapping in ECPG. Is it
Oh.. By all means..Please do..
The reason I posted it because I didn't wanted to work on it if core is
not
going to accept it on account of non-compliance with spec.
Is this fine?
* Allow a 'connection *' pointer to be specified instead of a string to
denote
a connection.
I plan to work on it whenever possible. What I would like to do is
eliminate the
locks around name->connection mapping as we would be directly using the
connection instead of a name.I think we can also add the SQL-CA to connection structure so that each
connection gets it's own SQL-CA. That way ECPG is as thread-safe as the
calling
application gets.
And on the plus side we don't have to worry about platform specific
threading
models either.
Thoughts?
Shridhar
Bruce Momjian wrote:
Should I add this to the TODO list?
--------------------------------------------------------------------------
-
Lee Kindness wrote:
Sort of related, I was thinking about adding some more thread-related
code such that if a connection wasn't explicitely specified then the
last connection SET or CONNECTed to for the current thread is used,
rather than just the "last connection".But yeah, specifying the connection by variable (be it string or
connection ptr) would be a definite step forward. Currently you cannot
write a generic function like:int getit(char *using_connection)
{
EXEC SQL BEGIN DECLARE SECTION;
char *s_connection = using_connection;
int s_it;
EXEC SQL END DECLARE SECTION;EXEC SQL AT :s_connection SELECT it INTO :s_it FROM some_table;
return( s_it );
}which could be run concurrently by multiple threads.
L.
Shridhar Daithankar writes:
On Friday 27 February 2004 20:54, Michael Meskes wrote:
On Fri, Feb 27, 2004 at 04:22:33PM +0530, Shridhar Daithankar
wrote:
How about, allowing 'connection *'? If somebody puts a
'connection *'
there it is used. If it is a string a name search is performed.
Best of
both worlds.
How shall anyone put a pointer to a connection struct inside the
SQL
statement?
It would help me a lot if you'd be able to give some examples.
EXEC SQL BEGIN DECLARE SECTION;
connect *connectionPtr;
EXEC SQL END DECLARE SECTION;EXEC SQL CONNECT TO db AS connectionPtr;
EXEC SQL AT connectionPtr SELECT 1;After all, it is matter of parsing some code and emitting equivalent
C code,
Show quoted text
isn't it?
Shridhar
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Lee Kindness wrote:
Shridhar, want to discuss this off list a bit to work through the various
options and then revent back to the list with a suitable to-do (for
discussion)?
I don't mind. Just for summary, I am listing the discussion/proposal so far on
this issue..
- Dispose names of connectiong and replace them with a pointer.
- Make SQL CA a member of connection structure rather than a thread local variable.
- Update man pages and documentation
- Update examples.
Drop me a mail offline to take this further.
Shridhar
- Dispose names of connectiong and replace them with a pointer.
You cannot dispose the names, you can only add something to also allow pointers.
The names are in the ESQL/C standard.
Andreas
Import Notes
Resolved by subject fallback
Zeugswetter Andreas SB SD wrote:
- Dispose names of connectiong and replace them with a pointer.
You cannot dispose the names, you can only add something to also allow pointers.
The names are in the ESQL/C standard.
Can you point me to the standards text? I am googling for it but nothing
resembling an independent standard has emerged as yet..
Shridhar
Shridhar Daithankar wrote:
Zeugswetter Andreas SB SD wrote:
- Dispose names of connectiong and replace them with a pointer.
You cannot dispose the names, you can only add something to also allow pointers.
The names are in the ESQL/C standard.Can you point me to the standards text? I am googling for it but nothing
resembling an independent standard has emerged as yet..
One of the links on the developers FAQ must point to it.
--
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, Pennsylvania 19073On Wed, Mar 03, 2004 at 08:47:50AM -0500, Bruce Momjian wrote:
But yeah, specifying the connection by variable (be it string or
connection ptr) would be a definite step forward. Currently you cannot
write a generic function like:int getit(char *using_connection)
{
EXEC SQL BEGIN DECLARE SECTION;
char *s_connection = using_connection;
int s_it;
EXEC SQL END DECLARE SECTION;EXEC SQL AT :s_connection SELECT it INTO :s_it FROM some_table;
return( s_it );
}which could be run concurrently by multiple threads.
Why? What doesn't work? AFAIRC the AT statement does indeed allow a
variable as connection_target.
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!
On Wed, Mar 03, 2004 at 07:40:40PM +0530, Shridhar Daithankar wrote:
Is this fine?
* Allow a 'connection *' pointer to be specified instead of a string to
denote a connection.
...
I personally have no problem with this as long as it does not break
compatibility to the code we allow now.
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!
From: "Michael Meskes" <meskes@postgresql.org>
Why? What doesn't work? AFAIRC the AT statement does indeed allow a
variable as connection_target.
Yeah, I was wrong there. I updated the thread test program in ecpg/test to
make use of this functionality - see patch in pgsql-patches yesterday.
L.
On Sunday 07 March 2004 20:28, Michael Meskes wrote:
On Wed, Mar 03, 2004 at 07:40:40PM +0530, Shridhar Daithankar wrote:
Is this fine?
* Allow a 'connection *' pointer to be specified instead of a string to
denote a connection.
...I personally have no problem with this as long as it does not break
compatibility to the code we allow now.
I searched thr. SQL92 standard over weekend(sunday and monday.. had a working
saturday..:-)) And need to correct some of the assumptions I stated
previously.
In ECPG we can not dispose connection names as strings because standard
expects it. Hence if we need to provide a connection pointer to denote a
connection, that would be a postgresql only extension and such should be
documented and warned for potential portability problem.
With responses so far, I believe it is OK for me to go ahead and actually try
some coding now..:-)
Will keep things posted.
Shridhar