postgresql：共享内存不足？
我正在使用 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() 操作，但我正在删除我创建的所有临时表，而且对于每个“大量”事务，我只触及相同的 5 个表，所以我不明白表锁耗尽可能会成为问题......
I'm running a bunch of queries using Python and psycopg2. I create one large temporary table w/ about 2 million rows, then I get 1000 rows at a time from it by using cur.fetchmany(1000) and run more extensive queries involving those rows. The extensive queries are self-sufficient, though - once they are done, I don't need their results anymore when I move on to the next 1000.
However, about 1000000 rows in, I got an exception from psycopg2:
psycopg2.OperationalError: out of shared memory
HINT: You might need to increase max_locks_per_transaction.
Funnily enough, this happened when I was executing a query to drop some temporary tables that the more extensive queries created.
Why might this happen? Is there any way to avoid it? It was annoying that this happened halfway through, meaning I have to run it all again. What might max_locks_per_transaction have to do with anything?
NOTE: I'm not doing any .commit()s, but I'm deleting all the temporary tables I create, and I'm only touching the same 5 tables anyway for each "extensive" transaction, so I don't see how running out of table locks could be the problem...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
当您创建一个表时，您将获得一个持续到事务结束的排他锁。即使你随后继续放弃它。
因此，如果我启动一个 tx 并创建一个临时表：
当我删除该表时，这些“关系”锁不会被删除：
事实上，它又添加了两个锁...似乎如果我不断创建/删除该临时表，每次都会加3把锁。
所以我想一个答案是您将需要足够的锁来应对整个事务中添加/删除的所有这些表。或者，您可以尝试在查询之间重用临时表，只需截断它们即可删除所有临时数据？
when you create a table, you get an exclusive lock on it that lasts to the end of the transaction. Even if you then go ahead and drop it.
So if I start a tx and create a temp table:
These 'relation' locks aren't dropped when I drop the table:
In fact, it added two more locks... It seems if I continually create/drop that temp table, it adds 3 locks each time.
So I guess one answer is that you will need enough locks to cope with all these tables being added/dropped throughout the transaction. Alternatively, you could try to reuse the temp tables between queries, simply truncate them to remove all the temp data?
您是否创建了多个同名保存点而不释放它们？
我按照这些说明，重复执行
SAVEPOINT savepoint_name但从未执行任何相应的RELEASE SAVEPOINT savepoint_name语句。 PostgreSQL 只是屏蔽了旧的保存点，从不释放它们。它会跟踪每一个，直到耗尽锁的内存为止。我认为我的 postgresql 内存限制要低得多，只需要大约 10,000 个保存点就可以达到 max_locks_per_transaction。Did you create multiple savepoints with the same name without releasing them?
I followed these instructions, repeatedly executing
SAVEPOINT savepoint_namebut without ever executing any correspondingRELEASE SAVEPOINT savepoint_namestatements. PostgreSQL was just masking the old savepoints, never freeing them. It kept track of each until it ran out of memory for locks. I think my postgresql memory limits were much lower, it only took ~10,000 savepoints for me to hit max_locks_per_transaction.那么，您是否在单个事务中运行整个创建+查询？这也许可以解释这个问题。仅仅因为它发生在您删除表时并不一定意味着什么，这可能恰好是它耗尽可用锁的时间点。
使用视图可能是临时表的替代方案，如果您要创建这个东西然后立即删除它，我肯定会首先选择它。
Well, are you running the entire create + queries inside a single transaction? This would perhaps explain the issue. Just because it happened when you were dropping tables would not necessarily mean anything, that may just happen to be the point when it ran out of free locks.
Using a view might be an alternative to a temporary table and would definitely by my first pick if you're creating this thing and then immediately removing it.