prisma: API calls failing with `Error querying the database: db error: FATAL: remaining connection slots are reserved for non-replication superuser connections`
Bug description
I have been running my app on GCP that connects to a postgres instance also running in GCP, for the past few months. However, since yesterday, my REST API calls are only working intermittently.
I have been getting this error about 50% of the time. Other times, it seems to work fine: Error querying the database: db error: FATAL: remaining connection slots are reserved for non-replication superuser connections
I have been initializing one prisma instance per endpoint (total 8 endpoints) and till date have had zero issues using connection_limit=80&pool_timeout=50 on the DB string.
I am not sure if it is from have idle open connections that aren’t closing or if it is from my prisma queries or some other issue. Can you kindly advise me on how to fix this issue? Any help would be much appreciated. I am unable to reproduce this issue as it is quite sporadic. Thank you
Default
2021-05-17 17:10:00.614 GMTInvalid `prisma.instance.findMany()` invocation:
Default
2021-05-17 17:10:00.614 GMT
Default
2021-05-17 17:10:00.614 GMT
Default
2021-05-17 17:10:00.614 GMT Error querying the database: db error: FATAL: remaining connection slots are reserved for non-replication superuser connections
Default
2021-05-17 17:10:00.614 GMT at cb (/server/models/prisma-client/runtime/index.js:79166:17)
Default
2021-05-17 17:10:00.614 GMT at processTicksAndRejections (internal/process/task_queues.js:97:5)
Default
2021-05-17 17:10:00.614 GMT(node:14) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). To terminate the node process on unhandled promise rejection, use the CLI flag `--unhandled-rejections=strict` (see https://nodejs.org/api/cli.html#cli_unhandled_rejections_mode). (rejection id: 4)
Default
2021-05-17 17:12:00.721 GMT(node:14) UnhandledPromiseRejectionWarning: Error:
Default
2021-05-17 17:12:00.721 GMTInvalid `prisma.events.findMany()` invocation:
Default
2021-05-17 17:12:00.721 GMT
Here is an example of the an endpoint where prisma instance gets initiated:
require('../../config/config');
const express = require('express');
const router = express.Router();
const prisma = require('../models/prismaClient.js');
const sendEmailSummary = require('../functions/sendEmailSummary');
router.post('/', async (req, res) => {
await sendEmailSummary(prisma);
res.sendStatus(200);
});
module.exports = router;
And here is the prisma client:
require('../../config/config');
const { PrismaClient } = require('./prisma-client');
const url = process.env.DATABASE_URL;
let prisma;
if (process.env.NODE_ENV === "production") {
prisma = new PrismaClient({
datasources: { db: { url } },
})
} else {
if (!global.prisma) {
global.prisma = new PrismaClient({
datasources: { db: { url } },
})
}
prisma = global.prisma
}
module.exports = prisma;
How to reproduce
Expected behavior
Should be returning values from database
Prisma information
Environment & setup
- OS: MacOS
- Database: PSQL
- Node.js version: 14
- Prisma version: 2.18.0
Prisma Version
2.18.0
About this issue
- Original URL
- State: open
- Created 3 years ago
- Reactions: 8
- Comments: 36 (16 by maintainers)
any update on this? I’m currently having this issue using Prisma, Postgres and Digital Ocean.
Hi Jacky!
Sorry, it has been a few weeks since you commented. I figured I would share some updates we had though.
Originally, I do not think we had the pooling set up properly. We were just connecting directly to our db: “postgresql//…” Then I was made aware of Prisma’s Data Proxy service. THIS was the connection pooling service we were looking for. Now we would connect to the pooling service: “prisma//…” So, we had set that up. Unfortunately, we were still having problems. However this time it was different. Instead of it being the connection limit, we were getting 504 errors because our requests were timing out. After reaching out to Prisma, in turned out it was a bug on their end. Nurul worked with me on this and his service was fantastic.
Eventually I was informed it was fixed, and we were invited to use Prisma Accelerate which contains their Data Proxy service and things seem to working great. We even noticed significant improvements in our load times. Here is what our connection chart looks like now for the past 4 weeks:
Hope this helps!
-Joey
This problem also exists on supabase and github actions images. Any updates on this please?
Hello!
Unfortunetely, we are also experiencing this issue. Same scenarioas everyone else has discussed: Everything seems to be working fine then suddenly our connection spikes and the app becomes inactive for ~5 min until it eventually fixs itself.
We have a web app that uses Prisma to interact with a PostgreSQL DB.
Originally we were using Prisma version 3.14, but after reading this we updated our packages to version 5.0. Sadly, the issue still comes up.
Here is a graph from AWS showing the connection spikes where we are encountering the issues.
I have not been able to reproduce the issue. The spikes seem to be completely random.
Reading through this tread. I saw that @Venkat03003 manually disconnected his connection. This is something I have not tried yet. However, my assumption is that this is not necessary with Prism’s connection pooling. Also, reading this connection guide from Prisma (https://www.prisma.io/docs/guides/performance-and-optimization/connection-management) it explicitly says: “You do not need to explicitly $disconnect().”
I am not sure if this is an issue with Prisma or if this is some kind of issue on our end. We are continuing to investigate the situation. Just thought I would contribute to the matter to keep the discussion alive.
Thanks
EDIT
I wanted to add more information with our application. Our application is built using next.js with a node.js environment on Vercel. So with that in mind, since we are using a serverless environment we would need to follow the challenges in Prisma’s docs (serverless connection management challenge). Which, we could be doing something wrong here… I never set a connection pool limit, so right now it is just set to its default which I believe is 5. However, I am sure we only initiated the prisma client one time, and as I said before, we have not explicitly disconnected. So, maybe this is an issue with our configuration. I am not sure.
Next step we are planning to take are:
Hope this extra info helps!
I’m having this issue with
"@prisma/client": "^4.10.1"😦@garrensmith This is due to the reaper not doing its job after a load spike (and being bad in general). My PR on original mobc should help/point in the right direction. https://github.com/importcjj/mobc/pull/60
Ok then this is the reason for your problem.
Maybe observe the process list to see how it goes from 1 to 2 (or whatever starts that increase) and see if you can identify the action that does that. Something you are doing - or your code is - has that effect and should be fixed.
I usually check the processes after encountering the error and it is around 50+. Then I terminate all of them locally and wait for the error to happen again - by then the # processes would have increased to the same.
So I figured out what the issue was. Sometimes (maybe 1 in 10 times or less often), when the prisma query is incorrect (for e.g. if I have incorrectly specified a field or a field doesn’t exist), prisma is not throwing the error.
Instead it opens a lot of prisma connections and doesn’t seem to close it – so subsequent prisma queries ended up throwing the “fatal db…” error. I haven’t enforced typescript so that could a way I can prevent incorrect queries to begin with. But perhaps could there be an issue also with prisma’s error handling messages?