Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Memory issue with PostgreSQL
Memory issue with PostgreSQL [message #1735571] Tue, 21 June 2016 09:34
Luca Faustin is currently offline Luca FaustinFriend
Messages: 1
Registered: June 2016
Junior Member
Hi,

we recently had an OutOfMemory error while retrieving a large amount of records from a PostgreSQL database.
We are using Cursor to retrieving the records but it seems that the driver is using cursor only if some conditions are true.

After some investigations we adopted this solution:
- setting query property "eclipselink.cursor" to use a cursor
- setting query property "eclipselink.read-only" to avoid registering object in the persistence context
- setting query property "eclipselink.jdbc.fetch-size", as needed by the PostgreSQL driver to use forward-fetch-capable cursor
- forcing a beginTransaction/rollbackTransaction on DatabaseSession, to use a connection without auto-commit (as needed by PostgreSQL driver to use cursor)
- calling method resultCursor.clear() in order to preserve memory

This is an example of our code:
        ...
	EntityManager em = emP.get();

	final CriteriaBuilder cB = em.getCriteriaBuilder();
	CriteriaQuery<Opportunita> q = cB.createQuery(Opportunita.class);
	final Root<Opportunita> opportunita = q.from(Opportunita.class);
	CriteriaQuery<Opportunita> select = q.select(opportunita);

	JpaQuery<Opportunita> query = (JpaQuery<Opportunita>) em.createQuery(select)
		.setHint(QueryHints.READ_ONLY, HintValues.TRUE)
		.setHint("eclipselink.cursor", "TRUE")
		.setHint("eclipselink.jdbc.fetch-size", 10000)
		;

	DatabaseSession ds = em.unwrap(DatabaseSession.class);
	ds.beginTransaction();
	
	Cursor resultCursor = query.getResultCursor();
	int i = 0;
	try {
	    while (resultCursor.hasNext()) {
		Opportunita o = (Opportunita) resultCursor.next();
		i++;
		if (i % 1000 == 0)
		    System.out.print('.');
		if (i % 70000 == 0)
		    System.out.println();
		resultCursor.clear();
	    }
	} finally {
	    resultCursor.close();
	}
	
	ds.rollbackTransaction();
       ....


My question is whether there is a better/cleaner way to retrieve a large amount of data from a PostgreSQL database without having OOM errors.

Thank you.
ciao, Luca
Previous Topic:eclipselink batch write is disabled when use history policy or DescriptorEventAdapter
Next Topic:cascade.PERSIST on Map<Entity, Entity>
Goto Forum:
  


Current Time: Wed Dec 13 17:00:42 GMT 2017

Powered by FUDForum. Page generated in 0.02483 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software