Search code examples
hibernatepostgresqlgrailsgroovygrails-orm

Grails 2.4.4 completely ignores fetch:'join' *when using PostgreSQL*

Update: this seems to only happen when using PostgreSQL. I've committed a project here: https://github.com/jbwiv/testfetch with two branches. "master" uses h2, and "postgres" uses PostgreSQL.

Try each branch, and the go to localhost:8080/testfetch/console.

In that console, type:

import testfetch.Employee
import testfetch.User
Employee.list()

In H2, I get what I would expect:

hibernate.SQL select this_.id as id1_1_1_, this_.version as version2_1_1_, user2_.id as id1_0_0_, user2_.version as version2_0_0_, user2_.employee_id as employee3_0_0_ from employee this_ left outer join app_user user2_ on this_.id=user2_.employee_id

However, in the postgres branch, I get:

hibernate.SQL /* criteria query */ select this_.id as id1_1_0_, this_.version as version2_1_0_ from employee this_
hibernate.SQL /* load testfetch.User */ select user0_.id as id1_0_0_, user0_.version as version2_0_0_, user0_.employee_id as employee3_0_0_ from app_user user0_ where user0_.employee_id=?
hibernate.SQL /* load testfetch.User */ select user0_.id as id1_0_0_, user0_.version as version2_0_0_, user0_.employee_id as employee3_0_0_ from app_user user0_ where user0_.employee_id=?
hibernate.SQL /* load testfetch.User */ select user0_.id as id1_0_0_, user0_.version as version2_0_0_, user0_.employee_id as employee3_0_0_ from app_user user0_ where user0_.employee_id=?
hibernate.SQL /* load testfetch.User */ select user0_.id as id1_0_0_, user0_.version as version2_0_0_, user0_.employee_id as employee3_0_0_ from app_user user0_ where user0_.employee_id=?
hibernate.SQL /* load testfetch.User */ select user0_.id as id1_0_0_, user0_.version as version2_0_0_, user0_.employee_id as employee3_0_0_ from app_user user0_ where user0_.employee_id=?
hibernate.SQL /* load testfetch.User */ select user0_.id as id1_0_0_, user0_.version as version2_0_0_, user0_.employee_id as employee3_0_0_ from app_user user0_ where user0_.employee_id=?
hibernate.SQL /* load testfetch.User */ select user0_.id as id1_0_0_, user0_.version as version2_0_0_, user0_.employee_id as employee3_0_0_ from app_user user0_ where user0_.employee_id=?
hibernate.SQL /* load testfetch.User */ select user0_.id as id1_0_0_, user0_.version as version2_0_0_, user0_.employee_id as employee3_0_0_ from app_user user0_ where user0_.employee_id=?
hibernate.SQL /* load testfetch.User */ select user0_.id as id1_0_0_, user0_.version as version2_0_0_, user0_.employee_id as employee3_0_0_ from app_user user0_ where user0_.employee_id=?
hibernate.SQL /* load testfetch.User */ select user0_.id as id1_0_0_, user0_.version as version2_0_0_, user0_.employee_id as employee3_0_0_ from app_user user0_ where user0_.employee_id=?
hibernate.SQL /* load testfetch.User */ select user0_.id as id1_0_0_, user0_.version as version2_0_0_, user0_.employee_id as employee3_0_0_ from app_user user0_ where user0_.employee_id=?
hibernate.SQL /* load testfetch.User */ select user0_.id as id1_0_0_, user0_.version as version2_0_0_, user0_.employee_id as employee3_0_0_ from app_user user0_ where user0_.employee_id=?
hibernate.SQL /* load testfetch.User */ select user0_.id as id1_0_0_, user0_.version as version2_0_0_, user0_.employee_id as employee3_0_0_ from app_user user0_ where user0_.employee_id=?
hibernate.SQL /* load testfetch.User */ select user0_.id as id1_0_0_, user0_.version as version2_0_0_, user0_.employee_id as employee3_0_0_ from app_user user0_ where user0_.employee_id=?
hibernate.SQL /* load testfetch.User */ select user0_.id as id1_0_0_, user0_.version as version2_0_0_, user0_.employee_id as employee3_0_0_ from app_user user0_ where user0_.employee_id=?
hibernate.SQL /* load testfetch.User */ select user0_.id as id1_0_0_, user0_.version as version2_0_0_, user0_.employee_id as employee3_0_0_ from app_user user0_ where user0_.employee_id=?
hibernate.SQL /* load testfetch.User */ select user0_.id as id1_0_0_, user0_.version as version2_0_0_, user0_.employee_id as employee3_0_0_ from app_user user0_ where user0_.employee_id=?
hibernate.SQL /* load testfetch.User */ select user0_.id as id1_0_0_, user0_.version as version2_0_0_, user0_.employee_id as employee3_0_0_ from app_user user0_ where user0_.employee_id=?
hibernate.SQL /* load testfetch.User */ select user0_.id as id1_0_0_, user0_.version as version2_0_0_, user0_.employee_id as employee3_0_0_ from app_user user0_ where user0_.employee_id=?
hibernate.SQL /* load testfetch.User */ select user0_.id as id1_0_0_, user0_.version as version2_0_0_, user0_.employee_id as employee3_0_0_ from app_user user0_ where user0_.employee_id=?

This is a huge blocker for us. I really appreciate any feedback you can provide.

I am having a real time getting lazy behavior to work properly in Grails 2.4.4 with Hibernate4.

For example, I have two classes, Employee and User. 

class Employee {
  User user
  static mapping = {
    user fetch:'join'
  }
  static belongsTo = User    
}

class User {
  Employee employee 
  static mapping = {
    employee fetch:'join'
    table 'app_user'
  }
}

However, when I try to query these objects with this setup, I still end up with an N+1 problem. For example

Employee.list()
Employee.executeQuery("select e from Employee e")

All of these result in one query to the Employee table, which returns 21 employees, and then 21 queries to the app_user table (which User is mapped to).

However, 

Employee.withCriteria {fetchMode 'user', FetchMode.JOIN)

works. In this case, I only have one query executed.

What am I doing wrong?

Tia.

Solution

  • zyro correctly pointed out in the JIRA issue that we had "max_fetch_depth = 0" in our PostgreSQL config.

    Quoting him:

    the postgres branch has dataSource.hibernate.max_fetch_depth = 0 and the hibernate docs say A 0 disables default outer join fetching. could this be the cause?

    Indeed, after removing this, it performs in the expected way.