prisma: The `ConnectorError` `cached plan must not change result type` periodically appears (Postgres).

Bug description

I’ve got several test suites for the graphql API (built with ApolloServer & Prisma). The tests basically run some queries against the API endpoint with the apollo client. They are standalone (imitate user queries to the API). API uses a dev database (local Postgres docker container) and a locally running server.

An error occurs periodically, I can run 1 test file a few times and it passes, then this error occurs, after that again the tests pass.

console.error src/tests/utils/ErrorLog.ts:7
    Exception: {
      errors: [
        {
          message: '\n' +
            'Invalid `prisma.company.create()` invocation:\n' +
            '\n' +
            '\n' +
            '  Error occurred during query execution:\n' +
            'ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Error { kind: Db, cause: Some(DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState("0A000"), message: "cached plan must not change result type", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("plancache.c"), line: Some(722), routine: Some("RevalidateCachedQuery") }) }) })',
          locations: [],
          path: [Array]
        }
      ],
      stacktrace: [
        'Error: ',
        'Invalid `prisma.company.create()` invocation:',
        '',
        '',
        '  Error occurred during query execution:',
        'ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Error { kind: Db, cause: Some(DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState("0A000"), message: "cached plan must not change result type", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("plancache.c"), line: Some(722), routine: Some("RevalidateCachedQuery") }) }) })',
        '    at new CombinedError (/Users/jackiejo/main/smidyo/smidyo-monorepo/quoting-portal/api/node_modules/apollo-server/node_modules/graphql-tools/src/stitching/errors.ts:90:5)',
        '    at Object.checkResultAndHandleErrors (/Users/jackiejo/main/smidyo/smidyo-monorepo/quoting-portal/api/node_modules/apollo-server/node_modules/graphql-tools/src/stitching/errors.ts:111:11)',
        '    at CheckResultAndHandleErrors.transformResult (/Users/jackiejo/main/smidyo/smidyo-monorepo/quoting-portal/api/node_modules/apollo-server/node_modules/graphql-tools/src/transforms/CheckResultAndHandleErrors.ts:15:12)',
        '    at /Users/jackiejo/main/smidyo/smidyo-monorepo/quoting-portal/api/node_modules/apollo-server/node_modules/graphql-tools/src/transforms/transforms.ts:37:45',
        '    at Array.reduce (<anonymous>)',
        '    at applyResultTransforms (/Users/jackiejo/main/smidyo/smidyo-monorepo/quoting-portal/api/node_modules/apollo-server/node_modules/graphql-tools/src/transforms/transforms.ts:35:21)',
        '    at /Users/jackiejo/main/smidyo/smidyo-monorepo/quoting-portal/api/node_modules/apollo-server/node_modules/graphql-tools/src/stitching/delegateToSchema.ts:104:12',
        '    at step (/Users/jackiejo/main/smidyo/smidyo-monorepo/quoting-portal/api/node_modules/apollo-server/node_modules/graphql-tools/dist/stitching/delegateToSchema.js:32:23)',
        '    at Object.next (/Users/jackiejo/main/smidyo/smidyo-monorepo/quoting-portal/api/node_modules/apollo-server/node_modules/graphql-tools/dist/stitching/delegateToSchema.js:13:53)',
        '    at fulfilled (/Users/jackiejo/main/smidyo/smidyo-monorepo/quoting-portal/api/node_modules/apollo-server/node_modules/graphql-tools/dist/stitching/delegateToSchema.js:4:58)'
      ]
    }

OR this error occurs if some tests pass in the suite

