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
- Proof of concept: River operating with pgx simple protocol We had an issue opened a little while back (#171) wherein it turned out that River couldn't operate with pgx's simple protocol turned on, wh... — committed to riverqueue/river by brandur 3 years ago
- Proof of concept: River operating with pgx simple protocol We had an issue opened a little while back (#171) wherein it turned out that River couldn't operate with pgx's simple protocol turned on, wh... — committed to riverqueue/river by brandur 3 years ago
@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 saysDefaultQueryExecModeis incompatible with PGBouncer.I’ve also run into this issue. Bun is the reason for using
QueryExecModeSimpleProtocolbut 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.@brandur Awesome. Thank you!
Odd, so the problem is that binary data being sent:
Coming from
'\x7b7d'which is{}in binary.I can easily repro that:
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?