Blitz: Database connections are not being closed: FATAL: sorry, too many clients already

[flybayer]

What is the problem?

Database connections are not being closed when the server is stopped.

Error querying the database: db error: FATAL: sorry, too many clients already

Steps to Reproduce (maybe)

1. blitz start
2. Visit page that reads from the database
3. Make changes to your app source files and save to trigger hot reloading
4. Repeat many times

Versions:

0.9.0

How to Fix

I think the blitz server needs to add a listener for a kill signal. Then run db.disconnect() inside that. Note: this must be implemented in a way that it will not fail if the db module doesn't exist.

This should be for both blitz start and blitz start --production

[abuuzayr]

thanks @flybayer for opening this issue!

the steps should be:

1. blitz start
2. Visit page that reads from the database
3. Make changes to your app source files and save to trigger hot reloading
4. Repeat many times

I hope the above makes sense.

Also, as per your suggestion I added the ?connection_limit=1 to my DB URL in .env, but I still get this issue.

[ivandevp]

May I help with this issue?

[abuuzayr]

of course @ivandevp please feel free!

[abuuzayr]

@ivandevp this might help: https://github.com/prisma/prisma/issues/1983

[flybayer]

@abuuzayr nice!

Looks like the solution is this:

import { PrismaClient } from "@prisma/client"

let prisma

if (process.env.NODE_ENV === "production") {
  prisma = new PrismaClient()
} else {
  // Ensure the prisma instance is re-used during hot-reloading
  global.prisma = global.prisma || new PrismaClient()
  prisma = global.prisma
}

export default prisma

[ivandevp]

Hey guys! Sorry for the delay. I've been trying to reproduce but haven't found a way to do it. I followed the steps but can't see the error in the current canary version, when I create a project with Blitz 0.9.0 I got an error related to React.Suspense but not sure what is happening. Are you still facing the error in the current canary version? The problem and solution makes sense to me, but can't see it reflected in my dev environment.

[sbardian]

I am also unable to reproduce. Tried 0.90 and 0.91.

[abuuzayr]

@flybayer yup so far so good!

thanks @ivandevp @sbardian, perhaps the steps from running blitz new to getting the error are quite a lot, so it quite a long way to getting the error.

I just tried it with 0.9.2-canary.1 on a fresh project and I get the error.

Full reproduction steps

Note: the default max_connections value for postgresql is 100.

1. npx [email protected] new test
2. cd test
3. Go to schema.prisma and uncomment the Project model
4. blitz db migrate
5. blitz generate all project
6. blitz start
7. Open localhost:3000/projects (the index page does not make any query to the db or import the db)
8. Check your postgresql process count by entering your psql instance and running this query.

test=# select count(*) from pg_stat_activity;
 count
-------
     8

1. Go to app/projects/pages/projects/index.tsx
2. Make a change that will depend on projects which will depend on getProjects query
3. Ensure that the query is called again, the terminal should show something like

> Running getProjects(null)
✔ getProjects returned [
  {
    "id": 1,
    "name": "MyName"
  }
]

instead of just

[ wait ]  compiling ...
[ info ]  bundled successfully, waiting for typecheck results...
[ wait ]  compiling ...
[ info ]  bundled successfully, waiting for typecheck results...

1. Run the command in Step 8 again in psql. Your count should have increased.
2. Repeat steps 9 - 11 until the count is more than the max_connections configuration in your Postgres server (defaults to 100). I got the error when the count was 105.

test=# select count(*) from pg_stat_activity;
 count
-------
   105
(1 row)

Error in connector: Error querying the database: db error: FATAL: sorry, too many clients already

During active development, HMR/Fast Refresh is invoked on queries to the db multiple times, so I can get the error easily after a while.

@ivandevp @sbardian These are a fair amount of steps, but if you do wish to reproduce this error, do try them and let me know if your mileage varies.

Testing the solution

These are the processes before the solution is deployed:

test=# select state,query from pg_stat_activity where usename = 'fawyna';
 state  |                                                    query
