NUMERIC/DECIMAL without precision are truncated on fetch with latest postgres jdbc driver

[pyranja]

Expected behavior

We use tables with NUMERIC/DECIMAL values without precision. These are mapped to BigDecimal values.

Actual behavior

With the latest postgres jdbc driver 42.3.0, larger fetched values are being truncated:

• example: column value 20000.0 , fetched value in jooq result: 2.0
• this is not happening consistently, it seems that a few initial fetches work
• we were able to reproduce in a test with a local database:
  • running a query once shows no problem, conversion is correct
  • after ~5-10 query executions, values are truncated

Steps to reproduce the problem

• A complete set of DDL statements can help re-create the setup you're having
• An MCVE can be helpful to provide a complete reproduction case: https://github.com/jOOQ/jOOQ-mcve

I'm working on mitigating the fallout in our live system, not sure when/if I can share a reproducer.

Versions

• jOOQ: 3.15.3
• Java: amazoncorretto 11
• Database (include vendor): AWS Aurora PG 12 / local postgres v 12
• OS: AWS ECS Fargate / local Ubuntu
• JDBC Driver (include name if inofficial driver): postgres v42.3.0

[pyranja]

I'd have to extract a minimal example when I find time. But maybe this info already helps.
To give you an idea:

Looped query in the reproducer:

Output of the reproducer test:
After a few iterations the result from jooq shows a different value than the result set via plain jdbc.

[pyranja]

Perhaps related to pgjdbc/pgjdbc#1716

This addresses #1712. It is a breaking change, but if the correct getter
(getInt) is used for the column, there is no difference. Other getters may behave
differently.

🤔

[pyranja]

This has been reported as bug in the driver itself in pgjdbc/pgjdbc#2326
And it seems to be confirmed by the maintainers.

I'm closing this, as jooq seems to be the victim here :/ Apologies @lukaseder

[lukaseder]

Thanks a lot for your analysis and for documenting this here as well.