Using lock and fetch throws "No value specified for parameter 7" sql exception

[BukhariH]

Hey @kagkarlsson!

Hope you're well - using your fantastic library at my new place and I saw that version 10.0 includes the pollUsingLockAndFetch so wanted to test it out.

Anyway, end up getting a weird exception only in production:

org.postgresql.util.PSQLException: No value specified for parameter 7.
    at org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:270)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:297)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:448)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:369)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:159)
    at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:148)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
    at com.github.kagkarlsson.jdbc.JdbcRunner.lambda$execute$2(JdbcRunner.java:85)
    at com.github.kagkarlsson.jdbc.JdbcRunner.withConnection(JdbcRunner.java:140)
    at com.github.kagkarlsson.jdbc.JdbcRunner.execute(JdbcRunner.java:66)
    at com.github.kagkarlsson.jdbc.JdbcRunner.query(JdbcRunner.java:62)
    at com.github.kagkarlsson.scheduler.jdbc.PostgreSqlJdbcCustomization.lockAndFetch(PostgreSqlJdbcCustomization.java:58)
    at com.github.kagkarlsson.scheduler.jdbc.AutodetectJdbcCustomization.lockAndFetch(AutodetectJdbcCustomization.java:87)
    at com.github.kagkarlsson.scheduler.jdbc.JdbcTaskRepository.lockAndGetDue(JdbcTaskRepository.java:157)
    at com.github.kagkarlsson.scheduler.LockAndFetchCandidates.run(LockAndFetchCandidates.java:75)
    at com.github.kagkarlsson.scheduler.RunUntilShutdown.run(RunUntilShutdown.java:40)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
    at java.util.concurrent.FutureTask.run(FutureTask.java:264)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630)
    at java.lang.Thread.run(Thread.java:832)
com.github.kagkarlsson.jdbc.SQLRuntimeException: org.postgresql.util.PSQLException: No value specified for parameter 7.
    at com.github.kagkarlsson.jdbc.JdbcRunner.translateException(JdbcRunner.java:126)
    at com.github.kagkarlsson.jdbc.JdbcRunner.lambda$execute$2(JdbcRunner.java:93)
    at com.github.kagkarlsson.jdbc.JdbcRunner.withConnection(JdbcRunner.java:140)
    at com.github.kagkarlsson.jdbc.JdbcRunner.execute(JdbcRunner.java:66)
    at com.github.kagkarlsson.jdbc.JdbcRunner.query(JdbcRunner.java:62)
    at com.github.kagkarlsson.scheduler.jdbc.PostgreSqlJdbcCustomization.lockAndFetch(PostgreSqlJdbcCustomization.java:58)
    at com.github.kagkarlsson.scheduler.jdbc.AutodetectJdbcCustomization.lockAndFetch(AutodetectJdbcCustomization.java:87)
    at com.github.kagkarlsson.scheduler.jdbc.JdbcTaskRepository.lockAndGetDue(JdbcTaskRepository.java:157)
    at com.github.kagkarlsson.scheduler.LockAndFetchCandidates.run(LockAndFetchCandidates.java:75)
    at com.github.kagkarlsson.scheduler.RunUntilShutdown.run(RunUntilShutdown.java:40)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
    at java.util.concurrent.FutureTask.run(FutureTask.java:264)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630)
    at java.lang.Thread.run(Thread.java:832)

Here's my config:

        Scheduler
                .create(configureDataSource(dataSource))
                .tableName(TABLE_NAME)
                .pollingInterval(Duration.ofMillis(50))
                .heartbeatInterval(Duration.ofSeconds(30))
                .shutdownMaxWait(Duration.ofMinutes(1))
                .threads(THREADS)
                .executorService(executor)
                .startTasks(toTasks(jobs))
                .pollUsingLockAndFetch(0.5, 1.0)
                .build();

build.gradle

    implementation 'com.github.kagkarlsson:db-scheduler:10.0'
    implementation 'org.postgresql:postgresql:42.2.19'

I'm running postgres version 12.5 on AWS RDS.

From the stacktrace it seems to be throwing an error here:

db-scheduler/db-scheduler/src/main/java/com/github/kagkarlsson/scheduler/jdbc/PostgreSqlJdbcCustomization.java

Lines 47 to 70 in 1d9139c

	@Override
	public List<Execution> lockAndFetch(JdbcTaskRepositoryContext ctx, Instant now, int limit) {
	final JdbcTaskRepository.UnresolvedFilter unresolvedFilter = new JdbcTaskRepository.UnresolvedFilter(ctx.taskResolver.getUnresolved());
	String selectForUpdateQuery =
	" UPDATE "+ctx.tableName+" st1 SET picked = ?, picked_by = ?, last_heartbeat = ?, version = version + 1 " +
	" WHERE (st1.task_name, st1.task_instance) IN (" +
	" SELECT st2.task_name, st2.task_instance FROM "+ctx.tableName+" st2 " +
	" WHERE picked = ? and execution_time <= ? " + unresolvedFilter.andCondition() + " order by execution_time asc FOR UPDATE SKIP LOCKED LIMIT ?)" +
	" RETURNING st1.*";
	return ctx.jdbcRunner.query(selectForUpdateQuery,
	ps -> {
	// Update
	ps.setBoolean(1, true); // picked (new)
	ps.setString(2, truncate(ctx.schedulerName.getName(), 50)); // picked_by
	setInstant(ps, 3, now); // last_heartbeat
	// Inner select
	ps.setBoolean(4, false); // picked (old)
	setInstant(ps, 5, now); // execution_time
	ps.setInt(6, limit); // limit
	},
	ctx.resultSetMapper.get());
	}

