postgresql - 删除索引锁

Question

我的 Postgres 版本是 9.6

我今天尝试使用以下命令从我的数据库中删除索引:

drop index index_name;

它导致了很多锁——整个应用程序被卡住了，直到我杀死了 drop 的所有 session (为什么它被分成几个 session ？)。

当我检查锁时，我发现几乎所有被阻止的 session 都执行该查询:

SELECT a.attname, format_type(a.atttypid, a.atttypmod),
pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod    
FROM
pg_attribute a LEFT JOIN pg_attrdef d
                       ON a.attrelid = d.adrelid AND a.attnum = d.adnum
WHERE a.attrelid = <index_able_name>::regclass
AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

这会阻止系统操作是否有意义？

所以我决定删除带有并发选项的索引以防止锁定。

drop index concurrently index_name;

我现在从 PGAdmin 执行它(因为你不能从 noraml 事务运行它)。

它运行了 20 分钟，但还没有完成。索引大小为 20MB+-。

当我检查数据库中的锁时，我看到该表上有一个选择查询，它阻止了删除命令。

但是当我选择这个选择并在另一个 session 中执行时 - 这变化很快(2-3 秒)。

那为什么会阻止我的掉落呢？还有另一种选择吗？也许改为禁用索引？

Answer 1

drop index 和 drop index concurrently 通常是非常快的命令，但与所有 DDL 命令一样，它们都需要对表进行独占访问。

它们的不同之处仅在于如何尝试实现这种独占访问。普通的 drop index 只会请求对表的独占锁定。这将阻止在 drop 查询开始后尝试使用该表的所有查询(甚至选择)。它将执行此操作直到获得独占锁 - 当以任何方式接触表的所有事务(在 drop 命令之前开始)将完成并且带有 drop 的事务被提交时。这解释了为什么您的应用程序停止工作。

concurrently 版本也需要简短的独占访问。但它的工作方式不同——它不会阻塞表，而是等到没有其他查询触及它，然后它(通常是简短的)工作。但如果 table 一直很忙，它就永远找不到这样的时刻，而是无限期地等待它。此外，我认为它只是尝试每隔 X 毫秒重复锁定表直到成功，因此稍后的并行执行可能更幸运并且完成得更快。

如果您看到多个同时的 session 试图删除一个索引，而您没有预料到，那么您的应用程序中存在错误。数据库永远不会自己做这件事。