Search code examples
javapostgresqlquartz-scheduler

Quartz, setting up postgres schema, what should JOB_DATA be?

I'm using liquibase to setup the database tables for quartz as per Bert R recipe.

What is the correct type for the JOB_DATA columns for postgres?

The raw error is

Caused by: org.quartz.JobPersistenceException: Couldn't retrieve trigger: 2
at org.quartz.impl.jdbcjobstore.JobStoreSupport.retrieveTrigger(JobStoreSupport.java:1538)
Caused by: java.lang.ArrayIndexOutOfBoundsException: 2
at org.postgresql.util.PGbytea.toBytes(PGbytea.java:76)

The base error is in PostgreSQlDelegate.java trying to read the JOB_DATA column from the qrtz_triggers table. This column does have just \x in it.

I have changed the above liquibase file to change the type from BLOB to BYTEA as I was getting an error 

column "job_data" is of type oid but expression is of type bytea

The quartz properties file includes the following settings for the Job Store

# Job Store
org.quartz.jobStore.class=org.quartz.impl.jdbcjobstore.JobStoreTX
org.quartz.jobStore.driverDelegateClass = 
org.quartz.impl.jdbcjobstore.PostgreSQLDelegate
Solution

  • The data type for the column should be bytea. Simple way to confirm

    1. Go to http://www.quartz-scheduler.org/downloads/
    2. download your version of quartz and extract it
    3. go to docs/dbTables and look for tables_postgres.sql

    There you will find the data type of job_data as bytea

    Now to the second issue you are facing after modifying the data type to bytea check out this link to see whether it solves the problem? PostgreSQL - Quartz JDBC-JobStoreTX - getTriggersForJob - ArrayIndexOutOfBoundsException