DefaultLockRepository fails silently w/ PostgreSQL

[agebhar1]

In what version(s) of Spring Integration are you seeing this issue?

unreleased main

Describe the bug

In #8606 ttl type moved to Duration and is used with JdbcTemplate, e.g.

spring-integration/spring-integration-jdbc/src/main/java/org/springframework/integration/jdbc/lock/DefaultLockRepository.java

Lines 340 to 357 in 191f693

	public boolean acquire(String lock) {
	Boolean result =
	this.serializableTransactionTemplate.execute(
	transactionStatus -> {
	if (this.template.update(this.updateQuery, this.id, Instant.now(),
	this.region, lock, this.id, Instant.now().minus(this.ttl)) > 0) {
	return true;
	}
	try {
	return this.template.update(this.insertQuery, this.region, lock, this.id,
	Instant.now()) > 0;
	}
	catch (DataIntegrityViolationException ex) {
	return false;
	}
	});
	return Boolean.TRUE.equals(result);
	}

Acquire fails silently with PostgreSQL and propagated back to:

spring-integration/spring-integration-jdbc/src/main/java/org/springframework/integration/jdbc/lock/JdbcLockRegistry.java

Lines 174 to 198 in 191f693

	public void lock() {
	this.delegate.lock();
	while (true) {
	try {
	while (!doLock()) {
	Thread.sleep(this.idleBetweenTries.toMillis());
	}
	break;
	}
	catch (TransientDataAccessException | TransactionTimedOutException | TransactionSystemException e) {
	// try again
	}
	catch (InterruptedException e) {
	/*
	* This method must be uninterruptible so catch and ignore
	* interrupts and only break out of the while loop when
	* we get the lock.
	*/
	}
	catch (Exception e) {
	this.delegate.unlock();
	rethrowAsLockException(e);
	}
	}
	}

and handled in the last clause:

catch (Exception e) {
  this.delegate.unlock();
  rethrowAsLockException(e);
}

with this exception:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [UPDATE LOCK
SET CLIENT_ID=?, CREATED_DATE=?
WHERE REGION=? AND LOCK_KEY=? AND (CLIENT_ID=? OR CREATED_DATE<?)
]; nested exception is org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of java.time.Instant. Use setObject() with an explicit Types value to specify the type to use.

Expected behavior

Should acquire the lock with PostgreSQL.

[artembilan]

Can't infer the SQL type to use for an instance of java.time.Instant

OK. Sounds like there is no automatic conversion from that type to temporal one on driver level.
Will fix it shortly using toEpochMilli()...

[artembilan]

Well, That doesn't work because toEpochMilli() is essentially a BIGINT, not a TIMESTAMP.
So, that is not OK.

How about to see if you can move your PostgreSQL server to the latest one?
According to the discussion here: https://stackoverflow.com/questions/25536969/spring-jdbc-postgres-sql-java-8-conversion-from-to-localdate

The fix has been done in the 9.4.1212:

I fixed my org.postgresql.util.PSQLException: Cant infer the SQL type to use for an instance of java.time.LocalDate. Use setObject() with an explicit Types value to specify the type to use exception updating my postgres JDBC from 9.4 Build 1206 to 9.4.1212 version.

BTW, does it work with the current Spring Integration 6.0.5 ?
We do have there a logic like this:

LocalDateTime.now(ZoneOffset.UTC)

Yeah... Probably if that works, and even if Instant works with the latest PostgreSQL, we just bring that LocalDateTime back and that's it.
We just cannot be sure what server is in use and follow Postel's Law as much as possible 🤷

[artembilan]

Looks like the driver doesn't support java.time.Instant: https://tada.github.io/pljava/use/datetime.html.

Therefore my assumption about bringing LocalDateTime logic should be correct.

[agebhar1]

@artembilan 6.0.5 is working fine with the LocalDateTime.now(ZoneOffset.UTC) implementation