Prisma: DB errors when using Prisma2 with pgBouncer connection pools

[iherger]

I am trying out pgBouncer connection pools (transaction mode) on managed Postgresql on DigitalOcean.

I am getting errors similar to:

[GraphQL error]: Message: Error in Photonselect,user: 
Error: Reason: Error in connector: Error querying the database: db error: ERROR: prepared statement "s667" already exists

This occurs only with connection pooling, not with direct db connections.

The errors occur under the following conditions:

• with versions[email protected] as well as latest alpha
• with deployments of the GraphQL server both on zeit-now and locally
• with Prisma2 starter project (GraphQL starter), when running yarn seed

Easiest way to reproduce:

• set up a managed postgresql db on DigitalOcean
• prisma2 init simple typescript GraphQL starter
• run yarn seed

[pantharshit00]

Hi @iherger

I was even unable to migrate the database on DO hosted instance. I have reported that issue here: https://github.com/prisma/prisma2/issues/562

Can you please tell me how you were able to migrate the database? Marking this as a reproduction available for now.

[iherger]

@pantharshit00, migration fails for me, if I am using underscores in the schema name, e.g. issue_556 fails whereas issue556 works.

[pantharshit00]

I am still unable to perform the migration 🙁

[divyenduz]

I installed pgbouncer on my mac but I am unable to reproduce this, would it be possible to provide a minimal reproduction?

Maybe bouncer + current state of your DB or something specific in your schema makes that error possible?

[pantharshit00]

@iherger Is this issue still relevant? Are you still able to reproduce this?

[timsuchanek]

I can reproduce this.
From what I understand from @pimeys we can't support pgBouncer right now, as it doesn't support transactions, which we need e.g. for nested mutations.

[pimeys]

We don't support pgBouncer right now due to it having limited capabilities compared to a direct connection to PostgreSQL. I've used pgBouncer quite a lot in my life and almost always needed to go back to direct connections due to it not working in certain cases.

This here seems to be a problem with prepared statements. So what Prisma does it prepares the statement first so the database can cache and save execution time without needing to parse the SQL for every query. This seems not to work with pgBouncer.

There is definitely a need for research what we can and cannot do with the pool, please @sorenbs and @idan take this ticket into product discussion and research.

[darknoon]

I am also trying to get this to work… what the heck are we supposed to do with serverless functions and our database? pgbouncer seems like the only way to not run out of connections when there are an unlimited number of concurrent lambdas.

[pantharshit00]

@darknoon We are working on documentation for connection management for lambda: https://github.com/prisma/prisma2/issues/942

AWS just also released AWS RDS proxy that could be a better solution here: https://aws.amazon.com/about-aws/whats-new/2019/12/amazon-rds-proxy-available-in-preview/

[seanemmer]

An earlier discussion of serverless listed PostGres and pgBouncer as a potential solution:

https://github.com/prisma/prisma2/issues/276

Can you comment as to whether pgBouncer support is on the roadmap and whether RDS proxy will be supported?

As noted above, this has important implications for the memory cost of implementing Prisma on serverless.

[matthewmueller]

@darknoon unrelated question: how were you able to unassign someone? 😅

[darknoon]

@matthewmueller, it wasn't intentional! must have hit a keyboard shortcut accidentally?

[darknoon]

I'm currently looking at AWS Aurora Serverless, since it's theoretically Postgres-compatible. Going to see if it works with prisma2. Honestly, I just want y'all to list supported configurations since I just want to pick something relatively cheap for now.

[janpio]

@matthewmueller, it wasn't intentional! must have hit a keyboard shortcut accidentally?

@darknoon We figured out that this "unassigning" is a display bug on Github's side, nothing you did. You just were the first user replying here after some changes in our GitHub org which caused this unassigning to happen - and your comment created the timeline entry. 🙏

[Linktheoriginal]

Similar to specs/#386 and prisma2/#1438, we have been attempting to use Prisma2 against a DigitalOcean PostgreSQL 11 database service.

