Re: Thread safe connection-name mapping in ECPG. Is it

> 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

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(at)jabber(dot)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:
> 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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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,
>> > isn't it?
>> >
>> > Shridhar
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 4: Don't 'kill -9' the postmaster
>>
>
>

Shridhar Daithankar <shridhar(at)frodo(dot)hserus(dot)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(at)frodo(dot)hserus(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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)?

L.

----- Original Message -----
From: "Shridhar Daithankar" <shridhar(at)frodo(dot)hserus(dot)net>
To: "Bruce Momjian" <pgman(at)candle(dot)pha(dot)pa(dot)us>
Cc: "PostgreSQL-development" <pgsql-hackers(at)postgresql(dot)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,
> >> > 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

On 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(at)jabber(dot)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(at)jabber(dot)org
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

From: "Michael Meskes" <meskes(at)postgresql(dot)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