问题描述

在使用postgres执行一个存储过程,存储过程的操作是对全库上百张表添加字段,执行到一半的时候抛出了错误:You might need to increase max_locks_per_transaction。

原因

因为一个过程中操作的表过多,超过了最大对象锁的限制数量,导致了异常。

大意是开启事务后,每次操作一张表,会进行一次表级的lock操作,并增加lock的大小,当lock的大小超过了默认的限定值,就会抛出异常。

postgres中默认max_locks_per_transaction大小是64

官方对此的解释

共享锁表跟踪在max_locks_per_transaction * (max_connections + max_prepared_transactions) 个对象(如表)上的锁。
因此,在任何一个时刻,只有不超过这么多个可区分对象能够被锁住。这个参数控制为每个事务分配的对象锁的平均数量。
个体事务可以锁住更多对象,数量可以和锁表中能容纳的所有事务的锁一样多。这不是能被锁住的行数,那个值是没有限制的。
默认值 64 已经被历史证明是足够的,但是如果你有需要在一个事务中使用很多不同表的查询(例如查询一个有很多子表的父表),你可能需要提高这个值。这个参数只能在服务器启动时设置。

当运行一个后备服务器时,你必须设置这个参数为大于等于主服务器上的值。否则,后备服务器上将不允许查询。

解决方案

1、修改配置文件,../data/postgresql.conf,将参数重新配置:max_locks_per_transaction = 1024,然后重启数据库即可

2、减少过程中操作表的数量,即将一个大的存储过程拆分成若干小过程,再进行执行,最终我们采用的是这个方法,因为根据官方的描述,默认配置是经过验证最合理的配置,将配置调大可能会产生未知的风险
————————————————