console.error src/tests/utils/ErrorLog.ts:7
    Exception: {
      errors: [
        {
          message: '\n' +
            'Invalid `prisma.salesDomain.create()` invocation:\n' +
            '\n' +
            '\n' +
            '  Error occurred during query execution:\n' +
            'ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Error { kind: Db, cause: Some(DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState("0A000"), message: "cached plan must not change result type", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("plancache.c"), line: Some(722), routine: Some("RevalidateCachedQuery") }) }) })',
          locations: [],
          path: [Array]
        }
      ],
      stacktrace: [
        'Error: ',
        'Invalid `prisma.salesDomain.create()` invocation:',
        '',
        '',
        '  Error occurred during query execution:',
        'ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Error { kind: Db, cause: Some(DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState("0A000"), message: "cached plan must not change result type", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("plancache.c"), line: Some(722), routine: Some("RevalidateCachedQuery") }) }) })',
        '    at new CombinedError (/Users/jackiejo/main/smidyo/smidyo-monorepo/quoting-portal/api/node_modules/apollo-server/node_modules/graphql-tools/src/stitching/errors.ts:90:5)',
        '    at Object.checkResultAndHandleErrors (/Users/jackiejo/main/smidyo/smidyo-monorepo/quoting-portal/api/node_modules/apollo-server/node_modules/graphql-tools/src/stitching/errors.ts:111:11)',
        '    at CheckResultAndHandleErrors.transformResult (/Users/jackiejo/main/smidyo/smidyo-monorepo/quoting-portal/api/node_modules/apollo-server/node_modules/graphql-tools/src/transforms/CheckResultAndHandleErrors.ts:15:12)',
        '    at /Users/jackiejo/main/smidyo/smidyo-monorepo/quoting-portal/api/node_modules/apollo-server/node_modules/graphql-tools/src/transforms/transforms.ts:37:45',
        '    at Array.reduce (<anonymous>)',
        '    at applyResultTransforms (/Users/jackiejo/main/smidyo/smidyo-monorepo/quoting-portal/api/node_modules/apollo-server/node_modules/graphql-tools/src/transforms/transforms.ts:35:21)',
        '    at /Users/jackiejo/main/smidyo/smidyo-monorepo/quoting-portal/api/node_modules/apollo-server/node_modules/graphql-tools/src/stitching/delegateToSchema.ts:104:12',
        '    at step (/Users/jackiejo/main/smidyo/smidyo-monorepo/quoting-portal/api/node_modules/apollo-server/node_modules/graphql-tools/dist/stitching/delegateToSchema.js:32:23)',
        '    at Object.next (/Users/jackiejo/main/smidyo/smidyo-monorepo/quoting-portal/api/node_modules/apollo-server/node_modules/graphql-tools/dist/stitching/delegateToSchema.js:13:53)',
        '    at fulfilled (/Users/jackiejo/main/smidyo/smidyo-monorepo/quoting-portal/api/node_modules/apollo-server/node_modules/graphql-tools/dist/stitching/delegateToSchema.js:4:58)'
      ]
    }

Also this periodically appears on postgres log in docker

dev-postgres_postgres_1 | 2021-06-15 14:09:35.072 UTC [35] ERROR: cached plan must not change result type

How to reproduce

Please refer to this repository: https://github.com/JackieJoo/prisma-error-sample

Expected behavior

No response

Prisma information

Environment & setup

  • OS: Mac OS
  • Database: PostgreSQL
  • Node.js version: v14.15.1

Prisma Version

2.24.1

About this issue

  • Original URL
  • State: open
  • Created 3 years ago
  • Reactions: 12
  • Comments: 23 (10 by maintainers)

Commits related to this issue

Most upvoted comments

I received this error when I had applied a migration to my dev DB, which reset the DB, but my server had not been restarted.

+20
paustint on Aug 27, 2021

Is there any news on this thread?

+6
MarcusHSmith on Oct 17, 2022

this happens after reseting the database with prisma migrate reset and an app is still running.

in our case we have a reset job that runs on test environments to reset the database.

+5
macrozone on Dec 16, 2022

I can reproduce this after running the reproduction twice.

It doesn’t seem to happen if I add &statement_cache_size=0 to the connection string so looks like a bug in the statement cache implementation. Add this to the connection string as a workaround for now.

You need to enable mode using DEBUG=* to see these error logs. I am marking this as confirmed bug.

Relevant stack traces:

  
 prisma:client:fetcher Error: Error occurred during query execution:
  prisma:client:fetcher ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Error { kind: Db, cause: Some(DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState("0A000"), message: "cached plan must not change result type", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("plancache.c"), line: Some(722), routine: Some("RevalidateCachedQuery") }) }) })
  prisma:client:fetcher     at NodeEngine.graphQLToJSError (C:\Users\harshit\code\reproductions\prisma-error-sample\node_modules\@prisma\client\runtime\index.js:28016:14)
  prisma:client:fetcher     at NodeEngine.request (C:\Users\harshit\code\reproductions\prisma-error-sample\node_modules\@prisma\client\runtime\index.js:27912:24)
  prisma:client:fetcher     at processTicksAndRejections (internal/process/task_queues.js:95:5) +117ms

prisma:client:fetcher Error: An operation failed because it depends on one or more records that were required but not
found. No 'User' record(s) (needed to inline the relation on 'Post' record(s)) was found for a nested connect on one-to-many relation 'PostToUser'.
  prisma:client:fetcher     at NodeEngine.graphQLToJSError (C:\Users\harshit\code\reproductions\prisma-error-sample\node_modules\@prisma\client\runtime\index.js:28014:16)
  prisma:client:fetcher     at NodeEngine.request (C:\Users\harshit\code\reproductions\prisma-error-sample\node_modules\@prisma\client\runtime\index.js:27912:24)
  prisma:client:fetcher     at processTicksAndRejections (internal/process/task_queues.js:95:5)
  prisma:client:fetcher     at cb (C:\Users\harshit\code\reproductions\prisma-error-sample\node_modules\@prisma\client\runtime\index.js:33786:26) +17ms
+5
pantharshit00 on Apr 19, 2022

