pgx doesn't encode json.RawMessage params

[chzhongsending]

Describe the bug
Unlike the pq library, the pgx stdlib doesn't encode types like json.RawMessage arguments.
type json.RawMessage []byte is defined in ecndoding/json.

To Reproduce

1. Execute a SQL with positional argument(s), and pass an json.RawMessage as its args.

package main

import (
	"database/sql"
	"encoding/json"
	_ "github.com/jackc/pgx/v5/stdlib"
	_ "github.com/lib/pq"
	"math/rand"
	"testing"
)

func prepareDBDriver(driver string) (*sql.DB, error) {
	db, err := sql.Open(driver, "postgres://postgres:123456@127.0.0.1:5432/playground?sslmode=disable")
	if err != nil {
		return nil, err
	}
	_, err = db.Exec(`CREATE TABLE IF NOT EXISTS value_test(
		id SERIAL PRIMARY KEY,
		text TEXT)`)
	if err != nil {
		return nil, err
	}

	return db, err
}

type point struct {
	X, Y, Z int
}

func testEncodeJsonRawMessage(driver string, t *testing.T) {
	db, err := prepareDBDriver(driver)
	if err != nil {
		t.Errorf("failed to initialize database: %v", err)
		return
	}
	defer func(db *sql.DB) {
		_ = db.Close()
	}(db)
	p := point{
		X: rand.Intn(10),
		Y: rand.Intn(10),
		Z: 0,
	}
	pb, err := json.Marshal(&p)
	if err != nil {
		t.Errorf("failed to marshal %+v: %v", p, err)
		return
	}
	pr := json.RawMessage(pb)
	_, err = db.Exec(`INSERT INTO value_test(text) VALUES($1)`, pr)
	if err != nil {
		t.Errorf("failed to insert %s: %v", pr, err)
		return
	}
}

func TestPqEncodeJsonRawMessage(t *testing.T) {
	testEncodeJsonRawMessage("postgres", t)
}

func TestPgxEncodeJsonRawMessage(t *testing.T) {
	testEncodeJsonRawMessage("pgx", t)
}

Expected behavior

• All tests above should pass.
  That is, pgx should encode commonly used built-in go library types correctly.
  Or at least, any type with underlying type of core types, like string, []byte should be encoded correctly without extra configuration.

Actual behavior

• TestPqEncodeJsonRawMessage: PASS, but TestPgxEncodeJsonRawMessage FAILED
  Program complains:

pgx_encode_test.go:53: failed to insert {"X":7,"Y":9,"Z":0}: failed to encode args[0]: unable to encode json.RawMessage{0x7b, 0x22, 0x58, 0x22, 0x3a, 0x37, 0x2c, 0x22, 0x59, 0x22, 0x3a, 0x39, 0x2c, 0x22, 0x5a, 0x22, 0x3a, 0x30, 0x7d} into text format for text (OID 25): cannot find encode plan

Version

• Go: $ go version -> go1.21.0 linux/amd64
• PostgreSQL: $ psql --no-psqlrc --tuples-only -c 'select version()' ->PostgreSQL 12.16 (Debian 12.16-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
• pgx: $ grep 'github.com/jackc/pgx/v[0-9]' go.mod -> v5.3.1

Additional context
n/a

[jackc]

json.RawMessage encodes to json or jsonb types. And types with the underlying type of string worked as expected. json.RawMessage and its ilk are tricky because the underlying type is a slice. But we want to treat that slice as a scalar value instead of a slice.

Fixed in 45f807f.

[kamatama41]

Hi, I have a problem when encoding json.RawMessage with the exec mode simple_protocol.

With simple_protocol, a json.RawMessage value is encoded to a text like \x7b2258223a332c2259223a352c225a223a307d

It can be reproduced with the following test.

package main

import (
	"database/sql"
	"encoding/json"
	_ "github.com/jackc/pgx/v5/stdlib"
	"math/rand"
	"testing"
)

func prepareDBDriver(driver string) (*sql.DB, error) {
	db, err := sql.Open(driver, "postgres://postgres:123456@127.0.0.1:5432/playground?sslmode=disable&default_query_exec_mode=simple_protocol")
	if err != nil {
		return nil, err
	}
	_, err = db.Exec(`CREATE TABLE IF NOT EXISTS value_test(
		id SERIAL PRIMARY KEY,
		text TEXT)`)
	if err != nil {
		return nil, err
	}

	return db, err
}

type point struct {
	X, Y, Z int
}

func testEncodeJsonRawMessage(driver string, t *testing.T) {
	db, err := prepareDBDriver(driver)
	if err != nil {
		t.Errorf("failed to initialize database: %v", err)
		return
	}
	defer func(db *sql.DB) {
		_ = db.Close()
	}(db)
	p := point{
		X: rand.Intn(10),
		Y: rand.Intn(10),
		Z: 0,
	}
	pb, err := json.Marshal(&p)
	if err != nil {
		t.Errorf("failed to marshal %+v: %v", p, err)
		return
	}
	pr := json.RawMessage(pb)
	_, err = db.Exec(`INSERT INTO value_test(text) VALUES($1)`, pr)
	if err != nil {
		t.Errorf("failed to insert %s: %v", pr, err)
		return
	}
}

func TestPgxEncodeJsonRawMessage(t *testing.T) {
	testEncodeJsonRawMessage("pgx", t)
}

• Go: $ go version -> go version go1.22.0 darwin/arm64
• PostgreSQL: $ psql --no-psqlrc --tuples-only -c 'select version()' -> PostgreSQL 13.13 on aarch64-unknown-linux-musl, compiled by gcc (Alpine 13.2.1_git20231014) 13.2.1 20231014, 64-bit
• pgx: $ grep 'github.com/jackc/pgx/v[0-9]' go.mod -> github.com/jackc/pgx/v5 v5.5.3

[jackc]

@kamatama41 Fixed in 88dfc22. The fundamental issue was that json.RawMessage is a []byte. In absence of other information, pgx considers []byte to be bytea / binary data. This commit registered json.RawMessage as a PostgreSQL json type in cases such as the simple protocol when the actual PostgreSQL type is unavailable.

[lkp-k]

Just want to say THANK YOU to @jackc

I was using pgx 5.4.3 and ran into this issue. I spent almost an hour trying to figure out looking at diffs and realized maybe it could be due to the fact we switched to pgbouncer and simple protocol. On 5.5.5 everything works again.

[C-Deck]

Glad to see this fixed. I was doing this in my code, so it'll be nice to remove it. It felt out of place.