Prisma自动递增int ID插入报错：integer out of range for type int4

创建Comment记录时触发"integer out of range for type int4"错误，id字段已定义为Int类型并设置@default(autoincrement())自动递增，数据库中唯一的int4字段就是id。相关代码与错误日志如下：

1. Prisma Schema定义 #

model Comment {
  id        Int             @id @default(autoincrement())
  cid       String      
  bid       String
  author    AppProfile      @relation(fields: [bid], references: [bid])
  comment   String
  created   DateTime        @default(now())
}

2. 创建记录的代码 #

const results = await prisma.comment.create({
  data: {
    cid: req.body.cid,
    comment: req.body.comment,
    bid: appProfile.bid,
  },
});

3. 错误日志 #

prisma:query INSERT INTO "public"."Comment" ("cid","bid","comment","created") VALUES ($1,$2,$3,$4) RETURNING "public"."Comment"."id"
prisma:query ROLLBACK
error - PrismaClientUnknownRequestError:
Invalid `prisma.comment.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(E22003), message: "integer out of range for type int4", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("eval.go"), line: Some(65), routine: Some("init") }) }) })
    at RequestHandler.handleRequestError (/Users/jmalicke/Projects/muf/dev/repos/muf/bubbles-serverless/node_modules/@prisma/client/runtime/index.js:34314:13)
    at RequestHandler.request (/Users/jmalicke/Projects/muf/dev/repos/muf/bubbles-serverless/node_modules/@prisma/client/runtime/index.js:34293:12)
    at async PrismaClient._request (/Users/jmalicke/Projects/muf/dev/repos/muf/bubbles-serverless/node_modules/@prisma/client/runtime/index.js:35273:16)
    at async comment (webpack-internal:///(api)/./pages/api/comment/index.ts:26:25) {
  clientVersion: '4.6.1',
  page: '/api/comment'
}

可能的原因及解决方法 #

• 自增ID超出int4范围：PostgreSQL的int4类型最大值为2147483647，若Comment表数据量过大，自增ID已达或超过该值就会触发错误。
  解决：将id字段类型从Int改为BigInt，更新schema后执行prisma migrate dev同步数据库。修改后的schema：

  model Comment {
    id        BigInt          @id @default(autoincrement())
    cid       String      
    bid       String
    author    AppProfile      @relation(fields: [bid], references: [bid])
    comment   String
    created   DateTime        @default(now())
  }
  

• 数据库序列异常：若表数据量不大，但自增序列被错误设置为接近int4最大值的起始值，也会导致插入时超出范围。
  解决：手动重置PostgreSQL中的序列。先查询当前序列值确认：

  SELECT currval('"public"."Comment_id_seq"');
  

  再重置序列：

  ALTER SEQUENCE "public"."Comment_id_seq" RESTART WITH 1;
  

内容的提问来源于stack exchange，提问作者user2836797