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

From: "egashira(dot)yusuke(at)fujitsu(dot)com" <egashira(dot)yusuke(at)fujitsu(dot)com>
To: "'pgsql-bugs(at)lists(dot)postgresql(dot)org'" <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Reconnect a single connection used by multiple threads in embedded SQL in C application causes error.
Date: 2022-02-21 12:09:46
Message-ID: TYWPR01MB7202E14319002430B83F7ABBFF3A9@TYWPR01MB7202.jpnprd01.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Attachment Content-Type Size
app.pgc application/octet-stream 5.2 KB

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Victor Yegorov 2022-02-21 17:57:00 Re: BUG #17406: Segmentation fault on GiST index after 14.2 upgrade
Previous Message PG Bug reporting form 2022-02-21 08:26:28 BUG #17412: CREATE INDEX on a partitioned table doesn't recognize existing partial indexes on partitions