[CORE-228] Exception when running parallel queries using Postgres Created: 26/Sep/16  Updated: 29/Jun/17  Resolved: 15/Jun/17

Status: Closed
Project: i2b2 Core Software
Component/s: CRC Cell
Affects Version/s: 1.7.06, 1.7.07
Fix Version/s: 1.7.09

Type: Bug Priority: Major
Reporter: Janice Donahoe Assignee: Janice Donahoe
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: PDF File EMAIL_Parallel_Queries_Error.pdf    
Rank: 0|i0031r:
i2b2 Feature/s:
Queries
i2b2 Sponsored Project/s:
i2b2 Core
Affects Database/s:
PostgreSQL
Participant/s:

 Description   
This issue was reported by Robert Lodahl from the University Medical Center Gottingen.

When running multiple parallel queries requesting a PDO, the CRC will return an error. It appears, for all parallel queries run by the custom plugin, the same connection is used.

Upon further investigation, Robert has located the problem to the local temporary tables (like #temp_fact_param_table in this case) are only local for every connection, not per statement. So the first query starts, creating the table, wanting to execute something using this table, while the second query starts and drops the table (marked in bold). Obviously an exception occurs because the table no longer exists.

The ERROR message that appears is:
 
"Caused by: org.postgresql.util.PSQLException: ERROR: relation "temp_fact_param_table" does not exist“
 

The exception can be traced back to the part of the code:

edu.harvard.i2b2.crc.dao.pdo.PdoQueryConceptDao.getConceptByFact(PdoQueryConceptDao.java:346)
 

The section of code that appears to be causing the problem can be seen in the attached document.

ATTACHMENTS:

EMAIL_Parallel_Queries_Error.pdf = original email from Robert.


 Comments   
Comment by Robert Lodahl [ 14/Nov/16 ]
Hello.
After further investigation I am quite sure that I found and resolved the issue.

The problem is: i2b2 creates it's temporary tables using this update statement:
"create table #temp_fact_param_table ( set_index int, char_param1 varchar(500) )"

i2b2 handles both sql-server and postgres in the same way. However they work quite different. While SqlServer creates local temporary tables using the "#"-sign in front of the table name, Postgres needs another update statement, explicitly naming that it is a temporary table to be created, see:

"create TEMPORARY table #temp_fact_param_table ( set_index int, char_param1 varchar(500) )"

This has resolved the issue for me, however, fixes in several files were needed.
Comment by Janice Donahoe [ 15/Jun/17 ]
This issue has been fixed and will be included in release 1.7.09.
Generated at Mon Mar 30 06:28:56 UTC 2020 using JIRA 7.6.3#76005-sha1:8a4e38d34af948780dbf52044e7aafb13a7cae58.