Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Transaction pooling is not possible with prepared statements #2327

Open
lastmjs opened this issue Sep 2, 2020 · 3 comments
Open

Transaction pooling is not possible with prepared statements #2327

lastmjs opened this issue Sep 2, 2020 · 3 comments

Comments

@lastmjs
Copy link

lastmjs commented Sep 2, 2020

As I've been digging into proxying requests to postgres because of large serverless load, I've come to realize that transaction pooling with node-postgres does not seem possible for prepared statements. The two major types of proxies that I've studied are AWS RDS Proxy for Postgres and pgBouncer. Neither seem to support prepared statements in the way that node-postgres executes them. If I am wrong and there is any way to currently achieve transaction pooling with node-postgres, please let me know. This seems a major roadblock for certain types of highly-scalable applications, such as serverless applications.

There may be a possible solution as hinted to here: #2266 and in this blog post: https://blog.bullgare.com/2019/06/pgbouncer-and-prepared-statements/

Until something like the above is implemented in pg, I don't believe transaction pooling is possible with prepared statements. You might say just not to use prepared statements, but they are widely used and I believe quite vital. For example, I use the Graphback project which uses Knex under-the-hood. Knex uses prepared statements for every query. There is no easy way for me to remove these dependencies from my application, it would take a major architectural overhaul.

@why-el
Copy link

why-el commented Jun 8, 2022

Yes, this is true for any client library that enables prepared statements. That defeats the purpose of transaction pooling since a prepared statement needs and has its state stored with the entire session. There are workarounds and forks that make for instance pgbouncer work without prepared statements, but I don't know how simple it is to make it work here. However, the docs for Knex should mention this important fact, especially for people running their applications behind proxies as RDS Proxy where their connections will be pinned unnecessarily.

@AlexAurand
Copy link

I'm dealing with a similar issue. Is there any way to do one of the following?

  1. Tell pg not to use prepared statements
  2. Prevent connection pinning while using prepared statements

@ericalves
Copy link

Same problem.

I use the Loopback framework which uses loopback-connector-postgresql which uses node-postgres to make the connection.

I tested the Heimdall Data proxy, but it doesn't work well because transaction statements are enabled.

Any work around?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

4 participants