Use of 'dialect' on cache store.

[ajcmartins]

Hey,

i am trying to understand what does the 'dialect' attribute on a 'string-keyed-jdbc-store' element does. I was expecting that it would take care of low level details related with the database implementation like the DB specific types.

I have a cache configured with a jdbc store pointing to a PostgreSQL DS configured as follows:

<local-cache name="cache1" start="EAGER">
    <string-keyed-jdbc-store datasource="java:jboss/datasources/CacheDS" dialect="POSTGRES" />
</local-cache>

And i am getting the following exception at startup:

10:30:01,733 ERROR [org.infinispan.persistence.jdbc.TableManipulation] (ServerService Thread Pool -- 65) ISPN008011: Error while creating table; used DDL statement: 'CREATE TABLE "ispn_bucket_cache1"(id VARCHAR NOT NULL, datum BINARY, version BIGINT, PRIMARY KEY (id))': org.postgresql.util.PSQLException: ERROR: type "binary" does not exist
  Position: 63
  at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
  at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
  at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
  at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
  at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403)
  at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:331)
  at org.jboss.jca.adapters.jdbc.WrappedStatement.executeUpdate(WrappedStatement.java:375)
  at org.infinispan.persistence.jdbc.TableManipulation.executeUpdateSql(TableManipulation.java:122)
  at org.infinispan.persistence.jdbc.TableManipulation.createTable(TableManipulation.java:105)
  at org.infinispan.persistence.jdbc.TableManipulation.start(TableManipulation.java:148)
  at org.infinispan.persistence.jdbc.binary.JdbcBinaryStore.doConnectionFactoryInitialization(JdbcBinaryStore.java:509)
  at org.infinispan.persistence.jdbc.mixed.JdbcMixedStore.start(JdbcMixedStore.java:69)
  at org.infinispan.persistence.manager.PersistenceManagerImpl.start(PersistenceManagerImpl.java:122)
  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_51]
  at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_51]
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_51]
  at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_51]
  at org.infinispan.commons.util.ReflectionUtil.invokeAccessibly(ReflectionUtil.java:183)
  at org.infinispan.factories.AbstractComponentRegistry$PrioritizedMethod.invoke(AbstractComponentRegistry.java:869)
  at org.infinispan.factories.AbstractComponentRegistry.invokeStartMethods(AbstractComponentRegistry.java:638)
  at org.infinispan.factories.AbstractComponentRegistry.internalStart(AbstractComponentRegistry.java:627)
  at org.infinispan.factories.AbstractComponentRegistry.start(AbstractComponentRegistry.java:530)
  at org.infinispan.factories.ComponentRegistry.start(ComponentRegistry.java:216)
  at org.infinispan.CacheImpl.start(CacheImpl.java:675)
  at org.infinispan.manager.DefaultCacheManager.wireAndStartCache(DefaultCacheManager.java:553)
  at org.infinispan.manager.DefaultCacheManager.createCache(DefaultCacheManager.java:516)
  at org.infinispan.manager.DefaultCacheManager.getCache(DefaultCacheManager.java:398)
  at org.infinispan.manager.DefaultCacheManager.getCache(DefaultCacheManager.java:412)
  at org.jboss.as.clustering.infinispan.DefaultCacheContainer.getCache(DefaultCacheContainer.java:103)
  at org.jboss.as.clustering.infinispan.DefaultCacheContainer.getCache(DefaultCacheContainer.java:94)
  at org.jboss.as.clustering.infinispan.subsystem.CacheService.start(CacheService.java:78)
  at org.jboss.as.clustering.msc.AsynchronousService$1.run(AsynchronousService.java:86) [wildfly-clustering-common-8.0.0.Final.jar:8.0.0.Final]
  at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_51]
  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_51]
  at java.lang.Thread.run(Thread.java:744) [rt.jar:1.7.0_51]
  at org.jboss.threads.JBossThread.run(JBossThread.java:122) [jboss-threads-2.1.1.Final.jar:2.1.1.Final]

This is because the query should be using bytea instead of binary on the data column. I know i can override this using the table configuration but i am wondering if:

1. Dialect isn't supposed to take care of this type of things;
2. The configuration isn't being propagated to the ISPN components;
3. This is something related with infinispan and i should probably post the question in that forum;

Any insights?

Cheers,

[rhusar]

Unless I am mistaken, you still need to specify what are your data types, e.g. the dialect doesn't know how long your VARCHARs are going to be.

Dialect is resolved directly from the driver when possible; when not possible that's when the explicit dialect setting comes to play. Dialect is needed to deal with query structure differences for selects, etc.

[ajcmartins]

I understand that the dialect couldn't know how long my VARCHARs would be if that was the case. But it wouldn't be totally surprising to expect that it would differentiate between basic types (bytea vs binary) when creating tables with the embedded DDL like the one above.

Cheers,