Prisma1: Error when deploying Prisma >= 1.31-beta - SQLException

[frandiox]

Describe the bug

I'm trying to update to datamodel v1.1. It worked in development environment (demo servers) and now wanted to deploy the latest version for staging environment. However, any version over 1.30.1 throws SQLException when deploying. See the full log at the end of the issue.

I'm deploying to Zeit Now v1 with a configuration similar to this: https://github.com/develomark/prisma-now (it was a Prisma tutorial if I don't remember wrong).

The DB is Postgres in Google Cloud Platform.

It works with 1.28, 1.30 and 1.30.1.
It crashes with 1.31-beta, 1.31, 1.32, 1.32.2 and 1.33-beta (that I tried).

Any ideas? Thanks!

Update: In fact, after deploying one of the problematic versions, the DB goes in error state and doesn't work even when connecting with other SQL clients like DataGrip. When trying to query some of the tables, it throws:

[53300] FATAL: remaining connection slots are reserved for non-replication superuser connections

That's fixed when deploying a previous version that works.

Additional context

Logs:

> Verifying instantiation in sfo1
> [0] Container started
> [0] No log level set, defaulting to INFO.
> [0]   at org.postgresql.Driver.makeConnection(Driver.java:452)
> [0]   at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)
> [0] org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections
> [0]   at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:430)
> [0]   at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
> [0]   at slick.jdbc.JdbcBackend$BaseSession.<init>(JdbcBackend.scala:453)
> [0]   at slick.jdbc.DriverDataSource.getConnection(DriverDataSource.scala:101)
> [0] 
> [0]   at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:95)
> [0] WARNING: SQLException occurred while connecting to <xxxxx>
> [0]   at slick.jdbc.JdbcBackend$BaseSession.<init>(JdbcBackend.scala:453)
> [0] org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections
> [0]   at slick.jdbc.JdbcBackend$DatabaseDef.createSession(JdbcBackend.scala:46)
> [0] Container started
> [0] No log level set, defaulting to INFO.
> [0] May 05, 2019 1:02:06 PM org.postgresql.core.v3.ConnectionFactoryImpl log
> [0]   at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:95)
> [0]   at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
> [0]   at org.postgresql.Driver.makeConnection(Driver.java:452)
> [0]   at slick.jdbc.DriverDataSource.getConnection(DriverDataSource.scala:101)
> [0]   at slick.basic.BasicBackend$DatabaseDef.acquireSession$(BasicBackend.scala:248)
> [0]   at slick.jdbc.DataSourceJdbcDataSource.createConnection(JdbcDataSource.scala:68)
> [0]   at slick.jdbc.JdbcBackend$DatabaseDef.acquireSession(JdbcBackend.scala:37)
> [0]   at slick.jdbc.JdbcBackend$BaseSession.<init>(JdbcBackend.scala:453)
> [0] May 05, 2019 1:02:06 PM org.postgresql.Driver connect
> [0]   at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
> [0]   at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:341)
> [0]   at java.util.concurrent.FutureTask.run(FutureTask.java:266)
> [0]   at slick.jdbc.JdbcBackend$DatabaseDef.createSession(JdbcBackend.scala:37)
> [0]   at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
> [0]   at slick.jdbc.DriverDataSource.getConnection(DriverDataSource.scala:101)
> [0]   at slick.jdbc.JdbcBackend$BaseSession.<init>(JdbcBackend.scala:453)
> [0]   at slick.basic.BasicBackend$DatabaseDef$$anon$2.run(BasicBackend.scala:274)
> [0]   at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
> [0]   at slick.jdbc.DriverDataSource.getConnection(DriverDataSource.scala:101)
> ✔ Scaled 1 instance in sfo1 [43s]
> Success! Deployment ready

[pantharshit00]

@frandiox

Can you please share your PRISMA_CONFIG? Try changing connectionLimit in that to a small number. Also, make sure rawAccess is enabled.

This issue is hard to reproduce so please wait for https://github.com/prisma/prisma/pull/4490 if changing those doesn't fix this.

[frandiox]

@pantharshit00 Thanks a lot, I set connectionLimit: 2 (I guess that's the very minimum) and looks like it's working now (1.32). What would be a good number for this or what's the default? I'm not really sure how this can affect performance. I guess it will be fixed after #4490, seems quite related to this issue.

For the record, my config file looks like this:

managementApiSecret: PRISMA_MANAGEMENT_API_SECRET
port: 4466
databases:
    default:
        connector: postgres
        host: SQL_HOST
        port: 5432
        user: SQL_USER
        password: SQL_PASSWORD
        migrations: true
        active: true
        rawAccess: true
        connectionLimit: 2

rawAccess was already there and I just added connectionLimit.

[pantharshit00]

Yes, I think right now prisma is making too many connections to your DB which GCP is not liking.

Wait for that PR for now

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed in 10 days if no further activity occurs. Thank you for your contributions.

[frandiox]

Still relevant

[frandiox]

Update: Looks like Prisma >= 1.31 has increased the number of connections but it's still around 10, which shouldn't be a problem for GCP. The smallest instance of Postgres in GCP has about 25 available connection but at least 6 of them are reserved for GCP itself so it basically allows less than 20 connections. The main problem we were having is that looks like another Prisma instance was connected to the same DB, and 2 instances at the same time were reaching the connection limit. That other Prisma instance is perhaps related to a previous deployment in Now v1 which didn't close its connections. The solution was to filter connections by IP address and only allowing the latest Prisma server.

Thanks for the help @pantharshit00 and @divyenduz (on Slack)