JSONB issue with default_query_exec_mode=simple_protocol

[doranych]

Describe the bug
An "invalid input syntax for type json" error occurs when attempting to pass a []byte as an argument for a query.
Specifically, the error message is:

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

To Reproduce
Create a table with a jsonb field and attempt to insert values into it. The code below demonstrates the issue.

package main

import (
	"context"
	"database/sql"
	"encoding/json"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/stdlib"
	"github.com/jmoiron/sqlx"

	_ "github.com/lib/pq"
)

func main() {

	conf, err := pgx.ParseConfig("postgresql://test:test123456@localhost:5432/postgres?default_query_exec_mode=simple_protocol")
	// conf, err := pgx.ParseConfig("postgresql://test:test123456@localhost:5432/postgres")
	if err != nil {
		panic(err)
	}
	connector := stdlib.GetConnector(*conf)
	db := sqlx.NewDb(sql.OpenDB(connector), "pgx")
	defer db.Close()

	pqDb, err := sqlx.Connect("postgres", "postgresql://test:test123456@localhost:5432/postgres?binary_parameters=yes&sslmode=disable")
	if err != nil {
		panic(err)
	}
	defer pqDb.Close()

	_, err = db.Exec("CREATE TABLE IF NOT EXISTS test (id serial PRIMARY KEY, data jsonb NOT NULL);")
	if err != nil {
		panic(err)
	}
	b, _ := json.Marshal(map[string]int{"test": 1})
	query := `INSERT INTO test (data) VALUES ($1)`

	_, err = pqDb.ExecContext(context.Background(), query, append([]byte{0x01}, b...)) // this one is required for pq to work with binary_protocol=yes
	if err != nil {
		panic("pq_nonempty_map: " + err.Error())
	}

	_, err = db.ExecContext(context.Background(), query, append([]byte{0x01}, b...)) // it does not matter if I add 1 byte in fornt of b or not. result will be the same
	if err != nil {
		panic("pgx_nonempty_map: " + err.Error())
	}

	b, _ = json.Marshal(map[string]int{})

	_, err = pqDb.ExecContext(context.Background(), query,  append([]byte{0x01}, b...))
	if err != nil {
		panic("pq_empty_map: " + err.Error())
	}

	_, err = db.ExecContext(context.Background(), query, b)
	if err != nil {
		panic("pgx_empty_map: " + err.Error())
	}

}

Expected behaviour
Data should be inserted into the table without any issues, as it does when using lib/pq.

Actual behaviour
An error appears when inserting data into the table.

Version

• Go: go version go1.23.1 darwin/arm64
• PostgreSQL: PostgreSQL 14.10 (Debian 14.10-1.pgdg120+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
• pgx: v5.7.2

Additional context

• Postgres 14 behind pgbouncer for production environment
• Project is running using lib/pq+sqlx. It works as intended.

Local testing is done in an ordinary Docker container with PostgreSQL, but it is necessary to work with the simple protocol due to pgbouncer restrictions.

pgx/stdlib is working fine if default_query_exec_mode is not set to simple_protocol

[doranych]

it may or may not be related to #2020 or your proposal golang/go#67546

i can't define it yet

[jackc]

From the docs regarding QueryExecModeSimpleProtocol

Assume the PostgreSQL query parameter types based on the Go type of the arguments.

You are passing a []byte as a query argument. pgx has no way of knowing the underlying PostgreSQL type in the simple protocol. So pgx assumes string = text and []byte = bytea.

If you look at your PostgreSQL logs, you will see that the query actually sent to the server is something like:

insert into t (id, data) values ( '1' ,  '\x7b2274657374223a317d' )

Obviously, that is going to fail. With the simple protocol, you have to pass a string instead of a []byte for text encoded values.

The following example demonstrates it working:

package main

import (
	"context"
	"encoding/json"
	"fmt"
	"log"
	"os"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/stdlib"
	_ "github.com/jackc/pgx/v5/stdlib"
)

func main() {
	conf, err := pgx.ParseConfig(os.Getenv("DATABASE_URL"))
	if err != nil {
		panic(err)
	}
	conf.DefaultQueryExecMode = pgx.QueryExecModeSimpleProtocol

	db := stdlib.OpenDB(*conf)
	defer db.Close()

	_, err = db.ExecContext(context.Background(), "create temporary table t (id int, data jsonb)")
	if err != nil {
		log.Fatal(err)
	}

	data, err := json.Marshal(map[string]int{"test": 1})
	if err != nil {
		log.Fatal(err)
	}

	_, err = db.ExecContext(context.Background(), "insert into t (id, data) values ($1, $2)", 1, string(data))
	if err != nil {
		log.Fatal(err)
	}

	var id int32
	var dataBuf []byte

	err = db.QueryRow("select * from t").Scan(&id, &dataBuf)
	if err != nil {
		log.Fatal(err)
	}

	fmt.Println(id, string(dataBuf))
}

I've also updated the documentation to call out []byte in the documentation of QueryExecModeSimpleProtocol in 5f33ee5.