Skip to content

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

@samavos

Description

@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: jackc/pgtype#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

Activity

added
bugSomething isn't working
triageNew issues that hasn't been reviewed
on Feb 15, 2023
jinlongchen

jinlongchen commented on May 2, 2023

@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

kyleconroy commented on Sep 26, 2023

@kyleconroy
Collaborator

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

samavos commented on Sep 26, 2023

@samavos
Author

@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

geekodour commented on Apr 9, 2024

@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

Arttii commented on May 16, 2024

@Arttii

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

      Development

      No branches or pull requests

        Participants

        @kyleconroy@Arttii@jinlongchen@geekodour@samavos

        Issue actions

          JSONB query support with pgx/v4 and prefer_simple_protocol results in ERROR: invalid input syntax for type json (SQLSTATE 22P02) · Issue #2085 · sqlc-dev/sqlc