[PSQL][Contribution Center] Unable to add a rule when using postgres database

[rdenarie]

1. Create a program
2. Add a rule

When saving there is an error in logs :

2023-11-23 14:03:53,528 | INFO  | Indexing in bulk 3 documents [o.e.analytics.es.AnalyticsESClient] ERROR SqlExceptionHelper ERROR: function lower(bytea) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 753 2023-11-23 14:03:53,594 | WARN  | Uncaught REST Service Exception [o.e.s.rest.impl.DefaultExceptionMapper] javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154) at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1626) at org.hibernate.query.Query.getResultList(Query.java:165) at io.meeds.gamification.dao.RuleDAO.findActiveRuleByEventAndProgramId(RuleDAO.java:135) at io.meeds.gamification.storage.RuleStorage.findActiveRuleByEventAndProgramId(RuleStorage.java:61) at io.meeds.gamification.service.impl.RuleServiceImpl.createRule(RuleServiceImpl.java:275) at io.meeds.gamification.rest.RuleRest.createRule(RuleRest.java:372) at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103) at java.base/java.lang.reflect.Method.invoke(Method.java:580) at org.exoplatform.services.rest.impl.method.DefaultMethodInvoker$1.run(DefaultMethodInvoker.java:192) at org.exoplatform.commons.utils.SecurityHelper.doPrivilegedExceptionAction(SecurityHelper.java:311) at org.exoplatform.services.rest.impl.method.DefaultMethodInvoker.invokeMethod(DefaultMethodInvoker.java:188) at org.exoplatform.services.rest.impl.method.DefaultMethodInvoker.invokeMethod(DefaultMethodInvoker.java:181) at org.exoplatform.services.rest.impl.RequestDispatcher.invokeResourceMethod(RequestDispatcher.java:630) at org.exoplatform.services.rest.impl.RequestDispatcher.dispatch(RequestDispatcher.java:567) at org.exoplatform.services.rest.impl.RequestDispatcher.dispatch(RequestDispatcher.java:507) at org.exoplatform.services.rest.impl.RequestHandlerImpl.handleRequest(RequestHandlerImpl.java:143) at org.exoplatform.services.rest.servlet.RestServlet.onService(RestServlet.java:100) at org.exoplatform.container.web.AbstractHttpServlet.service(AbstractHttpServlet.java:133) at javax.servlet.http.HttpServlet.service(HttpServlet.java:623) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:209) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at org.exoplatform.portal.application.JSFAjaxRedirectFilter.doFilter(JSFAjaxRedirectFilter.java:69) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at org.exoplatform.web.filter.ExtensibleFilter$ExtensibleFilterChain.doFilter(ExtensibleFilter.java:129) at org.exoplatform.mfa.filter.MfaFilter.doFilter(MfaFilter.java:65) at org.exoplatform.web.filter.ExtensibleFilter$ExtensibleFilterChain.doFilter(ExtensibleFilter.java:106) at org.exoplatform.software.register.web.UnlockFilter.doFilter(UnlockFilter.java:23) at org.exoplatform.web.filter.ExtensibleFilter$ExtensibleFilterChain.doFilter(ExtensibleFilter.java:106) at org.exoplatform.account.setup.web.AccountSetupFilter.doFilter(AccountSetupFilter.java:47) at org.exoplatform.web.filter.ExtensibleFilter$ExtensibleFilterChain.doFilter(ExtensibleFilter.java:106) at org.exoplatform.welcomescreens.web.TermsAndConditionsFilter.doFilter(TermsAndConditionsFilter.java:81) at org.exoplatform.web.filter.ExtensibleFilter$ExtensibleFilterChain.doFilter(ExtensibleFilter.java:106) at org.exoplatform.webconferencing.server.filter.SessionFilter.doFilter(SessionFilter.java:58) at org.exoplatform.web.filter.ExtensibleFilter$ExtensibleFilterChain.doFilter(ExtensibleFilter.java:106) at org.exoplatform.services.filters.sessionProvider.ThreadLocalACLSessionProviderInitializedFilter.doFilter(ThreadLocalACLSessionProviderInitializedFilter.java:45) at org.exoplatform.web.filter.ExtensibleFilter$ExtensibleFilterChain.doFilter(ExtensibleFilter.java:106) at org.exoplatform.push.filter.AuthenticationHeaderFilter.doFilter(AuthenticationHeaderFilter.java:69) at org.exoplatform.web.filter.ExtensibleFilter$ExtensibleFilterChain.doFilter(ExtensibleFilter.java:106) at io.meeds.oauth.filter.OAuthAbstractFilter.doFilter(OAuthAbstractFilter.java:47) at org.exoplatform.web.filter.ExtensibleFilter$ExtensibleFilterChain.doFilter(ExtensibleFilter.java:106) at org.exoplatform.web.filter.ExtensibleFilter.doFilter(ExtensibleFilter.java:79) at org.exoplatform.web.filter.GenericFilter.doFilter(GenericFilter.java:70) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at org.exoplatform.services.rest.servlet.RestEncodingFilter.doFilter(RestEncodingFilter.java:56) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at org.exoplatform.portal.application.localization.LocalizationFilter.doFilter(LocalizationFilter.java:145) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at org.exoplatform.web.CacheUserProfileFilter.doFilter(CacheUserProfileFilter.java:73) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at org.exoplatform.web.login.ConversationStateUpdateFilter.doFilter(ConversationStateUpdateFilter.java:66) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at org.exoplatform.services.security.web.SetCurrentIdentityFilter.doFilter(SetCurrentIdentityFilter.java:88) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at org.exoplatform.web.login.RememberMeFilter.doFilter(RememberMeFilter.java:108) at org.exoplatform.web.login.RememberMeFilter.doFilter(RememberMeFilter.java:72) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at io.meeds.oauth.webapi.OAuthDelegateFilter.doFilter(OAuthDelegateFilter.java:51) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at org.gatein.sso.integration.SSODelegateFilter.doFilter(SSODelegateFilter.java:61) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at org.exoplatform.container.web.PortalContainerFilter.doFilter(PortalContainerFilter.java:78) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at org.gatein.portal.init.PortalCheckInitFilter.doFilter(PortalCheckInitFilter.java:66) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at org.exoplatform.frameworks.jcr.web.ThreadLocalSessionProviderInitializedFilter.doFilter(ThreadLocalSessionProviderInitializedFilter.java:122) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:168) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:481) at org.gatein.sso.integration.SSODelegateValve.invoke(SSODelegateValve.java:142) at org.gatein.sso.agent.tomcat.ServletAccessValve.invoke(ServletAccessValve.java:55) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93) at org.gatein.sso.agent.tomcat.ServletAccessValve.invoke(ServletAccessValve.java:55) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:130) at org.apache.catalina.valves.RemoteIpValve.invoke(RemoteIpValve.java:765) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93) at org.apache.catalina.authenticator.SingleSignOn.invoke(SingleSignOn.java:289) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:390) at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63) at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:926) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1790) at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52) at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191) at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.base/java.lang.Thread.run(Thread.java:1583) Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:103) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:37) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:67) at org.hibernate.loader.Loader.getResultSet(Loader.java:2322) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2075) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2037) at org.hibernate.loader.Loader.doQuery(Loader.java:956) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:357) at org.hibernate.loader.Loader.doList(Loader.java:2868) at org.hibernate.loader.Loader.doList(Loader.java:2850) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2682) at org.hibernate.loader.Loader.list(Loader.java:2677) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:540) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:400) at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:219) at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1454) at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1649) at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1617) ... 103 common frames omitted Caused by: org.postgresql.util.PSQLException: ERROR: function lower(bytea) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 753 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415) at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190) at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:134) at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103) at java.base/java.lang.reflect.Method.invoke(Method.java:580) at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:118) at jdk.proxy3/jdk.proxy3.$Proxy123.executeQuery(Unknown Source) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57) ... 118 common frames omitted

[boubaker]

Not reproduced @rdenarie @srenault-meeds

[srenault-meeds]

Thanks @boubaker I let @rdenarie check it again and then close the issue

[boubaker]

Not reproduced @rdenarie @srenault-meeds

Used database version:

• PostgreSQL 16.1 (latest)

[rdenarie]

for the moment, the supported version is 14.6. Did you test with this version ?

[boubaker]

for the moment, the supported version is 14.6. Did you test with this version ?

We don't have a compatibility matrix, so if it works for the latest, it's just fine for us in fact, no need for retro-compatibility.