Grails: select returns (randomly) null when a row exists in PostgreSQL with connection pooling enabled

Question

We are developing a Grails 2.0 application that used to run smoothly on MySQL.

We asked our administrators to switch to PostgreSQL, which they prefer

We have added many new features to the application, including one that causes us a problem: an asynchronous third-party web service request

So, we have a created domain object, let it ask a question. When closing afterInsert , a Resource is created to subsequently save the result of the call to the external web service.

 class Question implements Serializable { static hasMany = [resources: Resource] static constraints = { resources(nullable: true) } def afterInsert() { Resource.withNewSession { Resource txt = Resource.create(null) this.addToResources(txt) } } Resource retrieveResource(){ return this.resources.find{ it instanceof Resource } } static Question create(Map params) throws SaveDomainException { //question creation } } 

We create the question as follows:

 //first we create question and save it def question = Question.create(params) question.save(flush:true, insert:true) getThirdPartyService().doCallAsync((int)req.retrieveResource().id) 

and ThirdPartyService as the doCallAsync method that spawns the ExecutorService (obtained through the grails executor plugin, so this is not a dangerous "Hibernate Session-Thread" problem) which executes a simple Resource.get(res_id) with the identifier obtained as shown above.

The problem is that with PostgreSQL and pooled = true in DataSource.groovy , get sometimes returns a null, sometinmes resource object.

We tested three different queries: a get(id) , a findById(id) and executeQuery with highlighted.

The stranger thing is that with the three lines above in the same method, we sometimes have different results. Only one out of three returns null, or three returns null, or none (which is the expected behavior, I remember)

We turned on PostgreSQL query logs to find out if this is a Hibernate caching problem, but all three queries are displayed in the logs, so hibernate occurs every time the database is reset. We see a resource insert with the correct identifier, followed by a commit, followed by three choices (which are provided with the correct resource identifier)

Does anyone have a hint that we are going to check further to find out where this error came from? (we tried to change the connection pool, no luck)

Last, if we add Thread.sleep(1000) (which is gory, but for testing purposes only ;-)) before the requests, everything runs smoothly. So this is apparently a visibility problem between postgres processes, but we have no idea how to solve this problem.

Answer 1

You took the time to study this issue and write it down as a well-presented question, but leave important journals?

I assume that you have overlapping transactions between your persistence and async get. Either the save was not committed, or the recipient has a consistent snapshot before saving. Make sure the protocol includes a process identifier or some other way to distinguish between connections and just look in what order the statements are.

---

Edit: Looks like transaction snapshot timings.

In PostgreSQL, all statements are in a transaction (possibly implicit and long only one statement).

The default mode is “Read Committed,” which means you can see the commit that occurs during the transaction.

There is also a “serializable” option, which means that you (mostly) see a frozen snapshot of your database at the start of the transaction.

See documents for more details.

Open two psql consoles and try some fixing options in one and select in the other with different isolation levels. You must be able to see what is happening with your living system.