Ability to use Heroku's pgbouncer

[chrisdrackett]

Heroku has a pgbouncer that is one line to install (https://devcenter.heroku.com/articles/postgres-connection-pooling).

Currently its options do not seem to line up 100% with the requirements of prisma and it isn't possible to change them.

server_reset_query = DISCARD ALL
 server_reset_query_always = 0

When running we see errors like the following:

2020-04-02T17:29:11+00:00 app[postgres.13791]: [AMBER] [11-1]  sql_error_code = 42P05 ERROR:  prepared statement "s3" already exists
2020-04-02T17:29:11+00:00 app[postgres.13791]: [AMBER] [11-2]  sql_error_code = 42P05 STATEMENT:  UPDATE "public"."Log" SET "ownerId" = $1, "type" = $2, "id" = $3, "updatedAt" = $4, "createdAt" = $5, "date" = $6 WHERE "public"."Log"."id" IN ($7)

Not sure if this would be possible to support, but it would be nice to use this vs. setting up and running our own instance of pgbouncer if at all possible!

[pimeys]

You cannot run Prisma without setting the options correctly with pgBouncer.

[pimeys]

I made a fix for Heroku

heroku/heroku-buildpack-pgbouncer#129

But they haven't even looked at my pull request...

Maybe try to use the patched buildpack?

[chrisdrackett]

ok, I'll look into it. Our app itself isn't hosted on Heroku, just our DB is so I would need to find some way to send the modified database url to our app. Not sure if that is possible. I might also need to host our own copy of pgbouncer, but I'm also not sure how to do that :D

[janpio]

We implemented a new PgBouncer mode in prisma/prisma#2520 that is activated by adding ?pgbouncer=true (or &pgbouncer=true of course) to your connection string. Available in the current alpha, and the next normal beta release 🚀

We will now try to test this with Heroku in our e2e tests to make sure this indeed works and will keep working long term: prisma/ecosystem-tests#385

If you try it out yourself @chrisdrackett, let us know if it works or not - until then we assume it does ;)

[chrisdrackett]

@janpio We already moved our server to Heroku from next so this no longer applies to us ;)

[janpio]

We'll have to find someone else to test this then :D