Re: Reconnect a single connection used by multiple threads in embedded SQL in C application causes error.

On Mon, Feb 21, 2022 at 12:09:46PM +0000, egashira(dot)yusuke(at)fujitsu(dot)com wrote:
> There seems to be something wrong with using a single connection in multiple threads in embedded SQL in C.
>
> Our customer is writing an embedded SQL in C application in Postgresql 12, which handles a single connection from multiple threads.
> In this application, database operations from each threads are serialized and do not operate on the connection at the same time.
> However, reconnecting (DISCONNECT and CONNECT) in one thread causes SQL execution in the other thread to fail with following message.
>
> the connection to the server was lost
>
> An overview of the application process is provided below. Attached is a simple reproduction application.
> I checked this with PostgreSQL 12.9.
>
> 1. Thread#1 EXEC SQL CONNECT;
> 2. Thread#2 EXEC SQL PREPARE / EXECUTE;
> 3. Thread#2 EXEC SQL DISCONNECT;
> 4. Thread#2 EXEC SQL CONNECT;
> 5. Thread#1 EXEC SQL PREPARE / EXECUTE;
> 6. Thread#1 EXEC SQL DISCONNECT;
> -> In step 5, Thread#1 takes above error.
>
> Document "35.2.2. Choosing a Connection" (*) says the following notes on choosing a connection and multithreading.
>
> > SQL statements in embedded SQL programs are by default executed on the current connection, that is, the most recently opened one.
>
> > 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.
>
> The reproduction application conforms to these notes.
> Each thread is using a recently opened connection, and they are not using a connection at the same time.
> However, the SQL execution in step 5 (Thread#1) does not use the connection opened in step 4 (Thread#2) and returns "the connection to the server was lost".
> # And the DISCONNECT in step 6(Thread#1) causes application crash by double free.
> # *** Error in `./app': double free or corruption (fasttop): 0x00007f12bc009780 ***
>
> When adding the connection-name to each CONNECT and executing a statement to switch the current connection (EXEC SQL SET CONNECTION connection-name;) before step 5,the SQL of step 5 could be executed.
> Do we need to create applications this way?

For now, yes.

> And, is there limitation that we can't CONNECT or DISCONNECT the DEFAULT connection inside a thread in multithreaded application?

For now, yes. This is a bug. The documentation you quoted is out of date
with respect to the code. That documentation comes from commit 8f61184
(2003-12). The behavior of connections shared across threads changed in
commit 757fb0e (2004-03). Since that change, there's a per-thread record of
the default connection, in addition to a global notion of the default
connection. I suspect a few use cases work well today:

- One thread does all the CONNECT and DISCONNECT commands. Other threads just
use the default connection, with mutual exclusion.
- Each thread does its own CONNECT, DISCONNECT, and other commands. Each
thread has its own default connection. No sharing at all.

Your example does the CONNECT to establish the default connection in one
thread, and it does the DISCONNECT of the default connection in another
thread. That's buggy today.

Another behavior that may qualify as a bug: ECPGsetconn() updates only the
thread-specific connection, while ECPGconnect() updates both the
thread-specific and global connections.