0

I am getting below errors on PCF while connecting Batch App with the Postgres DB. I've used script to create tables - https://github.com/spring-projects/spring-batch/blob/master/spring-batch-core/src/main/resources/org/springframework/batch/core/schema-postgresql.sql, but when I am opening tables in DBeaver client its showing double quotes ("). Version is reserved keyword in Postgres and I don't have a ways to changes internal code that Spring Batch uses to query database.

In both ways I am block, any quick pointers?

Error:

021-01-25T18:58:07.060+05:30 [APP/PROC/WEB/0] [OUT] Position: 68] with root cause
2021-01-25T18:58:07.060+05:30 [APP/PROC/WEB/0] [OUT] org.postgresql.util.PSQLException: ERROR: column "version" of relation "batch_job_instance" does not exist
2021-01-25T18:58:07.060+05:30 [APP/PROC/WEB/0] [OUT] Position: 68
2021-01-25T18:58:07.060+05:30 [APP/PROC/WEB/0] [OUT] at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2533) ~[postgresql-42.2.12.jar:42.2.12]
2021-01-25T18:58:07.060+05:30 [APP/PROC/WEB/0] [OUT] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2268) ~[postgresql-42.2.12.jar:42.2.12]
2021-01-25T18:58:07.060+05:30 [APP/PROC/WEB/0] [OUT] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:313) ~[postgresql-42.2.12.jar:42.2.12]
2021-01-25T18:58:07.060+05:30 [APP/PROC/WEB/0] [OUT] at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:448) ~[postgresql-42.2.12.jar:42.2.12]
2021-01-25T18:58:07.060+05:30 [APP/PROC/WEB/0] [OUT] at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:159) ~[postgresql-42.2.12.jar:42.2.12]
2021-01-25T18:58:07.060+05:30 [APP/PROC/WEB/0] [OUT] at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:125) ~[postgresql-42.2.12.jar:42.2.12]
2021-01-25T18:58:07.060+05:30 [APP/PROC/WEB/0] [OUT] at org.springframework.jdbc.core.JdbcTemplate.lambda$update$0(JdbcTemplate.java:867) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
2021-01-25T18:58:07.060+05:30 [APP/PROC/WEB/0] [OUT] at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:922) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
2021-01-25T18:58:07.060+05:30 [APP/PROC/WEB/0] [OUT] at org.springframework.batch.core.repository.dao.JdbcJobInstanceDao.createJobInstance(JdbcJobInstanceDao.java:120) ~[spring-batch-core-4.2.2.RELEASE.jar:4.2.2.RELEASE]
2021-01-25T18:58:07.060+05:30 [APP/PROC/WEB/0] [OUT] at org.springframework.batch.core.repository.support.SimpleJobRepository.createJobExecution(SimpleJobRepository.java:140) ~[spring-batch-core-4.2.2.RELEASE.jar:4.2.2.RELEASE]
2021-01-25T18:58:07.060+05:30 [APP/PROC/WEB/0] [OUT] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_265]
2021-01-25T18:58:07.060+05:30 [APP/PROC/WEB/0] [OUT] at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344) ~[spring-aop-5.2.6.RELEASE.jar:5.2.6.RELEASE]
2021-01-25T18:58:07.060+05:30 [APP/PROC/WEB/0] [OUT] at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198) ~[spring-aop-5.2.6.RELEASE.jar:5.2.6.RELEASE]
2021-01-25T18:58:07.060+05:30 [APP/PROC/WEB/0] [OUT] at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118) ~[spring-tx-5.2.6.RELEASE.jar:5.2.6.RELEASE]
2021-01-25T18:58:07.060+05:30 [APP/PROC/WEB/0] [OUT] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.6.RELEASE.jar:5.2.6.RELEASE]
2021-01-25T18:58:07.060+05:30 [APP/PROC/WEB/0] [OUT] at org.springframework.batch.core.repository.support.AbstractJobRepositoryFactoryBean$1.invoke(AbstractJobRepositoryFactoryBean.java:181) ~[spring-batch-core-4.2.2.RELEASE.jar:4.2.2.RELEASE]
2021-01-25T18:58:07.060+05:30 [APP/PROC/WEB/0] [OUT] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) ~[spring-aop-5.2.6.RELEASE.jar:5.2.6.RELEASE]
2021-01-25T18:58:07.060+05:30 [APP/PROC/WEB/0] [OUT] at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:137) ~[spring-batch-core-4.2.2.RELEASE.jar:4.2.2.RELEASE]
2021-01-25T18:58:07.060+05:30 [APP/PROC/WEB/0] [OUT] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_265]
2021-01-25T18:58:07.060+05:30 [APP/PROC/WEB/0] [OUT] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_265]
2021-01-25T18:58:07.060+05:30 [APP/PROC/WEB/0] [OUT] at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198) ~[spring-aop-5.2.6.RELEASE.jar:5.2.6.RELEASE]
2021-01-25T18:58:07.060+05:30 [APP/PROC/WEB/0] [OUT] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.2.6.RELEASE.jar:5.2.6.RELEASE]
2021-01-25T18:58:07.060+05:30 [APP/PROC/WEB/0] [OUT] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.6.RELEASE.jar:5.2.6.RELEASE]
2021-01-25T18:58:07.060+05:30 [APP/PROC/WEB/0] [OUT] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) ~[spring-aop-5.2.6.RELEASE.jar:5.2.6.RELEASE]
2021-01-25T18:58:07.060+05:30 [APP/PROC/WEB/0] [OUT] at com.mastercard.customer.data.management.batch.controller.MdsBatchJobController.handle(MdsBatchJobController.java:99) ~[classes/:na]
2021-01-25T18:58:07.060+05:30 [APP/PROC/WEB/0] [OUT] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_265]
2021-01-25T18:58:07.060+05:30 [APP/PROC/WEB/0] [OUT] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_265]
2021-01-25T18:58:07.060+05:30 [APP/PROC/WEB/0] [OUT] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_265]
2021-01-25T18:58:07.060+05:30 [APP/PROC/WEB/0] [OUT] at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_265]
2021-01-25T18:58:07.060+05:30 [APP/PROC/WEB/0] [OUT] at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:105) ~[spring-webmvc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
2021-01-25T18:58:07.060+05:30 [APP/PROC/WEB/0] [OUT] at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:879) ~[spring-webmvc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
2021-01-25T18:58:07.060+05:30 [APP/PROC/WEB/0] [OUT] at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:793) ~[spring-webmvc-5.2.6.RELEASE.jar:5.2.6.RELEASE]

