PostgreSQL error in "process-usertasks-quarkus-with-console" example

[CristopherGR]

Describe the bug

"process-usertasks-quarkus-with-console" Kogito Example

I am trying to start this Kogito example to test the Management Console and Task Console.

The README says that I should use these two commands:

1. mvn clean install -Ppostgresql,container to compile project with postgresql configuration
2. sh ./startServices.sh to start all the services that the project needs (the sh file executes a docker-compose command).

When the .sh execution finishes, I have the following services up:

• Infinispan: 11222
• Kafka: 9092
• Data Index: 8180
• Management Console: 8280
• Task Console: 8380
• Keycloak: 8480
• example-runtime-service: 8080

The services started without problems, each in a docker container, including the kogito example (8080). When I am trying to do this POST request http://localhost:8080/hiring with following content (JSON):

{   
    "candidate": {
        "name": "Jon Snow",
        "email": "jsnow@example.com",
        "salary": 30000,
        "skills": "Java, Kogito"
    }
}

I have the following ERROR in the LOGS of the containers:

docker-compose-postgres-1  | 2023-08-29 03:26:05.611 UTC [67] ERROR:  relation "process_instances" does not exist at character 13
docker-compose-postgres-1  | 2023-08-29 03:26:05.611 UTC [67] STATEMENT:  INSERT INTO process_instances (id, payload, process_id, process_version, version) VALUES ($1, $2, $3, $4, $5)
example-runtime-service    | 2023-08-29 03:26:05,618 ERROR [io.qua.ver.htt.run.QuarkusErrorHandler] (executor-thread-0) HTTP Request to /hiring failed, error id: 064bfdf5-29c3-4e7f-8d38-1c934bbc6997-1: org.jboss.resteasy.spi.UnhandledException: java.lang.RuntimeException: Error inserting process instance 0ead8744-4d02-4c83-ba83-c47ff62f5682
example-runtime-service    |    at org.jboss.resteasy.core.ExceptionHandler.handleApplicationException(ExceptionHandler.java:105)
example-runtime-service    |    at org.jboss.resteasy.core.ExceptionHandler.handleException(ExceptionHandler.java:359)
example-runtime-service    |    at org.jboss.resteasy.core.SynchronousDispatcher.writeException(SynchronousDispatcher.java:218)
example-runtime-service    |    at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:519)
example-runtime-service    |    at org.jboss.resteasy.core.SynchronousDispatcher.lambda$invoke$4(SynchronousDispatcher.java:261)
example-runtime-service    |    at org.jboss.resteasy.core.SynchronousDispatcher.lambda$preprocess$0(SynchronousDispatcher.java:161)
example-runtime-service    |    at org.jboss.resteasy.core.interception.jaxrs.PreMatchContainerRequestContext.filter(PreMatchContainerRequestContext.java:364)
example-runtime-service    |    at org.jboss.resteasy.core.SynchronousDispatcher.preprocess(SynchronousDispatcher.java:164)
example-runtime-service    |    at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:247)
example-runtime-service    |    at io.quarkus.resteasy.runtime.standalone.RequestDispatcher.service(RequestDispatcher.java:82)
example-runtime-service    |    at io.quarkus.resteasy.runtime.standalone.VertxRequestHandler.dispatch(VertxRequestHandler.java:147)
example-runtime-service    |    at io.quarkus.resteasy.runtime.standalone.VertxRequestHandler$1.run(VertxRequestHandler.java:93)
example-runtime-service    |    at io.quarkus.vertx.core.runtime.VertxCoreRecorder$14.runWith(VertxCoreRecorder.java:576)
example-runtime-service    |    at org.jboss.threads.EnhancedQueueExecutor$Task.run(EnhancedQueueExecutor.java:2449)
example-runtime-service    |    at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1478)
example-runtime-service    |    at org.jboss.threads.DelegatingRunnable.run(DelegatingRunnable.java:29)
example-runtime-service    |    at org.jboss.threads.ThreadLocalResettingRunnable.run(ThreadLocalResettingRunnable.java:29)
example-runtime-service    |    at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
example-runtime-service    |    at java.base/java.lang.Thread.run(Thread.java:829)
example-runtime-service    | Caused by: java.lang.RuntimeException: Error inserting process instance 0ead8744-4d02-4c83-ba83-c47ff62f5682
example-runtime-service    |    at org.kie.kogito.persistence.jdbc.Repository.uncheckedException(Repository.java:64)
example-runtime-service    |    at org.kie.kogito.persistence.jdbc.GenericRepository.insertInternal(GenericRepository.java:61)
example-runtime-service    |    at org.kie.kogito.persistence.jdbc.JDBCProcessInstances.create(JDBCProcessInstances.java:60)
example-runtime-service    |    at org.kie.kogito.process.impl.AbstractProcessInstance.lambda$start$1(AbstractProcessInstance.java:286)
example-runtime-service    |    at org.kie.kogito.services.uow.ProcessInstanceWorkUnit.perform(ProcessInstanceWorkUnit.java:47)
example-runtime-service    |    at org.kie.kogito.services.uow.CollectingUnitOfWork.end(CollectingUnitOfWork.java:63)
example-runtime-service    |    at org.kie.kogito.services.uow.ManagedUnitOfWork.end(ManagedUnitOfWork.java:51)
example-runtime-service    |    at org.kie.kogito.services.uow.UnitOfWorkExecutor.executeInUnitOfWork(UnitOfWorkExecutor.java:38)
example-runtime-service    |    at org.kie.kogito.process.impl.ProcessServiceImpl.createProcessInstance(ProcessServiceImpl.java:67)
example-runtime-service    |    at org.kie.kogito.hr.HiringResource.createResource_hiring(HiringResource.java:76)
example-runtime-service    |    at org.kie.kogito.hr.HiringResource_ClientProxy.createResource_hiring(Unknown Source)
example-runtime-service    |    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
example-runtime-service    |    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
example-runtime-service    |    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
example-runtime-service    |    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
example-runtime-service    |    at org.jboss.resteasy.core.MethodInjectorImpl.invoke(MethodInjectorImpl.java:170)
example-runtime-service    |    at org.jboss.resteasy.core.MethodInjectorImpl.invoke(MethodInjectorImpl.java:130)
example-runtime-service    |    at org.jboss.resteasy.core.ResourceMethodInvoker.internalInvokeOnTarget(ResourceMethodInvoker.java:660)
example-runtime-service    |    at org.jboss.resteasy.core.ResourceMethodInvoker.invokeOnTargetAfterFilter(ResourceMethodInvoker.java:524)
example-runtime-service    |    at org.jboss.resteasy.core.ResourceMethodInvoker.lambda$invokeOnTarget$2(ResourceMethodInvoker.java:474)
example-runtime-service    |    at org.jboss.resteasy.core.interception.jaxrs.PreMatchContainerRequestContext.filter(PreMatchContainerRequestContext.java:364)
example-runtime-service    |    at org.jboss.resteasy.core.ResourceMethodInvoker.invokeOnTarget(ResourceMethodInvoker.java:476)
example-runtime-service    |    at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:434)
example-runtime-service    |    at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:408)
example-runtime-service    |    at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:69)
example-runtime-service    |    at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:492)
example-runtime-service    |    ... 15 more
example-runtime-service    | Caused by: org.postgresql.util.PSQLException: ERROR: relation "process_instances" does not exist
example-runtime-service    |   Position: 13
example-runtime-service    |    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
example-runtime-service    |    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
example-runtime-service    |    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
example-runtime-service    |    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496)
example-runtime-service    |    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413)
example-runtime-service    |    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)
example-runtime-service    |    at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:152)
example-runtime-service    |    at io.agroal.pool.wrapper.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:88)
example-runtime-service    |    at org.kie.kogito.persistence.jdbc.GenericRepository.insertInternal(GenericRepository.java:59)
example-runtime-service    |    ... 39 more

