Does this library supports binary_parameters ?

[asafyish]

Hi,

I would like to use this package with pgBouncer in transaction mode but it seems that if I will try to use prepared statements it won't work, while I am ok with that (simply not use prepared statements), there are additional packages that use this library (https://github.com/graphile/worker for example), and they are using prepared statements, and therefor won't work correctly. If I understand correctly, supporting binary_parameters will allow this library, pgBouncer and prepared statements to play along.

Also, it seems like a nice performance improvement.

[boromisp]

The binary_parameters seems to be specific to one of the golang postgres drivers.
It looks like the actual "binary" part of that function is not necessary for what you want, that is just an implementation detail of their single round-trip, unnamed prepared statement.

It might just work with node-postgres as-is, have you tried it?

[boromisp]

I've read somewhere that PQExecParams from libpq does work with pgBouncer transaction mode.
The main difference between that and the node-postgres implementation seems to be buffering: libpq buffers the all the messages belonging to the query, while node-postgres sends each message one by one. I don't think this should matter with TCP, but I haven't looked at the internals of pgBouncer.

[lastmjs]

Having something like this might allow node-postgres to work with the AWS RDS Proxy as well. Seems any kind of transaction pooling will not work with node-postgres's prepared statements as they are, that's my hunch.

[lastmjs]

node-postgres definitely does not handle transaction pooling well with prepared statements. @boromisp I am very interested having single round-tripe unnamed prepared statements in node-postgres. Is this currently possible by using the native libpq bindings with node-postgres? Would I be able to configure the binary setting for libpq from node-postgres?

[boromisp]

The node-postgres implementation is single round-trip.
The issue discussed here is that the golang driver had to wait for the responses for some of the intermediate messages, but node-postgres doesn't do that.

[lastmjs]

I'm going to attempt to get transaction pooling working with node-postgres and pgBouncer. Just to clarify, does node-postgres support unnamed prepared statements?

[boromisp]

Yes, the parameterized queries are usually unnamed prepared statements. The library creates a prepared statement, binds the parameters and exeutes it in a single step.

[NeoPhi]

@boromisp It looks like on

node-postgres/packages/pg/lib/query.js

Line 54 in 95b5daa

return this.values.length > 0

if values were passed in it will do a prepare which to me seems to imply two round trips.

[boromisp]

If you follow the implementation of prepare:

node-postgres/packages/pg/lib/query.js

Line 180 in 95b5daa

prepare(connection) {

you can see that it doesn't wait for a response between messages, but prepares the statement, binds the parameters and executes the query synchronously. I assume that this counts as a single round-trip. Whether or not it is compatible with transaction pooling I haven't tested.

[eugene-kim]

I'm going to attempt to get transaction pooling working with node-postgres and pgBouncer. Just to clarify, does node-postgres support unnamed prepared statements?

@lastmjs any luck on this your efforts?

[lastmjs]

It did not work. It's been a few weeks now, so I'm a bit foggy on the details. I tried session and transaction pooling with pgBouncer, and though requests were handled just fine, the pool did not act as a pool. I saw no reason to believe it was providing any kind of benefit over connecting without the pool.