KB484099: Procedure doesn't exist in Platform Analytics Consumer for PostgreSQL

Description

When installing Platform Analytics and the default PostgreSQL database or the default username, "mstr_pa," is not used after starting the Platform Analytics Consumer, you may encounter the following error:

2020-02-20 06:25:31,408 ERROR ScheduledETLProcess pool-3-thread-2 - Error running five minute ETL:
org.hibernate.exception.SQLGrammarException: could not execute statement
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
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.executeUpdate(ResultSetReturnImpl.java:178)
at org.hibernate.engine.query.spi.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:107)
at org.hibernate.internal.StatelessSessionImpl.executeNativeUpdate(StatelessSessionImpl.java:635)
at org.hibernate.query.internal.NativeQueryImpl.doExecuteUpdate(NativeQueryImpl.java:292)
at org.hibernate.query.internal.AbstractProducedQuery.executeUpdate(AbstractProducedQuery.java:1594)
at com.Strategy.pa.persistence.util.PersistObjectUtil.executeSqlQuery(PersistObjectUtil.java:229)
at com.Strategy.pa.persistence.util.PersistObjectUtil.executeStoredProcedure(PersistObjectUtil.java:249)
at com.Strategy.pa.persistence.util.PersistObjectUtil.executeStoredProcedure(PersistObjectUtil.java:243)
at com.Strategy.pa.kafka.consumer.main.ScheduledETLProcess.fiveMinuteETL(ScheduledETLProcess.java:166)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: org.postgresql.util.PSQLException: ERROR: procedure five_minute_etl() does not exist
Hint: No procedure matches the given name and argument types. You might need to add explicit type casts.
Position: 6
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143)
at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:120)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:462)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:175)
... 15 more

Why is this happening?

The root cause of this problem is that the default schema is "public" and not "platform_analytics_wh." This results in the Platform Analytics Consumer not being able to find the procedure.

Solution

Change the default schema from "

public

" to "

platform_analytics_wh

" for connections to the "

platform_analytics_wh

" database.  Log in to the repository database and then run the following SQL query:

ALTER DATABASE platform_analytics_wh SET search_path TO platform_analytics_wh;

The Platform Analytics Consumer should now run without errors.