JSONB query support with pgx/v4 and prefer_simple_protocol results in ERROR: invalid input syntax for type json (SQLSTATE 22P02)

[samavos]

Version

1.16.0

What happened?

Attempting a query with an input parameter of type jsonb not null, using driver pgx/v4, and with prefer_simple_protocol=true results in the query failing with:

ERROR: invalid input syntax for type json (SQLSTATE 22P02)

This is due to the query using pgtype.JSONB rather than *pgtype.JSONB, which has a workaround for this very case: https://github.com/jackc/pgx/blob/eeda0368e66fafed0a3db500108bdb87b657a88a/values.go#L40

See also: https://github.com/jackc/pgtype/issues/45

Relevant log output

No response

Database schema

create table something (data jsonb not null);

SQL queries

insert into something (data) values ($1);

Configuration

Use pgx/v4 driver.

Playground URL

No response

What operating system are you using?

Linux

What database engines are you using?

pgx/v4

What type of code are you generating?

Golang

[jinlongchen]

func (plan *encodePlanDriverValuer) Encode(value any, buf []byte) (newBuf []byte, err error) {
	dv := value.(driver.Valuer)
	if dv == nil {
		return nil, nil
	}
	v, err := dv.Value() // here
	if err != nil {
		return nil, err
	}
	if v == nil {
		return nil, nil
	}

	newBuf, err = plan.m.Encode(plan.oid, plan.formatCode, v, buf)
	if err == nil {
		return newBuf, nil
	}

	s, ok := v.(string)
	if !ok {
		return nil, err
	}

	var scannedValue any
	scanErr := plan.m.Scan(plan.oid, TextFormatCode, []byte(s), &scannedValue)
	if scanErr != nil {
		return nil, err
	}

	// Prevent infinite loop. We can't encode this. See https://github.com/jackc/pgx/issues/1331.
	if reflect.TypeOf(value) == reflect.TypeOf(scannedValue) {
		return nil, fmt.Errorf("tried to encode %v via encoding to text and scanning but failed due to receiving same type back", value)
	}

	var err2 error
	newBuf, err2 = plan.m.Encode(plan.oid, BinaryFormatCode, scannedValue, buf)
	if err2 != nil {
		return nil, err
	}

	return newBuf, nil
}

I think that the value should be determined by sql.Scanner.Value and should not be guessed by pgx. If I choose to use a struct in place of json.RawMessage, pgx will mistakenly translate the JSON string to '\x...'

[kyleconroy]

Is this still an issue with pgx/v5? I don't have a way to reproduce this issue, so if you're still seeing this with v5, please add a schema and query.

[samavos]

@kyleconroy I believe this is fine in pgx/v5, since it uses different types here. I'm in the process of migrating our uses to pgx/v5, so I believe this will eventually not be a problem for me personally (but may be for others still of course!)

[geekodour]

@kyleconroy still having this issue on v5

just spent 3+ hours with this, so dumping things here. Too bad I didn't see this issue before.

-- schema
CREATE TABLE event (
    id text PRIMARY KEY DEFAULT uuid_generate_v7 () CHECK (id <> ''),
    action jsonb NOT NULL,
    created_at timestamp DEFAULT NOW(),
);
-- query
INSERT INTO event (action)
VALUES ($1)
RETURNING
    id;

Then I am using sqlc to construct this query (only relevant snippet)

	params := db.InsertEventParams{
		Action:    []byte(`{}`),
	}

I've query logging on: the actual query (has some actual data)

time=2024-04-10T04:22:42.594+05:30 level=INFO msg=Query commandTag="INSERT 0 1" pid=610274 sql="-- name: InsertEvent :one\nINSERT INTO gc_event (match_id, project_id, action)\nVALUES ($1, $2, $3)\nRETURNING\n    id\n" args="[<nil> <nil> 7b7d]" time=4.126109ms

Error that I get:

  "message": "ERROR: invalid input syntax for type json (SQLSTATE 22P02)"

All of this disappears if I don't use pgx.QueryExecModeSimpleProtocol as the DefaultQueryExecMode

[Arttii]

Is there any fix for this when using SimpleProtocol with pg bouncer?