In My postgres client script shows like -

CREATE TABLE scv_stg.batch_job_instance (
 job_instance_id int8 NOT NULL,
    "version" int8 NULL,
    job_name varchar(100) NOT NULL,
    job_key varchar(32) NOT NULL,
    CONSTRAINT batch_job_instance_pkey PRIMARY KEY (job_instance_id),
    CONSTRAINT job_inst_un UNIQUE (job_name, job_key)
)
Jeff Cook
  • 7,956
  • 36
  • 115
  • 186
  • This seems to be related to the syntax you used to declare the `version` column. You are putting it between two `"` while the DDL script provided by spring batch does not: https://github.com/spring-projects/spring-batch/blob/master/spring-batch-core/src/main/resources/org/springframework/batch/core/schema-postgresql.sql#L5. Have you tried the same syntax as the DDL of Spring Batch? I'm not familiar with postgres's syntax which might differ depending on the version, but this might help: https://stackoverflow.com/questions/10200769/postgresql-column-foo-does-not-exist. – Mahmoud Ben Hassine Jan 26 '21 at 09:51
  • @MahmoudBenHassine - I've used script from here as is - https://github.com/spring-projects/spring-batch/blob/master/spring-batch-core/src/main/resources/org/springframework/batch/core/schema-postgresql.sql. But when I'm opening tables in DBeaver client its showing version in "" quotes which is reserved keyword in postgres. How can we solve this issue? – Jeff Cook Jan 27 '21 at 05:31
  • Is your postgres server running on PCF as well? Let's forget about the DBeaver client for a moment. So if I understand correctly: 1) you create tables on postgres in PCF using the script provided by Spring Batch, 2) you run a job on PCF, 3) you get that exception. Is that correct? – Mahmoud Ben Hassine Jan 27 '21 at 07:48
  • Postgres is not on PCF, Postgres is a separate dedicated VM. if I delete all metadata tables and execute the https://github.com/spring-projects/spring-batch/blob/master/spring-batch-core/src/main/resources/org/springframework/batch/core/schema-postgresql.sql again and try to execute batch, I do get the same error. Wonder ! – Jeff Cook Jan 27 '21 at 08:45
  • I'm not able to reproduce the issue. Here is a complete example: https://github.com/spring-projects/spring-batch/issues/3092#issuecomment-768161553. This test starts postgresql in a docker container, executes the DDL script provided by Spring Batch and then runs a simple job. The job runs successfully and the exception you report does not happen. Please provide a [minimal complete example](https://stackoverflow.com/help/minimal-reproducible-example) the reproduces the issue to be able to help you. – Mahmoud Ben Hassine Jan 27 '21 at 09:45

0 Answers0