Postgres中的DELETE查询无限期挂起

Question

我现在正在尝试删除特定数据库表api_user中的一行或多行。但删除操作似乎无限期地挂起（目前已运行1800秒，因为我一直在寻找答案）。所涉及的行有相关的外键依赖项，但所有这些依赖项都已被删除，这已经得到了验证。

我通过Postico进行所有数据库内省（只是另一个数据库GUI客户端），因此当我取消查询时，会收到此错误消息。

ERROR: canceling statement due to user request
CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."api_event" x WHERE $1::pg_catalog.text OPERATOR(pg_catalog.= ) "user_id"::pg_catalog.text FOR KEY SHARE OF x"

这个表中有引用行的索引。 api_event 是一个表，它有索引和外键指向这个表。所有依赖于 api_event 的行都被删除了。

我检查了 pg_stat_activity 是否有任何可能同时运行的查询，但没有发现任何问题，所以我不确定接下来该问什么问题。任何方向都将是极好的！

运行 EXPLAIN DELETE FROM api_user WHERE organization_id = '<replaced value>';，返回结果如下：

Delete on api_user (cost=54.94..2903.50 rows=1874 width=6) -> Bitmap Heap Scan on api_user (cost=54.94..2903.50 rows=1874 width=6) Recheck Cond: ((organization_id)::text = '<replaced value>'::text) -> Bitmap Index Scan on api_user_organization_id (cost=0.00..54.47 rows=1874 width=0) Index Cond: ((organization_id)::text = '<replaced value>'::text)

锁监控

你检查过是否等待锁吗？ - a_horse_with_no_name

根据请求，我搜索了数据库上的锁。我使用了这个查询：

 select t.relname,
        l.locktype,
        page,
        virtualtransaction,
        pid,
        mode,
        granted 
 from pg_locks l, 
      pg_stat_all_tables t 
 where l.relation=t.relid 
 order by relation asc;

第一次返回时，我的DELETE没有运行，容器中有3行锁来自于pg_class、pg_index和pg_namespace。

第二次返回时，我的DELETE正在运行，包含21行锁。其中所有的relname都是之前已删除的一组行，这些行要么具有外键，要么具有与此行相关的索引。

解决之路

通过更多的问题和研究，出现了一个有趣的细节，即并非所有子表上的外键都有索引。在编写查询以查看哪些外键没有索引后，我注意到api_event没有索引到其api_user外键。现在api_event是一个庞大的表。

在api_event上创建索引解决了这个问题。

CREATE INDEX CONCURRENTLY user_id_to_events ON api_event(user_id);

- Darius Calliet

---

你检查过它是否在等待锁吗？ - user330315

@TimBiegeleisen 我编辑了原始帖子！ - Darius Calliet

你们的代码是否达到了生产级别，对于停机时间有零容忍的要求？ - Adrian Hartanto

@DariusCalliet 我的建议是，阻止 pg_hba.conf，重新加载数据库，重启数据库，并进行级联删除。 - Adrian Hartanto

@DariusCalliet，“删除时挂起”的答案是因为应用程序仍在访问您想要删除的表。确保您想要删除的表没有被任何人访问，这样PostgreSQL就可以优雅地删除该表。 - Adrian Hartanto

Answer 1

虽然这并不是一个非常有用的答案，但它可能会帮助某些人。我在对表进行大量删除后遇到了同样的问题。我正在尝试使用不同的删除查询，并尝试找出哪个查询最快。我还取消了一些查询，但无法找到根本原因。

我的数据库托管在 Google Cloud 上并支持备份。所以，我恢复了几天前的备份，问题就解决了。

- Berkay Berabi

Answer 2

创建索引对于缓慢的删除查询非常有用。 当您运行“explain analyze delete from xx”命令进行删除查询，但因为速度太慢而取消时，它将显示：

ERROR:  canceling statement due to user request
CONTEXT:  SQL statement "DELETE FROM ONLY "public"."AAAA" WHERE $1 OPERATOR(pg_catalog.=) "BBBB""

运行 CREATE INDEX CONCURRENTLY NAME_OF_INDEX ON AAAA(BBBB)

可以解决这个问题。

- eastonsuo

---

值得一提的是，对我来说，问题出在一个表中的索引引用了目标表。这显然与FK约束有关。只需注意上下文输出即可。我也不需要使用EXPLAIN ANALYZE来获取输出。可能是因为这是一个开发环境 - 不确定。 - Jacob Thomason

Answer 3

我不确定（也无法评论），但我认为您正在经历重复索引或删除后的清空。

- Churam