Document forceTransactions / pgBouncer

[janpio]

Prisma Client now has a flag forceTransactions (temporary name, might be changed) that can be set to true in the constructor, to enable a "transaction mode", where each query is wrapped in a transaction:

const client = new PrismaClient({
  forceTransactions: true,
})

This is useful for usage with pgBouncer, which in transaction mode only works correctly with Prisma Client when used in combination with this flag.

TODO paragraph of why using pgBouncer might be wanted.

We need to properly document this in the docs so we have a location we can point users to.

Information

1. pgBouncer must run in transaction mode. Other modes like session do not make any sense because - see comment below

2. pgBouncer must be configured in the following way:

   [pgbouncer]
   pool_mode = transaction
   server_reset_query = DEALLOCATE ALL
   server_reset_query_always = 1
   

   • How this can be configued depends on your hosting provider
     • Heroku: Usage with pg-bouncer #410 (comment) + waiting for Add PGBOUNCER_SERVER_RESET_QUERY_ALWAYS config heroku/heroku-buildpack-pgbouncer#129 to be merged for the third option

3. On some hosting providers there might be no options to set these options directly via UI or otherwise. There you can try to log in to the server manually, and do the following (untested for now TODO)
   
   More info: https://www.pgbouncer.org/usage.html#show-config

4. If you are using pgBouncer, but do not set the flag you will get an error message similar to this:

   ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Error { kind: Db, cause: Some(DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState("26000"), message: "prepared statement \"s0\" does not exist", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("prepare.c"), line: Some(505), routine: Some("FetchPreparedStatement") }) }) })
   

   TODO: We might want to catch this and output something useful (link to new docs page)

5. Migration Engine and Introspection Engine will not work with pgBouncer (in transaction mode)
   
   use the real database directly instead of the pgBouncer credentials to work around this

PR that merged the feature to Query Engine

prisma/prisma-engines#403

Related issues

prisma/prisma#556
prisma/prisma#1638
https://github.com/prisma/prisma-client-js/issues/228
#500
#410

[janpio]

Earlier notes from @pimeys on pgBouncer that we might want to incorporate into the documentation in some way:

---

Pgbouncer is a lightweight connection pooler for PostgreSQL. Instead of running
inside the application code, pgbouncer is a separate service offering support
for postgres line protocol and handling the pooling logic outside of
applications using the database.

Typically postgres supports only a certain amount of concurrent connections and
this limit can be reached quite fast when the service usage goes up.
This means the developer cannot just launch more instances of the service due to
the already running instances are reseving all the connections.

Transaction Mode

Now pgbouncer can be run in three different modes, them being session,
transaction and statement mode.

Session mode gives one connection per client, and is useful mainly to define
limits per team in an organization. For common users this mode doesn't allow any
advantages over just connecting to postgres directly.

Statement mode shares a connection with every query coming to the system. It
prevents certain useful features, such as transactions or prepared statements
and is not really useful with Prisma.

Transaction mode offers a connection for every transaction. With this mode and
certain other settings enabled, the prisma query is able to operate normally.
Needed settings for transaction mode:

• Prisma needs to be started in a mode that triggers a transaction in every
  case, even when just reading data. This allows us to use prepared statements
  in Prisma.
• Pgbouncer has flag SERVER_RESET_QUERY_ALWAYS set to 1 to delete prepared
  statements after each transaction is done. Otherwise the names of the prepared
  statements will clash between different clients and this will trigger
  nondeterministic crashes in Prisma.
• The other pgbouncer flag SERVER_RESET_QUERY should be set to DEALLOCATE ALL to just clean the prepared statements after the transaction is done. We
  don't need to clean everything and this will make the queries a tiny bit
  faster.

Downsides

• Lower perforamance due to extra roundtrips to BEGIN and COMMIT every
  single query we do even if we don't really need to run them in a transaction.

[janpio]

We should also make sure to clean up https://github.com/prisma/prisma2/blob/master/docs/faq.md#how-to-handle-connection-pooling-for-prisma-client-js-in-serverless-environments when we do this.

[sorenbs]

I think the forceTransactions name is fine.

I think the documentation of this option should focus on what it does, and only tangentially mention pgbouncer:

forceTransactions
Default: false
Type: boolean

Prisma Client often needs to perform multiple database queries in order to serve a single query. For performance reasons, these queries are not wrapped in a transaction.
Please note that writes, updates and deletes are always performed in a transaction.

Forcing reads to be done in a transaction can be useful when you use a proxy such as PgBouncer that have certain requirements for proper operation.

There should be separate documentation about how to use pgbouncer that mention this option.

[AndrewGold]

Quick question - is this option supported in Prisma 1, or is this only available in Prisma 2? We are having the pgBouncer issue.

[janpio]

This only refers to Prisma 2.x - for Prisma1 related questions you should open an issue at https://github.com/prisma/prisma1/

[janpio]

Internal Note: We need to do some more investigation before we actually document this: prisma/prisma#1638 (comment)

[janpio]

We changed the implementation, so this needs different documentation.