Configuring Postgres in Grails

Question

I have an application in migrating to MySQL and PostgreSQL and I both have different behaviors in the allocation of data.

By analyzing the database created in Postgres, I realized that the numbering of ID's created in each table are not being reset in change for another table. For example, it was set in the registers 3 'Table1' and counting the ID 1 to 3. When inserted into an object of another class in another table, the ID should be started for that table, but it follows the sequence of previous and when the object is persisted in 'table2' instead his first start with the ID continues to count from 4. I also noticed some changes in Mysql as the 'false' field 'boolean' is preenchdio with '0 'in Postgres is filled with' false '.

I updated the JDBC but the problem continues. I think maybe there should be some configuration of 'mapping' in the field, but not sure.

My DataSource is thus:

environments {
development {
    dataSource {

        dbCreate = "update" // one of 'create', 'create-drop', 'update', 'validate', ''

    url = "jdbc:postgresql://localhost:5432/app"
        driverClassName = "org.postgresql.Driver"
        dialect = org.hibernate.dialect.PostgreSQLDialect
        //url = "jdbc:mysql://localhost:3306/app"
        username = "app"
        password = "app123"

    }
}
test {
    dataSource {
        dbCreate = "update"
        url = "jdbc:h2:mem:testDb;MVCC=TRUE;LOCK_TIMEOUT=10000"
        username = "sa"
        password = ""
    }
}
production {
    dataSource {
        dbCreate = "update"
        url = "jdbc:postgresql://localhost:5432/app"
        driverClassName = "org.postgresql.Driver"
        username = "postgres"


        pooled = true
        properties {
           maxActive = -1
           minEvictableIdleTimeMillis=1800000
           timeBetweenEvictionRunsMillis=1800000
           numTestsPerEvictionRun=3
           testOnBorrow=true
           testWhileIdle=true
           testOnReturn=true
           validationQuery="SELECT 1"
        }
    }
}

}

You can follow the same pattern as in Mysql Postgres?

Burt Beckwith · Accepted Answer · 2012-10-13 05:48:06Z

1

Hibernate creates a single sequence for all tables in both Postgres and Oracle, but it's easy to create a single sequence per table. See this solution: http://grails.1312388.n4.nabble.com/One-hibernate-sequence-is-used-for-all-Postgres-tables-td1351722.html#a1351725

To use the custom dialect, create the class in src/groovy or src/java. Use whatever package and class name you want. To register it in Grails, set the dialect property in the dataSource block in DataSource.groovy, e.g.

dataSource {
   pooled = true
   dialect = com.foo.bar.MyDialect
   driverClassName = ...
   username = ...
   password = ...
}

edited Oct 13, 2012 at 5:48

answered Oct 12, 2012 at 1:37

Burt Beckwith

75.8k5 gold badges145 silver badges157 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

isilva Over a year ago

Hello Burt! Thanks for responding! I saw all the text and you've created a custom dialect to fix this problem with hibernate. I saw that code you created but would like to know how to implement this dialect in custom Grail? I saw your code but do not know what to do with it, please guide me what should I do to implement this custom dialect and fix this problem.

isilva Over a year ago

Hello Burt! Thanks for helping me! Your code worked PERFECTLY and his tip on how to reference it was really cool. Now I'll think of some way to make the ampo store boolean value '0 'instead of' false '(if you have any tips please tell me). At most I can only thank him. Thank you so much!

Burt Beckwith Over a year ago

You should ask about the 0/false thing in a separate question - best to keep to one question per question :)

Collectives™ on Stack Overflow

Configuring Postgres in Grails

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related