python - PostgreSQL 异常 : DB_Cursor: exception in execute: tuple concurrently updated

Question

作为升级过程的一部分，我们的产品脚本会更新触发器的存储过程。有两个守护进程在运行，其中任何一个都可以更新存储过程。看来PostgrSQL 并没有序列化DDL 来升级程序。确切的错误是“DB_Cursor:执行中的异常:元组同时更新”。谷歌搜索在搜索结果中找不到与此错误完全匹配的结果。看起来我们有一个竞争条件。避免或防止此类异常的最佳方法是什么？它会阻止升级过程成功，并且必须重新启动一个或两个进程(守护进程)以重试升级和恢复。 PostgreSQL 是否存在已知问题？我们正在运行 PostgreSQL 9.2.5。

Answer 1

It seems that PostgreSQL is not serializing the DDL to upgrade the procedure

是的。 pgsql 邮件列表中不时提到这一点，例如最近在这里:

'tuple concurrently updated' error when granting permissions

摘录:

We do have such locking for DDL on tables/indexes, but the theory in the past has been that it's not worth the trouble for objects represented by single catalog rows, such as functions or roles. You can't corrupt the database with concurrent updates on such a row, you'll just get a "tuple concurrently updated" error from all but the first-to-arrive update.

如果您同时替换函数体，这显然是您的问题。

建议的解决方案是:

In the meantime, you could consider using an application-managed advisory lock if you really need such grants to work transparently.