I understand that it is a problem with the database. But, as it is a container that is launched from a docker-compose command I don't know how I could fix the error.

Expected behavior

No response

Actual behavior

No response

How to Reproduce?

No response

Output of uname -a or ver

No response

Output of java -version

No response

GraalVM version (if different from Java)

No response

Kogito version or git rev (or at least Quarkus version if you are using Kogito via Quarkus platform BOM)

No response

Build tool (ie. output of mvnw --version or gradlew --version)

No response

Additional information

No response

[radtriste]

@cristianonicolai @wmedvede @ricardozanini looks like an issue with Flyway ?

[radtriste]

cc @nmirasch

[reillykw]

Add
%postgresql.quarkus.flyway.baseline-on-migrate=true
to application.properties and run
mvn clean package quarkus:dev -Pinfinispan

Running the service outside of docker will initialize the migrations for the database, which is what postgresql is missing here: the process_instances table

[ricardozanini]

Yes, it seems a problem with Flyway. @nmirasch @tiagodolphine @wmedvede can you take a look?

[ms9580]

Please download the DDL scipts file from https://repository.jboss.org/org/kie/kogito/kogito-ddl/1.43.0.Final/ which is mentioned on the documentation page https://docs.jboss.org/kogito/release/latest/html_single/#proc-infinispan-persistence-enabling_kogito-configuring.
In this zip file you will find DDL scripts for postgres in a separate folder named postgresql. Execute the script V1.35.0__create_runtime_PostgreSQL.sql in the same schema/database which is mentioned in the configuration.
This will resolve the error.
It seems that the DDLs in this script are not executed automatically.

Regards.