postgresql:共享内存不足？

Question

我正在使用Python和psycopg2运行一系列查询。我创建了一个包含大约200万行的大型临时表，然后使用cur.fetchmany(1000)一次从中获取1000行，并运行更多涉及这些行的查询。不过，这些广泛的查询是自给自足的，一旦它们完成，我就不再需要它们的结果了，当我转到下一个1000。

但是，大约1000000行，我从psycopg2得到一个异常：

psycopg2.OperationalError: out of shared memory
HINT:  You might need to increase max_locks_per_transaction.

有趣的是，当我执行一个查询以删除一些更广泛的查询创建的临时表时，就会发生这种情况。

为什么会这样？有什么办法可以避免呢？这件事发生在半路真是令人恼火，这意味着我必须重新检查一遍。max_locks_per_transaction可能与什么有关？

注意：我没有做任何.commit()s操作，但是我删除了我创建的所有临时表，而且我只为每个“扩展”事务处理相同的5个表，所以我不明白表锁用完是怎么回事。。。

Answer 1

那么，您是在单个事务中运行整个create+查询吗？这或许可以解释这个问题。仅仅因为它发生在删除表的时候并不一定意味着什么，这可能正好是它用完空闲锁的时候。

使用视图可能是临时表的一种替代方法，如果您正在创建此对象，然后立即将其删除，我将首先选择使用视图。

Answer 2

是否创建了多个同名的保存点而不释放它们？

我跟着these instructions，反复执行 SAVEPOINT savepoint_name但从未执行任何相应的RELEASE SAVEPOINT savepoint_name语句。PostgreSQL只是掩盖了旧的保存点，从未释放它们。它一直跟踪每一个锁，直到内存耗尽。我认为我的postgresql内存限制要低得多，我只需要大约10000个保存点就可以达到max_locks_per_transaction。

Answer 3

当您创建一个表时，您将获得一个独占锁，该锁将一直持续到事务结束。即使你把它扔了。

所以如果我启动一个tx并创建一个临时表：

steve@steve@[local] *=# create temp table foo(foo_id int);
CREATE TABLE
steve@steve@[local] *=# select * from pg_locks where pid = pg_backend_pid();
   locktype    | database | relation  | page | tuple | virtualxid | transactionid | classid |   objid   | objsubid | virtualtransaction |  pid  |        mode         | granted 
---------------+----------+-----------+------+-------+------------+---------------+---------+-----------+----------+--------------------+-------+---------------------+---------
 virtualxid    |          |           |      |       | 2/105315   |               |         |           |          | 2/105315           | 19098 | ExclusiveLock       | t
 transactionid |          |           |      |       |            |        291788 |         |           |          | 2/105315           | 19098 | ExclusiveLock       | t
 relation      |    17631 |     10985 |      |       |            |               |         |           |          | 2/105315           | 19098 | AccessShareLock     | t
 relation      |    17631 | 214780901 |      |       |            |               |         |           |          | 2/105315           | 19098 | AccessExclusiveLock | t
 object        |    17631 |           |      |       |            |               |    2615 | 124616403 |        0 | 2/105315           | 19098 | AccessExclusiveLock | t
 object        |        0 |           |      |       |            |               |    1260 |     16384 |        0 | 2/105315           | 19098 | AccessShareLock     | t
(6 rows)

当我删除表时，这些“关系”锁不会被删除：

steve@steve@[local] *=# drop table foo;
DROP TABLE
steve@steve@[local] *=# select * from pg_locks where pid = pg_backend_pid();
   locktype    | database | relation  | page | tuple | virtualxid | transactionid | classid |   objid   | objsubid | virtualtransaction |  pid  |        mode         | granted 
---------------+----------+-----------+------+-------+------------+---------------+---------+-----------+----------+--------------------+-------+---------------------+---------
 virtualxid    |          |           |      |       | 2/105315   |               |         |           |          | 2/105315           | 19098 | ExclusiveLock       | t
 object        |    17631 |           |      |       |            |               |    1247 | 214780902 |        0 | 2/105315           | 19098 | AccessExclusiveLock | t
 transactionid |          |           |      |       |            |        291788 |         |           |          | 2/105315           | 19098 | ExclusiveLock       | t
 relation      |    17631 |     10985 |      |       |            |               |         |           |          | 2/105315           | 19098 | AccessShareLock     | t
 relation      |    17631 | 214780901 |      |       |            |               |         |           |          | 2/105315           | 19098 | AccessExclusiveLock | t
 object        |    17631 |           |      |       |            |               |    2615 | 124616403 |        0 | 2/105315           | 19098 | AccessExclusiveLock | t
 object        |    17631 |           |      |       |            |               |    1247 | 214780903 |        0 | 2/105315           | 19098 | AccessExclusiveLock | t
 object        |        0 |           |      |       |            |               |    1260 |     16384 |        0 | 2/105315           | 19098 | AccessShareLock     | t
(8 rows)

事实上，它又增加了两个锁。。。如果我不断地创建/删除这个临时表，每次都会添加3个锁。

所以我想一个答案是，您将需要足够的锁来处理在整个事务中添加/删除的所有这些表。或者，您可以尝试在查询之间重用临时表，简单地截断它们以删除所有临时数据？