Following this question postgresql thread safety for temporary tables my case is slightly different, what I experience is that creating uniquely named temp tables seem to be unsafe, I simply have a test that runs few times, and fails when I witness that creation of the temp table of the second thread is probably interfered by the first thread.

The code is simple, here’s a pseudo code that represents it:

@Transactional(propagation = Propagation.REQUIRES_NEW,
            rollbackFor = {SQLException.class})
public void doCalculation(String id) {
       
     // sql: create temp table orders_temp_x as select ? from 'orders' where customer_id = ?
     createUniqueTempTableById(id);


     // sql: insert into 'orders_temp_x' select ? from ? order by %s
     fillUniqeTempTable(); <<<<<--- Thread 2 fails to find the temp table here 

}

What I experience debugging this is that when a second thread tries to insert rows into its unique temp table which was supposed to be created in the first method it fails to find the temp table with the following exception:
Caused by: org.postgresql.util.PSQLException: ERROR: relation “orders_temp_T2” does not exist

What I tried is to synchronize the doCalculation() method and everything worked as expected, but of course that defeats the purpose of it being used in parallel.

I’m using spring-jdbc with my connection using a Hikari pool with 10 max connections.

I also added the following code to make sure I’m getting a different connection on each thread:

try (Connection conn = getDataSource().getConnection()) {
            log.info("{}: Connection instance: {}", Thread.currentThread().getName(), System.identityHashCode(conn));
} catch (SQLException se) {
     log.error("Failed to get connection", se);
}

What am I missing here?