Operating without pgbouncer, the application initially runs fine, but eventually runs out of connections. When this happens, the query engine also seems to spin and use all available CPU.
DigitalOcean offers pgbouncer-based pools with transaction, session, and statement settings. With a transactional pool in place, we receive an error along the lines of "SqlState 26000, prepared statement 's1599' does not exist."

After reading the related issues on the repo, we tested a session pool but it still ran out of connections eventually.

We are using &connection_limit=15 in our connection string, but are still seeing the issue.
We're currently using preview20.3, and planning to update to preview22 once it's out. We're hoping that using the flag discussed in specs/#386 plus a transactional pool will solve the issue.

We're also exploring moving to mysql, but it is going to require additional work to get around some of the current limitations with how strings are handled in mysql.

[pantharshit00]

@Linktheoriginal PgBouncer support was added to the engine via https://github.com/prisma/prisma-engines/pull/403

We are working on exposing it via the client: https://github.com/prisma/specs/issues/386

So please wait while we are working on this.

[Linktheoriginal]

Working with @matthewmueller, we tried setting the --always-force-transactions flag in the engine invocation, but we're still receiving a prepared statement error. This was on preview21, engine commit 4f8eb5bd9628db47402dc855ad0b6c7cfc915d46.

[matthewmueller]

I'm assuming you hacked the generated code to add this flag to the engine invokation?

It looks like the flag is: --always_force_transactions, can you try that?

https://github.com/prisma/prisma-engines/pull/403#issue-365425419

[pimeys]

We also run all our tests with pgbouncer on transaction mode. Only query engine works, migrations and introspection not.

-------- Original Message --------
On Feb 14, 2020, 18:25, Matthew Mueller wrote:

I'm assuming you hacked the generated code to add this flag to the engine invokation?

It looks like the flag is: --always_force_transactions, can you try that?

prisma/prisma-engines#403 (comment)

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub, or unsubscribe.

[Linktheoriginal]

Correct, we added it to the generated code, in three places where it's being invoked. I've double checked with --always_force_transactions instead of --always-force-transactions, and I'm still getting a prepared statement error during query.

[Linktheoriginal]

Spent some additional time on this - there was a fourth place where it looks like the query server is started, and --always_force_transactions needed to be added there. With this in place, it seems like the first query executed works, but every query afterwards fails with "Error in connector: Error creating a database connection."

This is still on preview21 right now.

[oem2]

Similar to specs/#386 and prisma2/#1438, we have been attempting to use Prisma2 against a DigitalOcean PostgreSQL 11 database service.

Operating without pgbouncer, the application initially runs fine, but eventually runs out of connections. When this happens, the query engine also seems to spin and use all available CPU.
DigitalOcean offers pgbouncer-based pools with transaction, session, and statement settings. With a transactional pool in place, we receive an error along the lines of "SqlState 26000, prepared statement 's1599' does not exist."

After reading the related issues on the repo, we tested a session pool but it still ran out of connections eventually.

We are using &connection_limit=15 in our connection string, but are still seeing the issue.
We're currently using preview20.3, and planning to update to preview22 once it's out. We're hoping that using the flag discussed in specs/#386 plus a transactional pool will solve the issue.

We're also exploring moving to mysql, but it is going to require additional work to get around some of the current limitations with how strings are handled in mysql.

Do you mind telling me how you got pooling to work on DO? I have been stuck on this for two days.

[Linktheoriginal]

Do you mind telling me how you got pooling to work on DO? I have been stuck on this for two days.

We did not. We found the source of the connections being used (a script that wasn't calling disconnect), and removed pooling from our configuration.

[chrisdrackett]

note that this seems to be the same on Heroku. I opened an issue here: https://github.com/prisma/prisma-client-js/issues/628

[janpio]

Everyone looking into pgBouncer finding this issue, please take a look at https://github.com/prisma/prisma-client-js/issues/503 and test it out. Please provide feedback! This is not properly documented yet as we do not know if we got all the cases right and it really works with all pgBouncer systems. Thanks!

[janpio]

pgBouncer support is now part of Prisma and can be used. Until this is added to the official documentation, you can read about it on https://github.com/prisma/prisma-client-js/issues/503 🚀