Can't use pgx5's QueryExecModeSimpleProtocol to avoid prepared statements when using connection pooling

[casparwylie]

Hi there,

We use PgBouncer as our connection pooler in transaction mode, which means we cannot use prepared statements. In our application, we disable prepared statements by using QueryExecModeSimpleProtocol. I believe this is a common setup given the limitation of transaction mode.

The problem is running the River client produces:

level=ERROR
msg="producer: Error fetching initial queue settings"
err="ERROR: invalid input syntax for type json (SQLSTATE 22P02)"

Which I believe is likely related to river_queue.metadata database column which is JSONB. According to pgx's comment on QueryExecModeSimpleProtocol:

 This is especially significant for []byte values. []byte values are encoded as PostgreSQL bytea. string must be used
instead for text type values including json and jsonb.

This means I'm pretty sure the following line is the issue:

river/producer.go

Line 292 in 6b9b622

Metadata: []byte("{}"),

I appreciate this isn't a direct problem with River, though I think River's pgx driver would ideally know which exec mode is being used and use the according types?

Any advice would be much appreciated! Thank you.

[brandur]

Last year I did a little stab at this to see if I could get QueryExecModeSimpleProtocol working:

#655 (comment)

Largely I roughly came to the same conclusion in that it was mainly []bytes that had to be changed to string.

@bgentry Any thoughts on whether we should try this? I have to say that moving everything to string seems a little icky, but the good news is that a big refactor like this will be 100 times easier to pull off with an LLM.

[bgentry]

@brandur I would definitely love to find a way to make this work and I'm not totally opposed to your proposal, though I am concerned about the possible perf hit & extra allocations. Another option is to try to detect the protocol mode which it seems pgx makes available to us and then dynamically adapt the jsonb args for it. I worked up an example of that here: 4f4b7b9

I've only lightly reviewed it myself after mostly vibe coding it but would love to get your thoughts on the general direction and whether it seems viable. Note that it runs the full driver suite in both regular and simple protocol modes to make sure everything is safe.

[bgentry]

I spent some time iterating on the approach, adjusting it to handle more edge cases, improve test coverage, and clean up the implementation. I think it's now a complete solution to this problem and opened #1155—let's maybe continue the conversation over there?