I re-read the query a few times and as far as I can tell there seems to be six params there so I'm not sure sure where postgres is getting the 7th param from?

Anyway - would be great to get it working so let me know if you need anything else from my side!

[kagkarlsson]

Thank you! Will look at it as soon as possible! /Gustav tir. 23. mar. 2021 kl. 23:01 skrev Hasnain Bukhari ***@***.***

…

: Hey @kagkarlsson <https://github.com/kagkarlsson>! Hope you're well - using your fantastic library at my new place and I saw that version 10.0 includes the pollUsingLockAndFetch so wanted to test it out. Anyway, end up getting a weird exception only in production: org.postgresql.util.PSQLException: No value specified for parameter 7. at org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:270) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:297) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:448) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:369) at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:159) at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:148) at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java) at com.github.kagkarlsson.jdbc.JdbcRunner.lambda$execute$2(JdbcRunner.java:85) at com.github.kagkarlsson.jdbc.JdbcRunner.withConnection(JdbcRunner.java:140) at com.github.kagkarlsson.jdbc.JdbcRunner.execute(JdbcRunner.java:66) at com.github.kagkarlsson.jdbc.JdbcRunner.query(JdbcRunner.java:62) at com.github.kagkarlsson.scheduler.jdbc.PostgreSqlJdbcCustomization.lockAndFetch(PostgreSqlJdbcCustomization.java:58) at com.github.kagkarlsson.scheduler.jdbc.AutodetectJdbcCustomization.lockAndFetch(AutodetectJdbcCustomization.java:87) at com.github.kagkarlsson.scheduler.jdbc.JdbcTaskRepository.lockAndGetDue(JdbcTaskRepository.java:157) at com.github.kagkarlsson.scheduler.LockAndFetchCandidates.run(LockAndFetchCandidates.java:75) at com.github.kagkarlsson.scheduler.RunUntilShutdown.run(RunUntilShutdown.java:40) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515) at java.util.concurrent.FutureTask.run(FutureTask.java:264) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630) at java.lang.Thread.run(Thread.java:832) com.github.kagkarlsson.jdbc.SQLRuntimeException: org.postgresql.util.PSQLException: No value specified for parameter 7. at com.github.kagkarlsson.jdbc.JdbcRunner.translateException(JdbcRunner.java:126) at com.github.kagkarlsson.jdbc.JdbcRunner.lambda$execute$2(JdbcRunner.java:93) at com.github.kagkarlsson.jdbc.JdbcRunner.withConnection(JdbcRunner.java:140) at com.github.kagkarlsson.jdbc.JdbcRunner.execute(JdbcRunner.java:66) at com.github.kagkarlsson.jdbc.JdbcRunner.query(JdbcRunner.java:62) at com.github.kagkarlsson.scheduler.jdbc.PostgreSqlJdbcCustomization.lockAndFetch(PostgreSqlJdbcCustomization.java:58) at com.github.kagkarlsson.scheduler.jdbc.AutodetectJdbcCustomization.lockAndFetch(AutodetectJdbcCustomization.java:87) at com.github.kagkarlsson.scheduler.jdbc.JdbcTaskRepository.lockAndGetDue(JdbcTaskRepository.java:157) at com.github.kagkarlsson.scheduler.LockAndFetchCandidates.run(LockAndFetchCandidates.java:75) at com.github.kagkarlsson.scheduler.RunUntilShutdown.run(RunUntilShutdown.java:40) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515) at java.util.concurrent.FutureTask.run(FutureTask.java:264) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630) at java.lang.Thread.run(Thread.java:832) Here's my config: Scheduler .create(configureDataSource(dataSource)) .tableName(TABLE_NAME) .pollingInterval(Duration.ofMillis(50)) .heartbeatInterval(Duration.ofSeconds(30)) .shutdownMaxWait(Duration.ofMinutes(1)) .threads(THREADS) .executorService(executor) .startTasks(toTasks(jobs)) .pollUsingLockAndFetch(0.5, 1.0) .build(); build.gradle implementation 'com.github.kagkarlsson:db-scheduler:10.0' implementation 'org.postgresql:postgresql:42.2.19' I'm running postgres version 12.5 on AWS RDS. From the stacktrace it seems to be throwing an error here: https://github.com/kagkarlsson/db-scheduler/blob/1d9139c42397562dd5c4cd793363eabdc84f762e/db-scheduler/src/main/java/com/github/kagkarlsson/scheduler/jdbc/PostgreSqlJdbcCustomization.java#L47-L70 I re-read the query a few times and as far as I can tell there seems to be six params there so I'm not sure sure where postgres is getting the 7th param from? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#190>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AABKEHBVB4BQMNGTNGNYQ7DTFEFUBANCNFSM4ZWBPDGA> .

[kagkarlsson]

It is caused by the UnresolvedFilter that attempts to filter out executions that exist in the database table, but your Scheduler instance have no mapping for. That filter adds some parameters which are never set (a bug).
Could be resolved short-term for you by removing that execution in the table, but I think this PR should do the trick: #191
Will try to get it released asap

[BukhariH]

Thanks so much for the quick response Karl!

Deleted the unresolved task then deployed lock & fetch and it's all working very smoothly!

We are very heavy pollers and our CPU usage on the DB has dropped by half after releasing the lock & fetch strategy.

Thanks so much for all the hard work you put in to developing this library for us all!

[kagkarlsson]

Very good to hear that the new polling strategy had such a significant effect! Thank you for taking time and giving feedback 🙇

[kagkarlsson]

Fix released in v10.1