A little addictive story after updating the JDBC driver for PostgreSQL

background

--Spring Boot 1.5 application

PostgreSQL 9.6 --DB access is JDBC

The JDBC driver of PostgreSQL creates INSERT statements for the number of records even if multiple rows are INSERTed with batchUpdate (). This is slow, so I want to BULK INSERT for speed.

An option called reWriteBatchedInserts has been added from the driver 9.4.1209, and setting this will result in BULK INSERT, but there is a problem that invalid SQL is generated when using ʻINSERT ON CONFLICT. , This has been resolved in 42.2.2. However, it is 9.4.1212` to be introduced in Spring Boot 1.5 series. .. ..

So I decided to upgrade the driver version.

I tried to upgrade

Add the following to pom.xml to install the latest 42.2.4.

`pom.xml`


  <properties>
    ...
    <postgresql.version>42.2.4</postgresql.version>
    ...
  </properties>

Only this. It's easy.

However.

The test threw an error when building.

Expected :2015-12-15 23:30:59.999999
Actual   :2015-12-15 23:31:00.0

If you revert the driver version, the error disappears, so it is certain that it is the effect of the version upgrade.

Investigation

The reason why the test fails is that the time stamp has been moved up. In order to check which of Read and Write is causing the problem, I looked into the DB data without going through this driver, and found that the carried-up record was registered, so it seems that it is already invalid when writing. It seems that the data is.

Why is it so.

When I googled this and that, I found a certain Issue. According to this, it is a problem that occurs in version 42.1.1 or later, and it has been fixed in 42.2.3. that? Then the problem has been fixed ...?

Solution

This issue is a big deal, and in version 42.1.1 and later, the nanoseconds of java.sql.Timestamp are rounded off when converting to DB Timestamp which is accurate to microseconds. It turns out that earlier versions ignored nanoseconds.

So, when I check the configuration for testing ...

public Clock clock() {
    return Clock.fixed(ZonedDateTime.of(2015, 12, 15, 23, 30, 59, 999999999, ZoneId.systemDefault()).toInstant(), ZoneId.systemDefault());
}

I filled up to nanoseconds!

So, I solved it by changing 99999999999 to 999999000.