JDBCDataStore::execRetrieveQuery failed with SQLState: 55000 errors

I am trying to delete a bunch of orphan objects from the database (PostgreSQL 8.3) by retrieving a list of them, then running an iterator in which I check whether or not they have a relation with another object, if not I delete them. However after about 30 secs of running (there are a little over 250000 objects in the table of which about half are orphans) I get the following error: CONNECTIONBUS_RETRIEVE: JDBCDataStore::execRetrieveQuery failed with SQLState: 55000 Message: This statement has been closed. Which keeps repeating until the XAS crashes with the following error: ERROR - EXTERNALINTERFACE: java.lang.OutOfMemoryError: unable to create new native thread What could be the cause of these errors?
3 answers

I wouldn't advise iterating through so many objects at a time. Try and split the iteration into smaller batches, you can see this thread for an example.


Can't this batch idea be implemented in Mendix so it will do this automaticaly with a lot of objects? This way the user doesn't have any problems with dealing with a lot of objects and he doesn't have to implement a work around to make it work.


An java.lang.OutOfMemoryError is a 'normal' heap space error which means that you are trying to load to many objects in your memory. The only solution for this problem is handling those objects in batches.

One important note when you are using batches, make sure that you always sort the batch on a (preferably unique) column. I recently found out that if you won't sort on a unique column the database can return the objects at random which means that the XAS will return some objects twice and some objects not at all.

And my reaction to your comment, an iterator won't help you solving big memory issues. Using an iterator could result in some better performance because Java can optimize the handling of your list. However if you retrieve 250000 objects an iterator won't solve your problem because an iterator is basically just a way to access the objects in your list easily but it still requires to load all objects in your memory.
It would be a great feature if the XAS provides an iterator which can retrieve those objects in batches so you won't have to build some strange constructions. Let's hope R&D can build something like this in 2.5 or 3.0