Is ECPG's SET CONNECTION really not thread-aware?

Started by Tsunakawa, Takayukiover 8 years ago6 messages

Tsunakawa, Takayuki

tsunakawa.takay@jp.fujitsu.com

over 8 years ago

Hello,

The following page says:

https://www.postgresql.org/docs/devel/static/ecpg-connect.html#ecpg-set-connection

--------------------------------------------------
EXEC SQL AT connection-name SELECT ...;

If your application uses multiple threads of execution, they cannot share a connection concurrently. You must either explicitly control access to the connection (using mutexes) or use a connection for each thread. If each thread uses its own connection, you will need to use the AT clause to specify which connection the thread will use.

EXEC SQL SET CONNECTION connection-name;

...It is not thread-aware.
--------------------------------------------------

What does this mean by "not thread-aware?" Does SET CONNECTION in one thread change the current connection in another thread?
It doesn't look so, because the connection management and SQL execution in ECPG uses thread-specific data (TSD) to get and set the current connection, like this:

bool
ECPGsetconn(int lineno, const char *connection_name)
{
struct connection *con = ecpg_get_connection(connection_name);

if (!ecpg_init(con, connection_name, lineno))
return (false);

#ifdef ENABLE_THREAD_SAFETY
pthread_setspecific(actual_connection_key, con);
#else
actual_connection = con;
#endif
return true;
}

Regards
Takayuki Tsunakawa

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Tsunakawa, Takayuki

tsunakawa.takay@jp.fujitsu.com

over 8 years ago

In reply to: Tsunakawa, Takayuki (#1)

1 attachment(s)

Re: Is ECPG's SET CONNECTION really not thread-aware?

From: pgsql-hackers-owner@postgresql.org

[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Tsunakawa,
Takayuki
The following page says:

https://www.postgresql.org/docs/devel/static/ecpg-connect.html#ecpg-se
t-connection

--------------------------------------------------
EXEC SQL AT connection-name SELECT ...;

If your application uses multiple threads of execution, they cannot share
a connection concurrently. You must either explicitly control access to
the connection (using mutexes) or use a connection for each thread. If each
thread uses its own connection, you will need to use the AT clause to specify
which connection the thread will use.

EXEC SQL SET CONNECTION connection-name;

...It is not thread-aware.
--------------------------------------------------

What does this mean by "not thread-aware?" Does SET CONNECTION in one
thread change the current connection in another thread?
It doesn't look so, because the connection management and SQL execution
in ECPG uses thread-specific data (TSD) to get and set the current connection,
like this:

The ECPG code sets and gets the current connection to/from the TSD, so SET CONNECTION is thread-aware. I could confirm that like this:

threadA: CONNECT AS conA
threadB: CONNECT AS conB
threadA: CONNECT AS conC
threadA & threadB: SELECT pg_backend_pid() ... each thread displays different pids
threadA: SET CONNECTION conA
threadA & threadB: SELECT pg_backend_pid() ... each thread displays different pids, with thread outputting the same pid as before

So the doc seems to need fix. The patch is attached.

Regards
Takayuki Tsunakawa

Attachments:

ecpg_setconn.patchapplication/octet-stream; name=ecpg_setconn.patchDownload

diff --git a/doc/src/sgml/ecpg.sgml b/doc/src/sgml/ecpg.sgml
index dead4c3..f13a0e9 100644
--- a/doc/src/sgml/ecpg.sgml
+++ b/doc/src/sgml/ecpg.sgml
@@ -240,8 +240,7 @@ EXEC SQL AT <replaceable>connection-name</replaceable> SELECT ...;
   <para>
    If your application uses multiple threads of execution, they cannot share a
    connection concurrently. You must either explicitly control access to the connection
-   (using mutexes) or use a connection for each thread. If each thread uses its own connection,
-   you will need to use the AT clause to specify which connection the thread will use.
+   (using mutexes) or use a connection for each thread.
   </para>
 
   <para>
@@ -251,7 +250,7 @@ EXEC SQL AT <replaceable>connection-name</replaceable> SELECT ...;
 EXEC SQL SET CONNECTION <replaceable>connection-name</replaceable>;
 </programlisting>
    This option is particularly convenient if many statements are to be
-   executed on the same connection.  It is not thread-aware.
+   executed on the same connection.
   </para>
 
   <para>

meskes@postgresql.org

over 8 years ago

In reply to: Tsunakawa, Takayuki (#2)

Re: Is ECPG's SET CONNECTION really not thread-aware?

Dear Tsunakawa-san,

sorry for the late reply, I've been traveling all of last week and only
just came back.

What does this mean by "not thread-aware?" Does SET CONNECTION in one
thread change the current connection in another thread?
It doesn't look so, because the connection management and SQL execution
in ECPG uses thread-specific data (TSD) to get and set the current connection,
like this:

I'm pretty sure it is indeed thread-aware, although I didn't provide the
code for this feature myself.

So the doc seems to need fix. The patch is attached.

Thanks, applied.

Michael

--
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Meskes at (Debian|Postgresql) dot Org
Jabber: michael at xmpp dot meskes dot org
VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Tsunakawa, Takayuki

tsunakawa.takay@jp.fujitsu.com

over 8 years ago

In reply to: Michael Meskes (#3)

Re: Is ECPG's SET CONNECTION really not thread-aware?

From: pgsql-hackers-owner@postgresql.org

[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Michael Meskes
I'm pretty sure it is indeed thread-aware, although I didn't provide the
code for this feature myself.

So the doc seems to need fix. The patch is attached.

Thanks, applied.

Thank you, I'm relieved.

Could you also apply it to past versions if you don't mind? The oldest supported version 9.2 is already thread-aware.

Regards
Takayuki Tsunakawa

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

meskes@postgresql.org

over 8 years ago

In reply to: Tsunakawa, Takayuki (#4)

Re: Is ECPG's SET CONNECTION really not thread-aware?

On Wed, Jun 07, 2017 at 03:45:23AM +0000, Tsunakawa, Takayuki wrote:

Could you also apply it to past versions if you don't mind? The oldest supported version 9.2 is already thread-aware.

Done.

My standard workflow is to wait a couple days to see if everything works nicely before backporting. Obviously this doesn't make any sense for a doc patch, sigh.

Michael
--
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Meskes at (Debian|Postgresql) dot Org
Jabber: michael at xmpp dot meskes dot org
VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Tsunakawa, Takayuki

tsunakawa.takay@jp.fujitsu.com

over 8 years ago

In reply to: Michael Meskes (#5)

Re: Is ECPG's SET CONNECTION really not thread-aware?

Dear Meskes,

From: pgsql-hackers-owner@postgresql.org

[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Michael Meskes
Done.

Thanks, I confirmed the commit messages.

My standard workflow is to wait a couple days to see if everything works
nicely before backporting. Obviously this doesn't make any sense for a doc
patch, sigh.

I see. That's a good idea.

Regards
Takayuki Tsunakawa

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers