PostgreSQL：共享内存不足？
我正在用Python和psycopg2运行一系列查询。我创建了一个大临时表，里面大约有200万行数据，然后我每次从中取出1000行，使用cur.fetchmany(1000)，接着对这些行进行更复杂的查询。这些复杂的查询是独立的，完成后我就不再需要它们的结果了，接着就可以继续处理下一批1000行。
不过，当处理到大约100万行的时候，我收到了psycopg2的一个异常：
psycopg2.OperationalError: out of shared memory
HINT: You might need to increase max_locks_per_transaction.
有趣的是，这个问题发生在我执行一个删除一些临时表的查询时，这些临时表是之前的复杂查询创建的。
这可能是什么原因呢？有没有什么方法可以避免这种情况？这让我很烦，因为发生在一半的时候，意味着我得重新运行一遍。max_locks_per_transaction和这个有什么关系呢？
注意：我并没有做任何.commit()操作，但我删除了所有我创建的临时表，而且每次“复杂”事务只涉及同样的5个表，所以我不明白为什么会出现表锁用完的问题……
3 个回答
你是在一个事务里同时执行创建和查询操作吗？这样可能会解释你遇到的问题。只是因为它发生在你删除表的时候，并不一定意味着什么，这可能只是恰好在那时没有可用的锁了。
使用视图可能是临时表的一个替代方案。如果你是先创建这个东西然后立刻删除它，我会优先考虑使用视图。
你是不是创建了多个同名的保存点，但没有释放它们？
我按照这些步骤操作，反复执行了SAVEPOINT savepoint_name，但是从来没有执行过相应的RELEASE SAVEPOINT savepoint_name。结果，PostgreSQL只是在隐藏旧的保存点，从来没有释放它们。它一直在记录每一个保存点，直到锁的内存用完。我觉得我的PostgreSQL内存限制比较低，只用了大约10,000个保存点，我就达到了max_locks_per_transaction的上限。
当你创建一个表的时候，系统会对这个表加一个独占锁，这个锁会一直保持到事务结束。即使你之后把这个表删掉，锁也不会立刻释放。
比如说，如果我开始一个事务并创建一个临时表：
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)
实际上，这样还会增加两个锁……看起来如果我不断地创建和删除这个临时表，每次都会增加三个锁。
所以我想一个解决办法是，你需要有足够的锁来处理在整个事务中添加和删除的所有表。或者，你可以尝试在查询之间重复使用这些临时表，只需清空它们以移除所有临时数据？