postgresql supabase：`prisma migrate dev`有时超时(postgres咨询锁)

uqcuzwp8 于 2023-06-22 发布在 PostgreSQL

关注(0)|答案(1)|浏览(205)

我有一个supabase数据库（postgres 15.1.0.88），我使用prisma作为ORM（最新版本prisma@4.15.0）。尝试使用prisma migrate dev应用迁移时，命令大多数情况下返回超时错误消息，称Timed out trying to acquire a postgres advisory lock
pnpm prisma migrate dev结果：

Error: Error: P1002

The database server at `db.***.supabase.co`:`5432` was reached but timed out.

Please try again.

Please make sure your database server is running at `db.***.supabase.co`:`5432`.

Context: Timed out trying to acquire a postgres advisory lock (SELECT pg_advisory_lock(72707369)). Elapsed: 10000ms. See https://pris.ly/d/migrate-advisory-locking for details.

在supabase上的日志中，我也得到了这个错误消息，但不确定它是否与我的错误有关：

Event message
relation "_prisma_migrations" does not exist
Severity
ERROR

Timestamp
2023-06-11T09:48:31.165Z

Postgres Username
postgres

Session ID
***

Metadata
{
  "file": null,
  "host": "***",
  "metadata": [],
  "parsed": [
    {
      "application_name": null,
      "backend_type": "client backend",
      "command_tag": "PARSE",
      "connection_from": "***",
      "context": null,
      "database_name": "postgres",
      "detail": null,
      "error_severity": "ERROR",
      "hint": null,
      "internal_query": null,
      "internal_query_pos": null,
      "leader_pid": null,
      "location": null,
      "process_id": 17257,
      "query": "SELECT \"id\", \"checksum\", \"finished_at\", \"migration_name\", \"logs\", \"rolled_back_at\", \"started_at\", \"applied_steps_count\" FROM \"_prisma_migrations\" ORDER BY \"started_at\" ASC",
      "query_id": 0,
      "query_pos": 126,
      "session_id": "***",
      "session_line_num": 4,
      "session_start_time": "2023-06-11 09:48:30 UTC",
      "sql_state_code": "42P01",
      "timestamp": "2023-06-11 09:48:31.165 UTC",
      "transaction_id": 0,
      "user_name": "postgres",
      "virtual_transaction_id": "12/1941"
    }
  ],
  "parsed_from": null,
  "project": null,
  "source_type": null
}

然后，~2：03分钟后，我在supabase postgres日志中又收到了2条错误消息：

connection to client lost
canceling statement due to statement timeout（<-对于SELECT pg_advisory_lock(72707369) command）

当我等待一段时间后，命令再次工作，一次甚至几次。prisma migrate reset也是如此

postgresql

来源：https://stackoverflow.com/questions/76450818/supabase-prisma-migrate-dev-sometimes-times-out-postgres-advisory-lock

1条答案

按热度按时间

tpgth1q71#

Prisma使用PostgreSQL咨询锁，其魔术数字ID为72707369，如果前一个迁移仍然连接且空闲，则会阻止新的迁移。只有当连接完全关闭并从pg_stat_activity表（一个内部PostgreSQL表，显示当前示例中所有活动和空闲连接）中删除时，这种锁才会释放。
还有另一个表pg_lock，它包含所有锁，通过一个简单的SELECT查询，您可以在objid列中找到带有幻数72707369的锁。
因此，每次使用Prisma执行迁移时（具体而言，在迁移步骤之后），必须运行以下查询：

SELECT pg_terminate_backend(PSA.pid)
FROM pg_locks AS PL
    INNER JOIN pg_stat_activity AS PSA ON PSA.pid = PL.pid
WHERE PSA.state LIKE 'idle'
    AND PL.objid IN (72707369);

pg_terminate_backend断开连接并释放每个关联的锁。这被硬编码为Prisma锁的魔法数字，因为我们不想丢弃当前的每个空闲连接。
更多信息：

Prisma迁移：咨询锁定
PostgreSQL 9.27.2：服务器信令功能
PostgreSQL 13.3.5：咨询锁
PostgreSQL 54.12: pg_locks

赞(0）回复(0）举报 2023-06-22

我来回答

postgresql supabase：`prisma migrate dev`有时超时(postgres咨询锁)

1条答案

相关问题

热门标签

最新问答