jmoiron / sqlx

general purpose extensions to golang's database/sql
http://jmoiron.github.io/sqlx/
MIT License
16.3k stars 1.09k forks source link

NamedExec broken utf8 encoding where Exec succeeds #949

Closed JamieClayton7 closed 1 month ago

JamieClayton7 commented 1 month ago

I'm attempting to batch upsert some data using NamedExec & named struct parameters but receiving the error:

pq: invalid byte sequence for encoding "UTF8": 0xc3

Now, we're using Golang here so all inputs to pq, I assume, should be UTF8 encoded by default. I'd say there is something wrong with the data I'm inputting (zero byte runes, invalid utf8 or whatever) but if I insert the same data using single operation Exec, it doesn't fail.

Succeeding example:

_, err := r.client.Exec(`
    INSERT INTO table(id, val1, val2, val3) VALUES ($1, $2, $3, $4)
    ON CONFLICT (lookup_id) DO UPDATE
    SET  val1 = $2, val2 = $3, val3 = $4
`, id, val1, val2, val3) // val3 contains latin chars
if err != nil {
    log.Println(a)
    return err
}

Failing example:

_, err := r.client.NamedExec(`
    INSERT INTO table(id, val1, val2, val3) VALUES (:id, :val1, :val2, :val3)
    ON CONFLICT (lookup_id) DO UPDATE
    SET  val1 = :val1, val2 = :val2, val3 = :val3
`, records) // records of type []value
if err != nil {
    log.Println(a)
 }

type value struct {
  Id string `db:"id"`
  Value1 string `db:"val1"`
  Value2 string `db:"val2"`
  Value3 string `db:"val3"` // contains latin chars
}

I'd expect both of these examples to succeed. Postgres database server encoding is UTF8 (as is client encoding as per the pq driver specification).

JamieClayton7 commented 1 month ago

Exec does not actually work with the same file, I have added a step to the program alongside the NamedExec refactor that slices val3 to 50 characters. It seems like I'm breaking a multi-char rune sequence for a special char in half by doing that.