JdbcCustomization for Oracle

[shirishpandharikar]

AutodetectJdbcCustomization currently only supports PostgreSQL and MSSQL. Explicit query limit parts are supported by newer versions of Oracle as well. This can be achieved as below:
FETCH FIRST 5 ROWS ONLY

Although JdbcCustomization can be defined for Oracle, there is no way to set this explicitly during autoconfiguration in Spring Boot.

How can this be achieved? Since DbSchedulerCustomizer allows customizations which cannot be done via configuration properties, it can also allow setting a custom JdbcCustomization which can then be used by the SchedulerBuilder during the autoconfiguration.

[shirishpandharikar]

@kagkarlsson Can you have a look and share your thoughts on this? I was planning to add the OracleJdbcCustomization and raise a PR but then realized that there might be people who are already using Oracle versions which is old and does not support this feature and will break the AutodetectJdbcCustomization. The challenge is it also cannot be created outside as

1. JdbcTaskRepositoryContext is package protected
2. There is no way to set it during Spring Boot autoconfiguration

[kagkarlsson]

Will have a look soon. On vacation. We should probably fix the two issues you mention

[shirishpandharikar]

I'll try and work on a PR. Once you're back you can have a look. Enjoy your vacation.

[kagkarlsson]

There was a similar problem in #357, I pushed a change similar to what you suggest there

[kagkarlsson]

Why do we need JdbcTaskRepositoryContext ?

[shirishpandharikar]

I had a look at how we can support explicit query limit part. It is easy to support this via JdbcCustomzation as shown the comments above. However, to support LOCK_AND_FETCH with Oracle is not easy. Oracle does support SELECT...FOR UPDATE SKIP LOCKED but making with work with explicit query limit and returning the updated rows is not easily supported at least how PostgreSQL supports it.

If this is supported in future in a easy way the new OracleJdbcCustomization will have to override the
public List<Execution> lockAndFetch(JdbcTaskRepositoryContext ctx, Instant now, int limit) method. Since any such database specific customization is not a part of the core framework, defining it would not be possible unless it is defined in the com.github.kagkarlsson.scheduler.jdbc package itself.

Thanks for looking into this. I hope you had a great vacation.

[kagkarlsson]

For non-postgres SELECT AND UPDATE we need to split into two statements and wrap in a transaction:

• BEGIN
• SELECT SKIP LOCKED
• UPDATE selected set picked..
• COMMIT

I was hoping this orchestration could lie in JdbcTaskRepository, and that we just specify if the database supports it or not. I think the postgres-variant requiring only one query is very postgres-specific.

[kagkarlsson]

Now possible to set JdbcCustomization in spring boot (v12.0.0)

[kagkarlsson]

For lock-and-fetch, I have started a PR for adding support via two statements in #371 (only dependency update at time of writing)

[kagkarlsson]

After #371 lands, it will be minimal work to support this for Oracle. This PR also adds explicit LIMIT