@lperrious @vladimiremi @danil-iglu @tminard @DimosthenisK @macrozone @MarcusHSmith @Jackman3005 @paustint To confirm something: Are you all using enums in your Prisma Schema as @JackieJoo does?

+3
janpio on Jul 25, 2023

To confirm something: Are you all using enums in your Prisma Schema?

I am.

+2
DimosthenisK on Jul 25, 2023

What seemed to fix it for me was adding prismaClient.$disconnect() in the afterEach hook and reinstating the prismaClient in beforeEach.

+2
DimosthenisK on Feb 10, 2023

@pantharshit00 OMG thank you for your comment about the &statement_cache_size=0 workaround!

I’ve been futsin around with trying to get end-to-end tests working in GitHub Actions for two days and been stuck on this issue and this has solved it for me 🙏

We’ve also seen this error here and there when we have the API running and do a yarn prisma migrate reset. In our CI environment we were getting this error 100% of the time. I’m not sure what Prisma can do to fix this at runtime, but would love to see it resolved for good!

In the off-chance I can help another poor wondering soul, here’s a bit of information on my situation.

Prisma Migration

We have a table called “FormPrototype” and it has a column that is an enum called “status”. We have quite a few prisma generated migration files now and one of them has the following:

Note: this was generated when we removed a status from the enum

-- AlterEnum
BEGIN;
CREATE TYPE "FormPrototypeStatus_new" AS ENUM ('DRAFT', 'ACTIVE', 'ARCHIVED');
ALTER TABLE "FormPrototype" ALTER COLUMN "status" DROP DEFAULT;
ALTER TABLE "FormPrototype" ALTER COLUMN "status" TYPE "FormPrototypeStatus_new" USING ("status"::text::"FormPrototypeStatus_new");
ALTER TYPE "FormPrototypeStatus" RENAME TO "FormPrototypeStatus_old";
ALTER TYPE "FormPrototypeStatus_new" RENAME TO "FormPrototypeStatus";
DROP TYPE "FormPrototypeStatus_old";
ALTER TABLE "FormPrototype" ALTER COLUMN "status" SET DEFAULT 'DRAFT';
COMMIT;

Postgres Error

I believe that migration code in collaboration with how Postgres does caching is the cause for the following error that I was getting from the postgres container logs in my CI environment:

2022-04-18 07:06:21.482 UTC [1] LOG:  database system is ready to accept connections
 2022-04-18 07:08:56.418 UTC [74] ERROR:  cache lookup failed for type 17062
 2022-04-18 07:08:56.418 UTC [74] STATEMENT:  SELECT "public"."FormPrototype"."id",
"public"."FormPrototype"."name"
FROM "public"."FormPrototype"
WHERE "public"."FormPrototype"."status" = $1
OFFSET $2

Prisma Error

/home/runner/work/our-repo/our-repo/api/node_modules/@prisma/client/runtime/index.js:38707
          throw new PrismaClientUnknownRequestError(message, this.prisma._clientVersion);
                ^
Error: 
Invalid `prisma.formPrototype.findMany()` invocation in
/home/runner/work/our-repo/our-repo/api/src/events/notifications.ts:15:45

  12 console.log("Sending notifications...");
  13 const currentDate = new Date();
  14 
→ 15 const quests = await prisma.formPrototype.findMany(
  Error occurred during query execution:
ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Error { kind: Db, cause: Some(DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState("XX000"), message: "cache lookup failed for type 17062", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("lsyscache.c"), line: Some(2851), routine: Some("getTypeBinaryInputInfo") }) }) })
    at cb (/home/runner/work/our-repo/our-repo/api/node_modules/@prisma/client/runtime/index.js:38707:17)
    at /home/runner/work/our-repo/our-repo/api/src/context.ts:40:18
    at /home/runner/work/our-repo/our-repo/api/src/context.ts:32:18
    at PrismaClient._request (/home/runner/work/our-repo/our-repo/api/node_modules/@prisma/client/runtime/index.js:40859:18)
    at Object.notificationEventHandler (/home/runner/work/our-repo/our-repo/api/src/events/notifications.ts:15:18)
error Command failed with exit code 1.

End-to-end setup

We are using GitHub Actions and using the services feature specifying a Postgres container. Nothing too special here.

We have a start bash script that does the following to prepare and start our API:

export DATABASE_URL="postgresql://app_test:app_test@localhost:5432/app_test?schema=public"

yarn install --frozen-lockfile
yarn prisma migrate reset --force # no difference when using `migrate dev`
yarn start # ts-node --transpile-only src/index.ts
+2
Jackman3005 on Apr 18, 2022

I fixed the order. First query fails with cached plan must not change result type which in turns fails another query because it received null instead of a valid id.

+1
pantharshit00 on Jun 21, 2021
Read more comments on GitHub
← prisma: `take` key doesn't work correctly for nested query that returns one item with its nested children
prisma: The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request. →