Setting dialect on Grails 3.0.10 and 3.0.11

[domiko]

Hi Ivan. p.s. I found a workaround while writing this issue report

I've upgraded my application to Grails 3.0.11 and depending on the configuration I'm having one of 2 problems.

Note that

• The application works fine with Grails 3.0.9 (when configured as in point 1 under the 'hibernate:' section of application.yml).
• I'm using version 4.6.4 of grails-postgresql-extentions plugin.

Behaviour 1) If, in application.yml, I set the dialect under the 'hibernate:' section .. I get

• The net.kaleidos.hibernate.PostgresqlExtensionsDialect dialect IS NOT taken into account and hibernate ends up using the org.hibernate.dialect.PostgreSQL9Dialect default dialect.
• no exception.

2) If I set the dialect under the 'dataSource:' section (like it seems to have been suggested in different Grails discussions) .. I get

• The net.kaleidos.hibernate.PostgresqlExtensionsDialect dialect IS taken into account.
• A ClassCastException during startup on a Boolean conversion.

Workaround When configured as in point 2. The aforementioned exception can be traced back to line 60 of net.kaleidos.hibernate.PostgresqlExtensionsDialect. The dialect expect the dataSource.postgresql.extensions.sequence_per_table property to be present in application.yml. I added it and set it to true (I'm using discrete sequences) and it works fine.

[ilopmar]

Hi.

The problem with the ClassCastException during the startup was reported in #84 and is fixed in the version 4.6.5 released a few days ago. You can upgrade the plugin version and remove the sequence_per_table property because now it's again true by default.

Regarding the problem with the dialect, I'm going to do some tests and let you know what happen.

[ilopmar]

I remember now that the dialect problem was discussed here #80. In that issue the problem was that it wasn't defined under the wrong key.

I've been doing some tests and it seems that everything works fine with the documented configuration, dialect under hibernate key.

Could you please clone the following repo and do a tests:

https://github.com/ilopmar/pg-extensions-grails3-jsonb-example-app

After cloning just edit the application.yml file and set the credentials to connect to your database: url, username and password. Then just start the application using Grails 3.0.11.

Then, to make sure that the plugin dialect is being used, just connect to the database and see if the table is created with the jsonb type.

$ psql -U postgres pg_extensions_test
psql (9.4.5)

pg_extensions_test=# \d test_map_jsonb
 Table "public.test_map_jsonb"
┌─────────┬────────┬───────────┐
│ Column  │  Type  │ Modifiers │
├─────────┼────────┼───────────┤
│ id      │ bigint │ not null  │
│ version │ bigint │ not null  │
│ data    │ jsonb  │           │
└─────────┴────────┴───────────┘
Indexes:
    "test_map_jsonb_pkey" PRIMARY KEY, btree (id)

If I don't set the dialect or set it to org.hibernate.dialect.PostgreSQL9Dialect the application doesn't start because hibernate doesn't know how to map the user type:

org.hibernate.MappingException: No Dialect mapping for JDBC type: 90022

I've also tried to define the dialect under the dataSource key and it also works. Maybe something was changed in the last releases of Grails because with the first 3.0.X releases it didn't work.

So, as a summary, it seems that now you can define the dialect under the dataSource or the hibernate key.

Please let me know if with this information everything is working so I can close the issue.

[ilopmar]

And the same problem with the dialect happened here #79.

[domiko]

Hey.

I should have looked issue at closed issues before raising a new one :).

As for the dialect configuration I read this entry when I tried (and failed) to migrate to Grails 3.0.10. So when I did the migration to 3.0.11 I naturally tried to put the dialect in the hibernate section as well as in the dataSource one.

I will clone and try your repo later on today (or tomorrow) and keep you posted. Thank you.

[ilopmar]

Thanks for pointing to the issue in Grails-core. I didn't aware of that.

That's explains why at the beginning it only worked in hibernate. I'll update the documentation with this new information.