GeoNetwork must use database cursors efficiently

[simonp]

I encountered a problem with the current development trunk of GeoNetwork during a batch import of metadata records. Less than 60 records were imported. Then, the import of the rest fails due an "ORA-0100: Maximum open cursors exceeded" error of the database. The value for the maximum of open cursors was set to 300.

After exploring the problem I found out that the reason for this are non-parameterized SQL statements. Most SQL statements in the program code are parameterized, i.e. instead of writing a program snippet like

String query = "DELETE FROM metadata WHERE id=" + id; dbms.execute(query);

the code snippet

String query = "DELETE FROM metadata WHERE id=?"; dbms.execute(query, Integer.parseInt(id));

is used. However, there still exists "bad" program code which results in the use of a new cursor for each instance of the SQL statement. The problem can be fixed by the exchange of all "badly" implemented SQL statements by parameterized SQL statements.

Kind regards, Jürgen

---

Dr. Jürgen Seib Deutscher Wetterdienst TI1/K - IT-Koordination Frankfurter Strasse 135 63067 Offenbach Tel.: +49(69)8062-2609 EMail: juergen.seib@…

[simonp]

DataManager is probably the hotspot for this fix - looking at this now as its important we make efficient use of database resources

[mcr]

I can confirm this behavior. We did some stress tests and the performance was not as expected (INSPIRE requirements). We are running on an IBM P780 with 16x4.1 GHz and a lot of memory. The database monitor (DB2) shows a mixture of many dynamic and static SQL statements.

I can offer to retest after this issue is fixed.

Christian

[simonp]

Working through this using pg_stat_statements (shows sql statement stats for postgres). DataManager, Batch upload, export and privilege setting all seem ok now but more to come.

Retesting when finished would be great Christian - thanks.

[simonp]

More changes in recent commits - now typically seeing less than 100 statements being used and (almost all being reused) in postgis using pg_stat_statements (should be the same for all dbs) - still some code to check

[simonp]

Fixed in various commits over the last week or so.

Please test and reopen if the problem re-occurs. Thanks!