Supabase: `prisma migrate dev` 有时会超时(PostgreSQL advisory lock)

huangapple go评论69阅读模式
英文:

supabase: `prisma migrate dev` sometimes times out (postgres advisory lock)

问题

我有一个Supabase数据库(PostgreSQL 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

已到达位于 `db.***.supabase.co`:`5432` 的数据库服务器,但超时。

请重试。

请确保您的数据库服务器在 `db.***.supabase.co`:`5432` 上运行。

上下文:在尝试获取 PostgreSQL 咨询锁时超时(SELECT pg_advisory_lock(72707369))。已经过去:10000毫秒。请参阅 https://pris.ly/d/migrate-advisory-locking 以获取详细信息。

在Supabase的日志中,我还收到了这个错误消息,但不确定它是否与我的错误有关:

事件消息
关系 "_prisma_migrations" 不存在
严重性
错误

时间戳
2023-06-11T09:48:31.165Z

PostgreSQL 用户名
postgres

会话 ID
***

元数据
{
  "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 PostgreSQL日志中收到2条更多的错误消息:

  • 连接到客户端丢失
  • 由于语句超时而取消语句(针对 SELECT pg_advisory_lock(72707369) 命令)

然后,当我等待一段时间后,该命令再次正常工作,可能一次或多次。对于 prisma migrate reset 也发生相同情况。

英文:

I have a supabase database (postgres 15.1.0.88) and I'm using prisma as ORM(latest version prisma@4.15.0). When trying to apply migrations with prisma migrate dev, the command most of the times returns a timeout error message saying Timed out trying to acquire a postgres advisory lock

pnpm prisma migrate dev results in:

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.

In the logs on supabase I also get this error message, but not sure if it's related to my error:

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
}

Then, ~2:03 Minutes later, I get 2 more error messages in the supabase postgres logs:

  • connection to client lost
  • canceling statement due to statement timeout (<- for the SELECT pg_advisory_lock(72707369) command)

When I then wait some time, the command works again, once or even a few times.
The same also happens with prisma migrate reset

答案1

得分: 3

Prisma 使用具有魔术数字ID 72707369 的 PostgreSQL 咨询锁,如果先前的迁移仍然连接并处于空闲状态,则会阻止新迁移。这种类型的锁只有在连接完全关闭并从表 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 uses a PostgreSQL advisory lock with the magic number ID 72707369 that blocks a new migration if the previous one is still connected and idle. This kind of lock only releases when the connection is fully closed and removed from the table pg_stat_activity (an internal PostgreSQL table that shows all active and idle connections in the current instance).

There is another table, pg_lock that contains all the locks, and with a simple SELECT query, you can find in the objid column the lock with the magic number 72707369.

So, the following query must be run every time you do a migration with Prisma (specifically, after the migration step):

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 &#39;idle&#39;
    AND PL.objid IN (72707369);

pg_terminate_backend drops the connection and releases every associated lock. This is hardcoded to the magic number of the Prisma lock, because we don't want to drop every current idle connection.

For more information:

huangapple
  • 本文由 发表于 2023年6月11日 21:57:23
  • 转载请务必保留本文链接:https://go.coder-hub.com/76450818.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定