django.db.utils.DatabaseError : out of shared memory 错误

Question

我编写了一个脚本，使用 Django ORM 将一些对象加载到 Django 数据库中。底层数据库是 Postgres。

愉快的运行了一段时间后，脚本运行失败，报错:

django.db.utils.DatabaseError: out of shared memory
HINT: You might need to increase max_locks_per_transaction.

我猜这是我的脚本效率问题，而不是数据库设置问题。

脚本遍历 CSV 文件，并为 CSV 文件中的每一行创建一个数据库对象。通常有几千个对象要创建。我读过 some background material on database efficiency in Django .我可以排除一些错误 - 我没有遍历查询集，也没有使用 __in 查询或 OFFSET。

但我的数据库中的字段确实有很多索引，我想每次创建和保存对象时，Django 都必须更新所有索引。例如，我在 StoreItem 字段上有六个索引。

for item in csv_rows: 
  s, created = StoreItem.objects.get_or_create(display_url=item['display_url'], \
         retailer_img_url=item['retailer_img_url'],store=store_obj)
  s.name = item['name']
  s.description = item['description']
  s.affiliate = item['affiliate']
  ... more stuff
  s.save()

两个问题:

1. 更新数据库索引是否可能导致此错误？
2. 如果是这种情况，我该如何调试？

Answer 1

我快速谷歌了一下，有几个资源可供查看:

1. postgresql: out of shared memory?
2. http://www.databasesoup.com/2012/06/postgresqlconf-maxlockspertransaction.html

此引述摘自http://www.postgresql.org/docs/9.1/static/runtime-config-locks.html

max_locks_per_transaction (integer)

The shared lock table tracks locks on max_locks_per_transaction * (max_connections + max_prepared_transactions) objects (e.g., tables);hence, no more than this many distinct objects can be locked at anyone time. This parameter controls the average number of object locksallocated for each transaction; individual transactions can lock moreobjects as long as the locks of all transactions fit in the locktable. This is not the number of rows that can be locked; that valueis unlimited. The default, 64, has historically proven sufficient, butyou might need to raise this value if you have clients that touch manydifferent tables in a single transaction. This parameter can only beset at server start.

Increasing this parameter might cause PostgreSQL to request more System V shared memory than your operating system's defaultconfiguration allows. See Section 17.4.1 for information on how toadjust those parameters, if necessary.

When running a standby server, you must set this parameter to the same or higher value than on the master server. Otherwise, querieswill not be allowed in the standby server.

因此，看起来您应该检查 max_locks_per_transaction 的默认值以检查其设置是否合理

同样，如果您使用默认安装的 postgres，那么您可能应该检查所有其他默认值，因为在某些情况下默认值可能设置得太低。