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
multiplethreads.
In this application, database operations from each threads are serialized and do not operate on the connection at the
sametime.
However, reconnecting (DISCONNECT and CONNECT) in one thread causes SQL execution in the other thread to fail with
followingmessage.
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
openedone.
> If your application uses multiple threads of execution, they cannot share a connection concurrently. You must either
explicitlycontrol 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
"theconnection 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
SETCONNECTION connection-name;) before step 5,the SQL of step 5 could be executed.
Do we need to create applications this way?
And, is there limitation that we can't CONNECT or DISCONNECT the DEFAULT connection inside a thread in multithreaded
application?
[*] https://www.postgresql.org/docs/12/ecpg-connect.html#ECPG-SET-CONNECTION
Regards,
Yusuke Egashira