时间戳

首页 > 解决方案 > 删除具有大量传入外键的行时共享内存不足

postgresql - 删除具有大量传入外键的行时共享内存不足

问题描述

我开发了一个多租户应用程序，其中我们有一个主模式来跟踪租户，以及 99 个应用程序数据库来分配负载。每个应用程序数据库中的 33 个表中的每一个都有一个指向主架构的租户列。这意味着有 3,267 个外键指向主模式的租户 ID，大约有 6000 个触发器与租户表相关联。

最近，我添加了一个表，并在我们删除测试租户的测试套件的拆解部分开始收到此错误：

psycopg2.errors.OutOfMemory: out of shared memory
HINT:  You might need to increase max_locks_per_transaction.
CONTEXT:  SQL statement "SELECT 1 FROM ONLY "test2"."item" x WHERE $1 OPERATOR(pg_catalog.=) "tenant" FOR KEY SHARE OF x"
 For query
SET CONSTRAINTS ALL IMMEDIATE

按照建议提高 max_locks_per_transaction 可以解决问题，删除一些应用程序模式也是如此。这里明显的解决方案是减少冗余模式的数量或删除外键约束，这样我们就不必持有这么多锁，但我很好奇这里是否还有其他事情发生。

我曾设想只有要删除的行（与测试模式相关联）会被锁定，因此只有测试模式会被锁定。无论如何，此时已没有数据指向租户表，因此在实践中锁定几乎是多余的。

更新：

有关更多上下文，我在这里并没有做任何真正花哨的事情。下面是我的架构和查询的简化示例：

CREATE SCHEMA master;
CREATE table master.tenant (id uuid NOT NULL PRIMARY KEY);

CREATE SCHEMA app_00;

CREATE table app_00.account (id uuid NOT NULL PRIMARY KEY, tenant uuid NOT NULL);
ALTER TABLE app_00.account ADD CONSTRAINT fk_tenant FOREIGN KEY (store) REFERENCES master.store(id) DEFERRABLE;

CREATE table app_00.item (id uuid NOT NULL PRIMARY KEY, tenant uuid NOT NULL);
ALTER TABLE app_00.item ADD CONSTRAINT fk_tenant FOREIGN KEY (store) REFERENCES master.store(id) DEFERRABLE;

实际上，我正在为 app_00..99 的每个模式创建 33 个表。现在假设我的数据库填充了数据，因上述错误而失败的查询是：

DELETE FROM master.tenant WHERE id = 'some uuid';

标签： postgresqllockingout-of-memory

解决方案

您没有告诉我们太多有关设置的信息，但可能涉及分区或继承。这些功能通常要求语句在查询计划或执行期间递归到表分区或继承子级。无论如何，您的 SQL 语句必须涉及许多表。

现在，无论何时 PostgreSQL 接触到一个表，它都会在其上加锁以避免并发执行冲突。如果涉及到很多表，可能是原来有max_connections * max_locks_per_transaction条目的锁表已经用完了。

解决方法就是增加max_locks_per_transaction. 不用担心，提高该参数没有负面影响，在服务器启动期间只会分配更多的共享内存。

推荐阅读