prisma: Error: P1001: Can't reach database server when including large table
Bug description
Bug: I am trying to query a table with around 10,000 rows and join a table with ~100,000 rows on it. Im expecting this query to run successfully or timeout, but it will not even show up in the query log as it will hit the error right after the graphql prisma:client request is printed out. The error does not occur when limiting the request to less rows.
What i have tried: I have tried increasing the timeout or check if something is going wrong at the database level, but the raw query runs fine, when ran manually.
Raw error:
{
"errorMessage": "\nInvalid `prisma.item.findMany()` invocation:\n\n\n Can't reach database server at `localhost`:`5432`\n\nPlease make sure your database server is running at `localhost`:`5432`.",
"errorType": "PrismaClientKnownRequestError3",
"stackTrace": [
"Error: ",
"Invalid `prisma.item.findMany()` invocation:",
"",
"",
" Can't reach database server at `localhost`:`5432`",
"",
"Please make sure your database server is running at `localhost`:`5432`.",
" at cb (/Users/nschroeter/Documents/workspace/RamboTrades/hercules-serverless/node_modules/@prisma/client/runtime/index.js:36374:17)",
" at processTicksAndRejections (node:internal/process/task_queues:94:5)",
" at async /Users/nschroeter/Documents/workspace/RamboTrades/hercules-serverless/dist/apps/updatePrices/main.js:219:27",
" at async AppService.run (/Users/nschroeter/Documents/workspace/RamboTrades/hercules-serverless/dist/apps/updatePrices/main.js:1482:23)",
" at async handler (/Users/nschroeter/Documents/workspace/RamboTrades/hercules-serverless/dist/apps/updatePrices/main.js:1910:5)"
]
}
How to reproduce
- Create a PostgreSQL 12 database with the default image via docker-compose
- Create 2 tables (Both with a combined PK and ~20columns)
- Link them with a one-to-many relationship
- Insert a couple (>100k) rows in both tables
- Run a findMany query on the first table and include the second
Expected behavior
Im expecting the query to either resolve or timeout.
Prisma information
prisma:client:fetcher Error: Can't reach database server at `localhost`:`5432`
prisma:client:fetcher
prisma:client:fetcher Please make sure your database server is running at `localhost`:`5432`.
prisma:client:fetcher at BinaryEngine.graphQLToJSError (/Users/nschroeter/Documents/workspace/RamboTrades/hercules-serverless/node_modules/@prisma/client/runtime/index.js:29972:16)
prisma:client:fetcher at BinaryEngine.request (/Users/nschroeter/Documents/workspace/RamboTrades/hercules-serverless/node_modules/@prisma/client/runtime/index.js:29857:24)
prisma:client:fetcher at processTicksAndRejections (node:internal/process/task_queues:94:5)
prisma:client:fetcher at async cb (/Users/nschroeter/Documents/workspace/RamboTrades/hercules-serverless/node_modules/@prisma/client/runtime/index.js:36344:26)
prisma:client:fetcher at async /Users/nschroeter/Documents/workspace/RamboTrades/hercules-serverless/dist/apps/updatePrices/main.js:219:27
prisma:client:fetcher at async AppService.run (/Users/nschroeter/Documents/workspace/RamboTrades/hercules-serverless/dist/apps/updatePrices/main.js:1482:23)
prisma:client:fetcher at async handler (/Users/nschroeter/Documents/workspace/RamboTrades/hercules-serverless/dist/apps/updatePrices/main.js:1910:5)
Environment & setup
- OS: Mac OS
- Database: PostgreSQL
- Node.js version: 14
- Other: NestJS v8
Prisma Version
prisma : 2.29.1
@prisma/client : 2.29.1
Current platform : darwin
Query Engine : query-engine 1be4cd60b89afa04b192acb1ef47758a39810f3a (at node_modules/@prisma/engines/query-engine-darwin)
Migration Engine : migration-engine-cli 1be4cd60b89afa04b192acb1ef47758a39810f3a (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine : introspection-core 1be4cd60b89afa04b192acb1ef47758a39810f3a (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary : prisma-fmt 1be4cd60b89afa04b192acb1ef47758a39810f3a (at node_modules/@prisma/engines/prisma-fmt-darwin)
Default Engines Hash : 1be4cd60b89afa04b192acb1ef47758a39810f3a
Studio : 0.419.0
Preview Features : filterJson, referentialActions
About this issue
- Original URL
- State: closed
- Created 3 years ago
- Reactions: 9
- Comments: 16 (10 by maintainers)
This happens at any point when querying the data. I’ve done some digging and it seems related to prisma translating the include statement into 2 seperate SQL SELECT statements instead of a JOIN statement (Documentation: here) . The second one being a WHERE IN with a huge amount of data from the first query. I since swapped out the query to a raw query with an INNER JOIN and it seems to do the trick. Nevertheless the error message is very confusing.
I’ve setup a minimum reproduction repo here: https://github.com/nwidynski/reproduction-prisma-8878
You can use the prisma seed command to seed the needed data and you might need to increase the max_stack_depth parameter of the postgres instance.
I am going to close this as its been 3 months now and there is no activity here.
Please reply if you want us to take another look.
Do you have a reproduction by chance @cyrus-za? That would still be super helpful here.
I just ran into this and setting
QUERY_BATCH_SIZE=5000
fixes it for me. Thanks for saving me a lot of time @janpio and @pantharshit00