Upgrading postgres JDBC driver to 42.3.0 breaks big decimal read after 5 reads (only with Hikari)

[NikolayMetchev]

Describe the issue
f you have a numeric column in a postgres table with a value of 20000 and you read it 5 times on the 5th read it will read it as 2 instead of 20000.

This only breaks if you use the postgres jdbc driver version 42.3.0. If you revert to an older JDBC driver for postgres the bug doesn't manifest.

If you use pure JDBC and avoid hikari altogether the bug also doesn't reproduce.

Changing the version of postgres server also seems to have no affect on this bug.

Driver Version?
42.3.0

Java Version?
11

OS Version?
MacOS/Linuix

PostgreSQL Version?
10.15-13.4

To Reproduce
Please find attached a project with a unit test

Expected behaviour
postgresBug.zip
Every read should result in 20000

Logs
N/A

I have reported this to Hikari as well: brettwooldridge/HikariCP#1865

[davecramer]

Ya, we switch to binary after 5 uses of the statement. Makes sense... Thanks for the report

[NikolayMetchev]

Ya, we switch to binary after 5 uses of the statement. Makes sense... Thanks for the report

Could this be the culprit: a417307

[davecramer]

Quick way to find out... I'll be able to give you something to test in a minute

[davecramer]

hmmm looks like that class was completely rewritten. It might take a bit longer to sort.
@bokken ?

[NikolayMetchev]

You should be able to reproduce it by running the attached test. Please let me know if it is unclear.

[davecramer]

Thx. running embedded test messes with my system, but I will figure it out

[davecramer]

Why are you using prepareCall ??? (Just curious. I've never seen that before)

[NikolayMetchev]

Mistake on my part. Changing to prepareStatement results in the same failure.

[bokken]

Is this only an issue when you explicitly set scale (to 20)?

[NikolayMetchev]

No, I am setting the scale so that the comparison of BigDecimal doesn't fail in the test.

[NikolayMetchev]

Although actually if I change the type of the column to be numeric without explicit scale the test does pass...
So setting the scale explicitly in the DB seems to be the culprit.

[davecramer]

On Wed, Oct 27, 2021, 12:07 PM Nikolay Metchev ***@***.***> wrote: Although actually if I change the type of the column to be numeric without explicit scale the test does pass... So setting the scale explicitly in the DB seems to be the culprit.

The pure jdbc test does not transfer in binary

…

— You are receiving this because you commented. Reply to this email directly, view it on GitHub <#2326 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AADDH5SUXRBQRPBYR4REGO3UJAWVNANCNFSM5G2SM5ZQ> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

[NikolayMetchev]

On Wed, Oct 27, 2021, 12:07 PM Nikolay Metchev @.***> wrote: Although actually if I change the type of the column to be numeric without explicit scale the test does pass... So setting the scale explicitly in the DB seems to be the culprit.
The pure jdbc test does not transfer in binary
…
— You are receiving this because you commented. Reply to this email directly, view it on GitHub <#2326 (comment)>, or unsubscribe https://github.com/notifications/unsubscribe-auth/AADDH5SUXRBQRPBYR4REGO3UJAWVNANCNFSM5G2SM5ZQ . Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

How do I force it to transfer in binary?

[davecramer]

prepareThreshold | Integer | 5 | Statement prepare threshold. A value of -1 stands for forceBinary

[NikolayMetchev]

I figured it out:

import com.zaxxer.hikari.HikariConfig
import com.zaxxer.hikari.HikariDataSource
import io.zonky.test.db.postgres.embedded.EmbeddedPostgres
import org.junit.jupiter.api.Test
import org.postgresql.jdbc.PgConnection
import java.math.BigDecimal
import java.sql.Connection

class PostgresBug {
    private val edb = EmbeddedPostgres.start()

    @Test
    fun runTestWithHikari() {
        val cfg = HikariConfig().also {
            it.dataSource = edb.postgresDatabase
        }
        val ds = HikariDataSource(cfg)
        runTest { ds.connection }
    }

    @Test
    fun runTestWithPureJDBC() {
        runTest { forceBinary ->
            val connection = edb.postgresDatabase.connection
            if (forceBinary) {
                (connection as PgConnection).forceBinary = true
            }
            connection
        }
    }

    private fun runTest(getConnection: (Boolean) -> Connection) {
        val twentyK = BigDecimal("20000")
        getConnection(false).use { conn ->
            conn.prepareStatement(
                """
                    create table test_table
                    (
                        numeric_column numeric(40,20) not null
                    );
                    INSERT INTO TEST_TABLE VALUES(20000);
                """
            ).execute()
        }

        repeat(100) { i ->
            getConnection(true).use { conn ->
                val rs = conn.prepareStatement("SELECT * FROM TEST_TABLE").executeQuery()
                rs.use {
                    rs.next()
                    val bigDecimal = rs.getBigDecimal(1)

                    assert(bigDecimal.compareTo(twentyK) == 0) { "$i, $bigDecimal != $twentyK" }
                }
            }
        }
    }
}

[NikolayMetchev]

With the above code both tests fail...

[bokken]

I can recreate with simple unit test of the binary decoding of numeric.
For now, disabling binary support for numeric (and numeric array) is probably best work around.

[NikolayMetchev]

My workaround was to revert to a previous version of the JDBC driver.

[pyranja]

We were also affected by this problem. Our table definitions do not set a scale or precision for NUMERIC/DECIMAL values though.

[davecramer]

@pyranja thx for the report. Until we figure this out, the best thing is to downgrade. I'm working on it.

[NikolayMetchev]

Does maven central have a way of removing that version. This bug is quite obscure and it caused a real production incident for us, so worth avoiding it for others.

[davecramer]

Not that I am aware of. The best we could do is release a version that doesn't have the problem

[davecramer]

Can people please check
pgjdbc v42.3.1-rc1 is ready for preview.

Git SHA: b3050e6
Staging repository: https://oss.sonatype.org/content/repositories/orgpostgresql-1287

[faespsenar]

Unfortunately PostgreSQL JDBC 42.3.1 is causing connection leaks with Hikari and C3p0

[davecramer]

On Thu, Nov 25, 2021, 8:05 PM Dennys Santos Sobrinho < ***@***.***> wrote: Unfortunately PostgreSQL JDBC 42.3.1 is causing connection leaks with Hikari and C3p0 — You are receiving this because you modified the open/close state. Reply to this email directly, view it on GitHub <#2326 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AADDH5TQNNSCL7G2TRF7TLDUN3ML3ANCNFSM5G2SM5ZQ> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

Do you have any other information? This isn't enough to diagnose the issue

…