--------+-------------------------------------------------------------------------------------------------------------
 idle   | SELECT COUNT(*) FROM (SELECT "public"."Job"."id" FROM "public"."Job" WHERE 1=0 OFFSET $1) AS "sub" LIMIT $2
 active | select state,query from pg_stat_activity where usename = 'fawyna';
(2 rows)

test=# select state,query from pg_stat_activity where usename = 'fawyna';
 state  |                                                    query
--------+-------------------------------------------------------------------------------------------------------------
 idle   | SELECT COUNT(*) FROM (SELECT "public"."Job"."id" FROM "public"."Job" WHERE 1=0 OFFSET $1) AS "sub" LIMIT $2
 active | select state,query from pg_stat_activity where usename = 'fawyna';
 idle   | SELECT "public"."Project"."id", "public"."Project"."name" FROM "public"."Project" WHERE 1=1 OFFSET $1
 idle   | SELECT "public"."Project"."id", "public"."Project"."name" FROM "public"."Project" WHERE 1=1 OFFSET $1
(4 rows)

test=# select state,query from pg_stat_activity where usename = 'fawyna';
 state  |                                                    query
--------+-------------------------------------------------------------------------------------------------------------
 idle   | SELECT COUNT(*) FROM (SELECT "public"."Job"."id" FROM "public"."Job" WHERE 1=0 OFFSET $1) AS "sub" LIMIT $2
 active | select state,query from pg_stat_activity where usename = 'fawyna';
 idle   | SELECT "public"."Project"."id", "public"."Project"."name" FROM "public"."Project" WHERE 1=1 OFFSET $1
 idle   | SELECT "public"."Project"."id", "public"."Project"."name" FROM "public"."Project" WHERE 1=1 OFFSET $1
 idle   | SELECT "public"."Project"."id", "public"."Project"."name" FROM "public"."Project" WHERE 1=1 OFFSET $1
(5 rows)

test=# select state,query from pg_stat_activity where usename = 'fawyna';
 state  |                                                                  query
--------+-----------------------------------------------------------------------------------------------------------------------------------------
 idle   | SELECT COUNT(*) FROM (SELECT "public"."Job"."id" FROM "public"."Job" WHERE 1=0 OFFSET $1) AS "sub" LIMIT $2
 active | select state,query from pg_stat_activity where usename = 'fawyna';
 idle   | SELECT "public"."Project"."id", "public"."Project"."name" FROM "public"."Project" WHERE 1=1 OFFSET $1
 idle   | SELECT "public"."Project"."id", "public"."Project"."name" FROM "public"."Project" WHERE 1=1 OFFSET $1
 idle   | SELECT "public"."Project"."id", "public"."Project"."name" FROM "public"."Project" WHERE 1=1 OFFSET $1
 idle   | COMMIT
 idle   | SELECT "public"."Project"."id", "public"."Project"."name" FROM "public"."Project" WHERE 1=1 OFFSET $1
 idle   | SELECT "public"."Project"."id", "public"."Project"."name" FROM "public"."Project" WHERE "public"."Project"."id" = $1 LIMIT $2 OFFSET $3
(8 rows)

On every refresh, a new process is spawned.

After deploying the solution, the same process is used, but the query is changed:

test=# select state,query from pg_stat_activity where usename = 'fawyna';
 state  |                                                 query
--------+-------------------------------------------------------------------------------------------------------
 active | select state,query from pg_stat_activity where usename = 'fawyna';
 idle   | SELECT "public"."Project"."id", "public"."Project"."name" FROM "public"."Project" WHERE 1=1 OFFSET $1
(2 rows)

test=# select state,query from pg_stat_activity where usename = 'fawyna';
 state  |                                                                  query
--------+-----------------------------------------------------------------------------------------------------------------------------------------
 active | select state,query from pg_stat_activity where usename = 'fawyna';
 idle   | SELECT "public"."Project"."id", "public"."Project"."name" FROM "public"."Project" WHERE "public"."Project"."id" = $1 LIMIT $2 OFFSET $3
(2 rows)

@ivandevp do let me know if you want me to set up the PR, i'd be happy to!

[ivandevp]

Awesome @abuuzayr! Go ahead with the PR. I will try to get the error with the steps you mentioned but in the meantime I think is good to have the PR If you already got the solution.