We ’re in the process of upgrading OpenClinica 3.1.4 (Oracle ) to OpenClinica 3.9 (PostgreSQL)
To test the upgrade we used a dump of our Oracle production database.
After upgrade, tests were performed .
Following issue came up. If in the menu, the ‘Notes & Discrepancies’ menu item is clicked, OPenclinica hangs after a while. (The ‘test’ production database has approx 3000 subjects ,450 users, 9000 discrepancy notes.)
At the server where the postgres database is installed, our free disk space of 8G went down to 0G , just by retrieving the discrepancy notes: ( SQL statement found in the PostgreSQL server status window )
SELECT COUNT(DISTINCT (dn.*)) from discrepancy_note dn,study s , user_account ua , study_subject ss
where dn.study_id = s.study_id
and ( s.study_id =$1 or s.parent_study_id =$2)
and dn.parent_dn_id is null
In the .. ..\PostgreSQL\8.4\data\base\pgsql_tmp directory, temporary files are created. They consumes all free space.
if SELECT COUNT(DISTINCT (dn.*))… is replaced by SELECT * …
Free space stays 8G but we get an ‘out of memory’ error.
One observation: Are the tables user_account and study_subject necessary in this query?
I reproduced the problem with a clean install of OpenClinica 3.9 with 5000 subjects, 7 users, 25 discrepancies. This setup consumes 150MB space.
Has this something to do with the query or is the PostgreSQL database not setup properly?