Insert fails with 'SQLSTATE 22P03: invalid scale in external "numeric" value' for certain BigDecimals

[megri]

This happens with the following client config:

val dbClient = Postgres
  .Client()
  .withCredentials(user = postgresUser, password = postgresPassword)
  .database(postgresDb)
  .withSessionPool
  .maxSize(1)
  .withBinaryParams(true)
  .withBinaryResults(true)
  .newRichClient(postgresHost)

(If .withBinaryParams/.withBinaryResults is set to false the inserts work)

Reproduction

Database setup:

create table foo (n numeric(6,2));

Scala:

def insertBigDecimal(bd: BigDecimal) =
  sql"""insert into foo (n) values ($bd)"""
    .exec(dbClient)
    .map(rc => s"inserted $rc rows")
    .handle{ case _ => "failed to insert" }

val a = BigDecimal(10)
val b = BigDecimal(10.0)
val c = BigDecimal("1E+1")
val d = BigDecimal("10")

val fa = insertBigDecimal(a)
val fb = insertBigDecimal(b)
val fc = insertBigDecimal(c)
val fd = insertBigDecimal(d)

println(a, Await.result(fa)) // (10,inserted 1 rows)
println(b, Await.result(fb)) // (10.0,inserted 1 rows)
println(c, Await.result(fc)) // (1E+1,failed to insert)
println(d, Await.result(fd)) // (10,inserted 1 rows)

[vkostyukov]

Hey @megri! I'm not super sure how actively this project is maintained but I have a merge access and would be happy to ship your fix in case if you have some time to work on this.

[megri]

Hey @vkostyukov, sorry to hear that, it feels like a nice library!

It seems like things are even worse than I initially thought — which kinda makes me feel like I may be doing something wrong..

Never the less, I forked this repo and unlocked the tests for BigDecimal to use the standard Arbitrary[BigDecimal] from scalacheck. This makes the suite UtilsSpec->"Numeric utils" fail for any negative BigDecimal passed (the one in Generators doesn't generate negative BigDecimals): Numerics.read-/writeNumeric doesn't seem to be symmetrical in its decoding/encoding of values.

edit: It should be noted that this is also the case for actually running an insert against a database: The action will fail for any negative BigDecimal. I have confirmed that it fails on at least writes, using PostgreSQL v10.5

I am now looking into rewriting these two methods. The documentation on postgres' binary format for numerics is sparse but I think I found something in this email thread (posted for future reference): https://www.postgresql.org/message-id/491DC5F3D279CD4EB4B157DDD62237F404E27FE9%40zipwire.esri.com

[jeremyrsmith]

@megri The best source of "documentation" for the binary formats is the source code itself. All of these routines are the result of attempting to port the encoder/decoder from postgres itself. It's possible that the decimal format has changed, or that it never actually worked right (it wouldn't surprise me as I remember it being pretty difficult to understand... see https://github.com/postgres/postgres/blob/master/src/backend/utils/adt/numeric.c#L803 and the other functions/structures in that file.

Apologies that I don't have time to maintain this project lately, but volunteers are always welcome!