river: Enqueue problem (PostgreSQL 22P02)

Have been playing around with River and I get an error when adding a job to the queue. I define 3 string arg as job args.

The error I received is as follows: error getting unique job: ERROR: invalid input syntax for type json (SQLSTATE 22P02)

type ExampleJobArgs struct {
	Email  string `json:"email"`
	UserID string `json:"user_id"`
}

func (ExampleJobArgs) Kind() string {
	return "activation_email"
}

func (ExampleJobArgs) InsertOpts() river.InsertOpts {
	return river.InsertOpts{
		UniqueOpts: river.UniqueOpts{
			ByArgs:   true,
			ByPeriod: 4 * time.Hour,
		},
	}
}

// ...

type api struct { queue *river.Client[pgx.Tx] }

queue, err := river.NewClient(riverpgxv5.New(postgres), &river.Config{})
if err != nil {
	panic(err)
}

// ...

func (a *api) enqueueHandler(w http.ResponseWriter, r *http.Request) {
	ctx, cancel := context.WithCancel(r.Context())
	defer cancel()

	if _, err = a.queue.Insert(ctx, worker.ExampleJobArgs{
		Email:    "foo@bar.tld",
		UserID:  "1",
	}, nil); err != nil {
		// ...
	}

	// ...
}

My periodic jobs works correctly, but I only get this error when enqueueing from API.

About this issue

  • Original URL
  • State: closed
  • Created 3 years ago
  • Reactions: 1
  • Comments: 19 (10 by maintainers)

Commits related to this issue

Most upvoted comments

@lexor Nice find.

Did you see that PgBouncer now supports prepared statements if you’re using transaction pooling as of a few months ago in version 1.21.0? There is a chance that pgx comment about PgBouncer support is outdated now …

https://www.pgbouncer.org/faq.html#how-to-use-prepared-statements-with-transaction-pooling

I found the problem. I encounter this error when the default queue exec mode is selected as simple protocol or exec mode in the pgxpool config (config.ConnConfig.DefaultQueryExecMode = pgx.QueryExecModeSimpleProtocol). However pgx says DefaultQueryExecMode is incompatible with PGBouncer.

I’ve also run into this issue. Bun is the reason for using QueryExecModeSimpleProtocol but isn’t required to reproduce the error. Here’s a standalone unit test that uses https://github.com/testcontainers/testcontainers-go to provision a clean DB.

func Test_Pgx_QueryExecModeSimpleProtocol_QueueCreateOrSetUpdatedAt(t *testing.T) {
	ctx := context.Background()
	pg := must(postgres.Run(ctx, "postgres:alpine", postgres.BasicWaitStrategies()))
	defer testcontainers.TerminateContainer(pg)
	cfg := must(pgxpool.ParseConfig(pg.MustConnectionString(ctx, "sslmode=disable")))
	cfg.ConnConfig.DefaultQueryExecMode = pgx.QueryExecModeSimpleProtocol
	db := must(pgxpool.NewWithConfig(ctx, cfg))
	driver := riverpgxv5.New(db)
	migrator := must(rivermigrate.New(driver, nil))
	params := &riverdriver.QueueCreateOrSetUpdatedAtParams{Metadata: []byte("{}"), Name: river.QueueDefault}
	must(driver.GetExecutor().QueueCreateOrSetUpdatedAt(ctx, params))
	// BOOM!
}

func must[T any](val T, err error) T { if err != nil { panic(err) } return val }

@brandur Awesome. Thank you!

Odd, so the problem is that binary data being sent:

2024-01-22 14:14:25.253 UTC [8782] ERROR:  invalid input syntax for type json at character 225
2024-01-22 14:14:25.253 UTC [8782] DETAIL:  Token "\" is invalid.

Coming from '\x7b7d' which is {} in binary.

I can easily repro that:

platform_main_test=# select '\x7b7d'::jsonb;
ERROR:  invalid input syntax for type json
LINE 1: select '\x7b7d'::jsonb;
               ^
DETAIL:  Token "\" is invalid.
CONTEXT:  JSON data, line 1: \...

But I’d expect the encoded data to still be a non-binary {} at that point during insertion, and am not sure what would transform it that way. I’d guess something on the Go side of things?