Postgres 长时间运行的事务在父分区表上保持锁定

Question

TL；DR:我们有长时间运行的导入，它们似乎持有父分区表的锁，即使没有直接引用父表。

在我们的系统中，我们有库存和inventory_items。库存往往有 200k 左右的项目，我们的访问模式通过使用本机分区inventory_id对inventory_items表进行分区是有意义的（我们在 Postgres 12 上）。换句话说，每个清单都有自己的分区inventory_items表。这是通过以下 DDL 完成的：

CREATE TABLE public.inventory_items (
  inventory_id integer NOT NULL,
  /* ... */
)
PARTITION BY LIST (inventory_id);

在我们的应用程序代码中，当通过Web仪表板创建清单时，我们会通过以下方式自动创建分区子inventory_items表：

CREATE TABLE IF NOT EXISTS inventory_items_#{inventory_id}
  PARTITION OF inventory_items
  FOR VALUES IN (#{inventory_id});

这些库存通常每天通过CSV或其他方式完全重新加载/重新导入一次，这些导入任务有时需要一段时间。

我们注意到，当这些长时间导入正在运行时，无法创建新的清单，因为如上所述，创建清单意味着创建分区子inventory_items表，并且长时间运行的导入和在 Web 仪表板中创建清单之间存在一些锁定争用，这很糟糕：我们不能仅仅因为发生了完全不相关的导入而阻止用户创建清单。

我在psql中使用以下查询来确定谁持有什么锁：

select pid, relname, mode
from pg_locks l
join pg_class t on l.relation = t.oid
where t.relkind = 'r';

此查询返回成功获取/持有的锁；它不会显示等待获取锁的pid(因为其他PID持有它)。对于这些，你必须看看postgres日志。

导入开始后，工作进程 （pid 9029） 将获取以下锁

 pid  |        relname     |       mode
------+--------------------+------------------
 9029 | inventory_items_16 | AccessShareLock
 9029 | inventory_items_16 | RowExclusiveLock

我们要导入的库存的id为16，所以持有的锁位于属于该库存的inventory_items分区子表上。请注意，父< code>inventory_items表上似乎没有任何锁。

当我尝试在仪表板中创建清单时，由于30SQL语句超时，请求会暂停和超时。在超时之前，锁如下所示：

 pid  |        relname     |       mode
------+--------------------+------------------
 7089 | inventories        | RowExclusiveLock

 9029 | inventory_items_16 | AccessShareLock
 9029 | inventory_items_16 | RowExclusiveLock

PID 7089是web服务器。它成功地抓取了库存上的RowExclusiveLock（＜code＞INSERT INTO库存＜/code＞），但查看postgres日志，它试图抓取119795上的AccessExclusive锁，但失败了，这是父＜code＞inventory_items＜/code＞表：

postgres.7089 [RED] [29-1]  sql_error_code = 00000 LOG:  statement: CREATE TABLE IF NOT EXISTS inventory_items_16
postgres.7089 [RED] [29-2]    PARTITION OF inventory_items
postgres.7089 [RED] [29-3]    FOR VALUES IN (16);
postgres.7089 [RED] [29-4]
postgres.7089 [RED] [30-1]  sql_error_code = 00000 LOG:  process 7089 still waiting for AccessExclusiveLock on relation 119795 of database 16402 after 1000.176 ms
postgres.7089 [RED] [30-2]  sql_error_code = 00000 DETAIL:  Process holding the lock: 9029. Wait queue: 7089.
postgres.7089 [RED] [30-3]  sql_error_code = 00000 STATEMENT:  CREATE TABLE IF NOT EXISTS inventory_items_16
postgres.7089 [RED] [30-4]    PARTITION OF inventory_items
postgres.7089 [RED] [30-5]    FOR VALUES IN (16);

我认为在创建子分区时需要在父表上使用 AccessExclusiveLock 的原因是 postgres 需要将一些内部架构 y 元数据写入父表，以便它可以将 inventory_id=16 的行路由到这个新表，这对我来说很有意义。

但是，从我的pg_locks查询来看，我不知道锁争用是从哪里来的。web服务器需要父表上的AccessExclusiveLock，但pg_locks显示，仅在子inventory_items_16表上保留锁。

那么，这里会发生什么？子表上的锁是在父表上的锁中“扩展”，还是与父表上的锁争用？

有没有其他方法可以解决这个问题？我们对划分这些表的决定非常有信心，但是这种意想不到的锁争用正在导致真正的问题，所以我们正在寻找一种干净、维护最少的方法来保持这种基本架构。

在极少数情况下，活动导入的存在不会阻止web worker。90%的情况下会，但有时不会。因此，在这个混合体中的某个地方有一点点不确定性，它混淆了一切。

Answer 1

使用CREATE TABLE…partition OF…创建分区需要分区表上的ACCESS EXCLUSIVE锁，这将与对分区表的所有访问冲突。

另一方面，插入到分区中需要在计划插入语句时对分区表进行访问共享锁定。这会导致锁定冲突。

我看到了两条出路：

> < li>

分两步创建新分区:

CREATE TABLE inventory_items_42 (
   LIKE inventory_items INCLUDING DEFAULTS INCLUDING CONSTRAINTS
);
ALTER TABLE inventory_items
   ATTACH PARTITION inventory_items_42 FOR VALUES IN (42);

这只需要分区表上的共享更新独占锁（从 PostgreSQL v12 开始），它与并发插入兼容。

使用服务器准备好的语句将INSERT插入到分区中，并确保在开始加载数据的长时间运行事务之前准备好语句。您可以使用PostgreSQL的PREPARE和EXECUTE语句或使用API的工具。