Postgres交易似乎无缘无故地占用了AccessExclusiveLock

Question

经过大量的谷歌搜索，我想我会问这个问题。我有一段代码可以针对 postgres （9.2） 数据库执行以下操作：

1. 开始交易
2. 删除表上的索引（5个索引）
3. 在表中插入一百万行
4. 重新创建索引
5. 提交事务。

我对 postgres 的阅读告诉我，在此操作正在进行时，我应该能够做到这一点并且仍然允许其他用户从表中进行选择（实际上甚至使用现有索引，因为它们尚未删除）。

我实际上发现的是表上的所有其他查询（它们都是选择查询）都卡住了。在查看pg_locks和pg_stat_activity表时，我发现我的事务在表上创建了一个 AccessExclusiveLock，阻止其他查询运行。此事务完成后，所有其他查询都可以正常执行。

所以，我的问题是 - 为什么创建索引/插入数据会在表上创建独占锁？它不应该使用侵入性较小的锁（例如 SHARE 锁）吗？

我是根据我对官方文档的阅读来做这个的——所以我不会被任何人的RTFM邀请:)

谢谢，
Jalpesh

Answer 1

我想您希望能够在大容量插入期间查询表，并通过在插入期间不维护索引来加快大容量插入。

我认为您可以通过以下步骤实现这一点:

begin;    

maxid = select max(id) from table_name;    

create index table_name_id_tmp_idx where id<=maxid;    

drop index table_name_id_idx;    

commit;    

begin;    

do_bulk_insert();    

create index table_name_id_idx on table_name(id);    

drop index table_name_id_tmp_idx;    

commit;    

这不可能适用于每个索引，因为您需要这个＜code＞where＜/code＞谓词来表示部分索引，它将对现有行返回＜code＞true＜/code＞，对插入的行返回＜code＞false＜/code>。但序列列、时间戳等也可以。

Answer 2

我也想要这个，但你做不到。

不是创建索引或插入在表上采用 AccessExclusive 锁，而是删除索引在这样做。

留档并没有提供获得每种锁类型的每种情况的详尽列表——它只提供了说明性示例（但也许这个示例应该包含在其中）。

我相信代码的许多部分都假定在表上的AccessShare锁被保持时索引不会消失。因此，删除索引需要与之冲突。