extra_float_digits incompatibility on PostgreSQL port (9005)

[yhojann-cl]

Company or project name

A single hello world project for test how to connect java spring boot on multiple sources (postgres as main database and clickhouse as logger events database).

Describe what's wrong

From Java Spring using a JDBC postgres driver to connect to 9005 port and works with flyway with a single SELECT 1 says:

Caused by: org.postgresql.util.PSQLException: ERROR: Query execution failed.
DB::Exception: Setting extra_float_digits is neither a builtin setting nor started with the prefix 'SQL_' registered for user-defined settings
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2733) ~[postgresql-42.7.5.jar:42.7.5]
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2420) ~[postgresql-42.7.5.jar:42.7.5]
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:372) ~[postgresql-42.7.5.jar:42.7.5]
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:330) ~[postgresql-42.7.5.jar:42.7.5]
        at org.postgresql.core.SetupQueryRunner.run(SetupQueryRunner.java:57) ~[postgresql-42.7.5.jar:42.7.5]
        at org.postgresql.core.v3.ConnectionFactoryImpl.runInitialQueries(ConnectionFactoryImpl.java:922) ~[postgresql-42.7.5.jar:42.7.5]
        at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:337) ~[postgresql-42.7.5.jar:42.7.5]
        at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:54) ~[postgresql-42.7.5.jar:42.7.5]
        at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:273) ~[postgresql-42.7.5.jar:42.7.5]
        at org.postgresql.Driver.makeConnection(Driver.java:446) ~[postgresql-42.7.5.jar:42.7.5]
        at org.postgresql.Driver.connect(Driver.java:298) ~[postgresql-42.7.5.jar:42.7.5]
        at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:137) ~[HikariCP-5.1.0.jar:na]
        at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:360) ~[HikariCP-5.1.0.jar:na]
        at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:202) ~[HikariCP-5.1.0.jar:na]
        at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:461) ~[HikariCP-5.1.0.jar:na]
        at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:550) ~[HikariCP-5.1.0.jar:na]
        ... 147 common frames omitted

My datasource:

package com.example.demo.config;

import javax.sql.DataSource;
import java.util.Properties;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.beans.factory.annotation.Qualifier;


/**
 * 
 */
@Configuration
public class ClickHouseDataSourceConfig {

    @Value("${app.clickhouse.datasource.url}")
    private String url;

    @Value("${app.clickhouse.datasource.username}")
    private String username;

    @Value("${app.clickhouse.datasource.password}")
    private String password;

    @Value("${app.clickhouse.datasource.driver-class-name}")
    private String driverClassName;

    @Bean(name="clickhouseDataSource")
    public DataSource clickhouseDataSource() {

        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(this.url);
        config.setUsername(this.username);
        config.setPassword(this.password);
        // config.setDriverClassName("com.clickhouse.jdbc.ClickHouseDriver");
        // config.setDriverClassName("com.github.housepower.jdbc.ClickHouseDriver");
        config.setDriverClassName(this.driverClassName);
        config.setMaximumPoolSize(5);
        config.setConnectionTimeout(30000);
        config.setAutoCommit(true); // ClickHouse does not support transactions
        config.setConnectionTestQuery("SELECT 1");

        Properties dsProps = new Properties();
        dsProps.setProperty("connectTimeout", "30");
        dsProps.setProperty("socketTimeout", "300");
        dsProps.setProperty("tcpKeepAlive", "true");

        // Incompatibilities with Clickhouse
        dsProps.setProperty("sendExtraFloatDigits", "false");
        dsProps.setProperty("extra_float_digits", "0");
        dsProps.setProperty("prepareThreshold", "0");
        dsProps.setProperty("preparedStatementCacheQueries", "0");
        dsProps.setProperty("preparedStatementCacheSizeMiB", "0");
        dsProps.setProperty("defaultRowFetchSize", "1000");
        dsProps.setProperty("binaryTransfer", "false");
        dsProps.setProperty("preferQueryMode", "simple");
        dsProps.setProperty("assumeMinServerVersion", "9.0");

        return new HikariDataSource(config);
    }

    @Bean(name="clickhouseJdbc")
    public NamedParameterJdbcTemplate clickhouseJdbc(
            @Qualifier("clickhouseDataSource") DataSource ds) {
        return new NamedParameterJdbcTemplate(ds);
    }
}

And the Flyway config:

package com.example.demo.config;

import java.util.Map;
import javax.sql.DataSource;
import org.flywaydb.core.Flyway;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;


/**
 * 
 */
@Configuration
public class ClickHouseFlywayConfig {

    @Value("${app.clickhouse.url}")
    private String clickhouseUrl;

    @Bean
    public Flyway clickhouseFlyway(@Qualifier("clickhouseDataSource") DataSource ds) {
        Flyway flyway = Flyway.configure()
        .dataSource(ds)
        .locations("classpath:db/migration/clickhouse")
        .baselineOnMigrate(true)
        .configuration(Map.of(
            "flyway.placeholders.dbname", this.extractDatabaseName(clickhouseUrl)
        ))
        .placeholderPrefix("${{")
        .placeholderSuffix("}}")
        .skipDefaultCallbacks(true) // Disable transactions
        .load();

        flyway.migrate();
        return flyway;
    }

    private String extractDatabaseName(String jdbcUrl) {
        if (jdbcUrl != null && jdbcUrl.contains("/")) {
            String[] parts = jdbcUrl.split("/");
            return parts[parts.length - 1].split("\\?")[0];
        }
        return "localhost";
    }
}

The source URL is jdbc:postgresql://cms_clickhouse:9005/dev?sslmode=disable.

Does it reproduce on the most recent release?

Yes

How to reproduce

Connect to ClickHouse using the datasource exposed in this issue.

Expected behavior

Connect to Clickhouse and run Flyway correctly.

Error message and/or stacktrace

Caused by: org.postgresql.util.PSQLException: ERROR: Query execution failed.
DB::Exception: Setting extra_float_digits is neither a builtin setting nor started with the prefix 'SQL_' registered for user-defined settings
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2733) ~[postgresql-42.7.5.jar:42.7.5]
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2420) ~[postgresql-42.7.5.jar:42.7.5]
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:372) ~[postgresql-42.7.5.jar:42.7.5]
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:330) ~[postgresql-42.7.5.jar:42.7.5]
        at org.postgresql.core.SetupQueryRunner.run(SetupQueryRunner.java:57) ~[postgresql-42.7.5.jar:42.7.5]
        at org.postgresql.core.v3.ConnectionFactoryImpl.runInitialQueries(ConnectionFactoryImpl.java:922) ~[postgresql-42.7.5.jar:42.7.5]
        at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:337) ~[postgresql-42.7.5.jar:42.7.5]
        at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:54) ~[postgresql-42.7.5.jar:42.7.5]
        at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:273) ~[postgresql-42.7.5.jar:42.7.5]
        at org.postgresql.Driver.makeConnection(Driver.java:446) ~[postgresql-42.7.5.jar:42.7.5]
        at org.postgresql.Driver.connect(Driver.java:298) ~[postgresql-42.7.5.jar:42.7.5]
        at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:137) ~[HikariCP-5.1.0.jar:na]
        at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:360) ~[HikariCP-5.1.0.jar:na]
        at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:202) ~[HikariCP-5.1.0.jar:na]
        at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:461) ~[HikariCP-5.1.0.jar:na]
        at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:550) ~[HikariCP-5.1.0.jar:na]
        ... 147 common frames omitted

Additional context

I use Docker image from dockerhub clickhouse/clickhouse-server:latest.