I am not able to use other schema (than public) for quartz tables. This is my quartz setup:

spring.quartz.job-store-type=jdbc
spring.quartz.jdbc.initialize-schema=always
spring.quartz.properties.org.quartz.jobStore.driverDelegateClass=org.quartz.impl.jdbcjobstore.PostgreSQLDelegate
spring.quartz.properties.org.quartz.jobStore.isClustered=true
spring.quartz.properties.org.quartz.jobStore.clusterCheckinInterval=2000
spring.quartz.properties.org.quartz.scheduler.instanceId=AUTO
spring.quartz.properties.org.quartz.jobStore.class = org.quartz.impl.jdbcjobstore.JobStoreTX
spring.quartz.properties.org.quartz.jobStore.useProperties=false
spring.quartz.properties.org.quartz.jobStore.tablePrefix=QRTZ_

And config class:

@Bean
public SchedulerFactoryBean schedulerFactory(ApplicationContext applicationContext, DataSource dataSource, QuartzProperties quartzProperties) {
    SchedulerFactoryBean schedulerFactoryBean = new SchedulerFactoryBean();
    AutowireCapableBeanJobFactory jobFactory = new AutowireCapableBeanJobFactory(applicationContext.getAutowireCapableBeanFactory());
    Properties properties = new Properties();
    properties.putAll(quartzProperties.getProperties());

    schedulerFactoryBean.setOverwriteExistingJobs(true);
    schedulerFactoryBean.setDataSource(dataSource);
    schedulerFactoryBean.setQuartzProperties(properties);
    schedulerFactoryBean.setJobFactory(jobFactory);

    return schedulerFactoryBean;
}

@Bean
public Scheduler scheduler(ApplicationContext applicationContext, DataSource dataSource, QuartzProperties quartzProperties)
    throws SchedulerException {
    Scheduler scheduler = schedulerFactory(applicationContext, dataSource, quartzProperties).getScheduler();
    scheduler.start();
    return scheduler;
}

This works fine, and the tables are getting created. However I would like to have the tables in a different schema. So I set quartz to use 'quartz' schema.

spring.quartz.properties.org.quartz.jobStore.tablePrefix=quartz.QRTZ_

This is the error I'm getting:

[ClusterManager: Error managing cluster: Failure obtaining db row lock: ERROR: current transaction is aborted, commands ignored until end of transaction block] [org.quartz.impl.jdbcjobstore.LockException: Failure obtaining db row lock: ERROR: current transaction is aborted, commands ignored until end of transaction block

Any ideas on how to solve it?

CodePudding user response：

It was a bold hope that "tablePrefix" can also adjsut the "db schema", (and there is no documented property concerning "db schema"), but you could get more lucky, if you configure it on the datasource. i.e. you would introduce/configure different (spring) datasource( bean)s for every user/schema used by your application ...

• (like here:) Spring Boot Configure and Use Two DataSources
• or here

, then you'd wire the scheduler factory with the appropriate datasource (quartz).

schedulerFactoryBean.setDataSource(quartzDataSource);

Or via (@Autowired) parameter injection, or method invocation : @Bean initialization - difference between parameter injection vs. direct method access?

UPDATE (regarding "wiring"):

..from current spring-boot doc:

To have Quartz use a DataSource other than the application’s main DataSource, declare a DataSourcebean, annotating its @Bean method with @QuartzDataSource. Doing so ensures that the Quartz-specific DataSource is used by both the SchedulerFactoryBean and for schema initialization. Similarly, to have Quartz use a TransactionManager other than the application’s main ... declare a TransactionManager bean, ...@QuartzTransactionManager.

You can take even more control by customizing:

spring.quartz.jdbc.initialize-schema 
Database schema initialization mode.
default: embedded (embedded|always|never)

spring.quartz.jdbc.schema 
Path to the SQL file to use to initialize the database schema.
default: classpath:org/quartz/impl/jdbcjobstore/tables_@@platform@@.sql

... properties, where @@platform@@ refers to your db vendor.

But it is useless for your requirement... since looking at and complying with the original schemes - they seem schema independent/free. (So the data source approach looks more promising, herefor.)

REFS:

• https://www.quartz-scheduler.org/documentation/quartz-2.3.0/configuration/ConfigJobStoreTX.html
• Spring Boot Configure and Use Two DataSources
• https://stackoverflow.com/a/42360877/592355
• https://www.baeldung.com/spring-annotations-resource-inject-autowire
• @Bean initialization - difference between parameter injection vs. direct method access?
• spring.quartz.jdbc.initialize-schema
• What are the possible values of spring.datasource.initialization-mode?
• spring.quartz.jdbc.schema
• https://github.com/quartz-scheduler/quartz/tree/master/quartz-core/src/main/resources/org/quartz/impl/jdbcjobstore
• https://docs.spring.io/spring-boot/docs/current/reference/html/features.html#features.quartz
• https://docs.spring.io/spring-boot/docs/current/api/org/springframework/boot/autoconfigure/quartz/QuartzDataSource.html

CodePudding user response：

So the idea is that Quartz doesn't create the tables using spring.quartz.properties.org.quartz.jobStore.tablePrefix

Table names are static. Eg qrtz_triggers. as @xerx593 pointed out.

What we can do is to create the tables (manual, flyway, liquibase) in a different schema, update tablePrefix=schema.qrtz_ and it will work.

Tested with Postgres