有个存储过程每次执行几个更新和插入的语句，单条语句的数量差不多在5000条左右。
通过另外一个存储过程，循环执行这个存储过程，如果循环次数在4次左右是可以正常执行的，但是循环次数在6次以上就会报“ An I/O error occurred while sending to the backend.”，求解可能是什么问题
CREATE OR REPLACE PROCEDURE p_report_deal_nh_ele_test(in_month character varying)
LANGUAGE plpgsql
AS $procedure$
declare
V_BEGIN_DATE DATE;
V_END_DATE DATE;
exec_begin_time timestamp;
errMsg varchar(4000);
v_year numeric(10);
v_month numeric(10);
v_eleTCE numeric(10, 4) := 1.229;
v_oilTCE numeric(10, 4) := 1.4571;
v_waterTCE numeric(10, 8) := 0.0000857;
BEGIN
exec_begin_time := clock_timestamp() ;
V_BEGIN_DATE := to_date(in_month, 'yyyy-mm');
V_END_DATE := V_BEGIN_DATE+interval'1 months';
v_year := to_number(substr(in_month, 1, 4),'9999');
v_month := to_number(substr(in_month, 6, 7),'99');
--删除 报表 t_account_unit_cal 数据
delete from t_account_unit_cal t
where t.cal_month = in_month
and expert_type = '04'
AND (DATE_CAL_TYPE = '0' OR DATE_CAL_TYPE IS NULL);
--新增数据
--增加统计对象
INSERT INTO t_account_unit_cal
(id,
cal_obj,
unit_id,
unit_name,
cal_month,
area_code,
EXPERT_TYPE,
DATE_CAL_TYPE)
SELECT NEXTVAL('SE_ACCOUNT_UNIT_CAL') ID,
unit_id,
unit_id,
unit_name,
in_month as cal_month,
area_code,
'04' EXPERT_TYPE,
0 DATE_CAL_TYPE
FROM t_account_unit t3
WHERE t3.expert_type = '04';
--更新报表指标
--清空指标
truncate table t_account_unit_cal_mid;
--新增电表指标至中间表
insert into t_account_unit_cal_mid
(cal_obj,unit_id,unit_name,cal_val1,cal_val2,cal_val3,cal_val4,cal_val5,cal_val6,cal_val7,cal_val8,cal_val9,cal_val10,cal_val11,cal_val12,cal_val13,
cal_val72,cal_val73,cal_val74,cal_val100,cal_val105,cal_val116,cal_val109
)
select
unit_id,
unit_id,
unit_name,
t_elec cal_val1,--总用电量
p_elec cal_val2,--生产用电量
m_elec cal_val3,--办公用电量
c_elec cal_val4, --营业用电量
t_money cal_val5,--专票金额
n_money cal_val6,--无专票金额
p_amt cal_val7,--生产用电金额
m_amt cal_val8,--办公用电金额
c_amt cal_val9,--营业用电金额
s_telec cal_val10,--实际自用总电量
s_pelec cal_val11,--实际生产自用电量
s_tamt cal_val12,--实际自用总电费
s_pamt cal_val13,--实际生产自用电费
lastYMFee cal_val72,--用电同期值
lastFee cal_val73,--用电上月值
lastYearFee cal_val74,--用电去年全年值
elecTCE cal_val100, -- 电吨标煤
f_HEDIANF cal_val105, --完成报账的金额数
round(thisAdjust,2) cal_val116, --本月的成效值
f_TAX cal_val109 --总税额
from (
select
u.unit_id,
u.unit_name,
sum(t.t_elec) t_elec,
sum(t.t_amt) t_amt,
sum(t.p_elec) p_elec,
sum(t.p_amt) p_amt,
sum(t.m_elec) m_elec,
sum(t.m_amt) m_amt,
sum(t.c_elec) c_elec,
sum(t.c_amt) c_amt,
sum(t.t_money) t_money,
sum(t.n_money) n_money,
sum(t.s_telec) s_telec,
sum(t.s_tamt) s_tamt,
sum(t.s_pelec) s_pelec,
sum(t.s_pamt) s_pamt,
sum(t.t_eleTCE) as elecTCE, -- 电吨标煤
sum(f_nhhx_report_getlastmass(1,t.id,t.d_year,t.d_month)) lastFee, -- 上个月的电量
sum(f_nhhx_report_getmonthmass(1,t.id,t.d_year-1,t.d_month)) lastYMFee, -- 去年同期值
sum(f_nhhx_report_getyearmass(1,t.id,t.d_year-1)) lastYearFee, -- 去年全年的值
sum(t.thisAdjust) thisAdjust, --本月的成效值
sum(t.f_HEDIANF) f_HEDIANF,
sum(t.f_TAX) f_TAX --税额
from (
select s.unit_id, -- 核算单元
s.name, -- 房屋名称
m.id,
m.meter_num, -- 电表表号
m.elecno, -- 电表户号
l.d_year,
l.d_month,
sum(coalesce(l.totaldianf,0)) as t_elec, --总电量
sum(coalesce(l.hedianf,0)) as t_amt, --总电费
sum(coalesce(l.produce_dl,0)) as p_elec, --生产电量
sum(coalesce(l.produce_df,0)) as p_amt, --生产电费
sum(coalesce(l.manage_dl,0)) as m_elec, --管理电量（办公）
sum(coalesce(l.manage_df,0)) as m_amt, -- 管理电费(办公)
sum(coalesce(l.channel_dl,0)) as c_elec, -- 渠道电量（营业）
sum(coalesce(l.channel_df,0)) as c_amt,-- 渠道电费（营业）
sum(coalesce((case l.is_added when '1' then l.hedianf when '2' then l.hedianf end),0)) as t_money, -- 专票金额
sum(coalesce((case l.is_added when '0' then l.hedianf when '2' then NPINGDIANF+NFENGDIANF+NGUDIANF end),0)) as n_money, --普票金额
sum(coalesce(f_nhhx_report_getselfelec(m.id,l.totaldianf,l.hedianf,0),0)) as s_telec, -- 实际自用总电量（度）
sum(coalesce(f_nhhx_report_getselfelec(m.id,l.totaldianf,l.hedianf,1),0)) as s_tamt, -- 实际自用总电费（元）
sum(coalesce(f_nhhx_report_getselfelec(m.id,l.produce_dl,l.produce_df,0),0)) as s_pelec, -- 实际自用生产电量（度）
sum(coalesce(f_nhhx_report_getselfelec(m.id,l.produce_dl,l.produce_df,1),0)) as s_pamt, -- 实际自用生产电费（元）
sum(coalesce (l.totaldianf * 1.229,0)) as t_eleTCE, -- 总电量转吨标煤
0 as thisAdjust, -- 本月的成效值
SUM(coalesce((case f_nhhx_report_getaccountstat(l.check_sheet) when 2 then l.HEDIANF when 3 then l.HEDIANF else 0 end),0)) AS f_HEDIANF, --完成报账的金额数
SUM(coalesce(l.tax,0)) as f_TAX --总税额
from powuse_site s
left join powuse_ele_meter m on s.id=m.siteid
left join pay_elec_list l on m.id=l.db_id
where l.d_year= v_year and l.d_month= v_month
-- and s.sitetypeid=124 --局用机楼 142 --接入局所 143 --室外机柜 145 --移动基站
and s.is_type is null -- 20210412byzj 剔除铁塔的统计数据
group by s.unit_id,s.id,s.name,m.id,m.meter_num,m.elecno,l.d_year,l.d_month) t
left join t_account_unit u on u.unit_id=t.unit_id
group by u.unit_id,u.unit_name,t.d_year,t.d_month) c;
--更新电表指标
update t_account_unit_cal t
set (cal_val1,cal_val2,cal_val3,cal_val4,cal_val5,cal_val6,cal_val7,cal_val8,cal_val9,cal_val10,cal_val11,cal_val12,cal_val13,
cal_val72,cal_val73,cal_val74,cal_val105,cal_val116,cal_val109) =
(select cal_val1,cal_val2,cal_val3,cal_val4,cal_val5,cal_val6,cal_val7,cal_val8,cal_val9,cal_val10,cal_val11,cal_val12,cal_val13,
cal_val72,cal_val73,cal_val74,cal_val105,cal_val116,cal_val109 from t_account_unit_cal_mid tm
where tm.unit_id=t.unit_id and t.expert_type='04' and t.DATE_CAL_TYPE = '0'
and t.cal_month= in_month) where t.cal_month= in_month and t.expert_type='04' and t.DATE_CAL_TYPE = '0';
--新增成功日志
call p_report_deal_nh_write_log('P_REPORT_DEAL_NH_ELE','Y',0,exec_begin_time, clock_timestamp() ,V_BEGIN_DATE,V_END_DATE,'daily update P_REPORT_DEAL_NH_ELE success');
--异常处理,写入日志
exception when others then
call p_report_deal_nh_write_log('P_REPORT_DEAL_NH_ELE','N',0,exec_begin_time, clock_timestamp() ,V_BEGIN_DATE,V_END_DATE,sqlstate||'>>'||sqlerrm);
END;
$procedure$
;
create or replaceprocedure test2()
language plpgsql
as $procedure$
declare
v_month varchar(2);
begin
for i in 1..12 loop
v_month := to_char(i, 'fm00');
call p_report_deal_nh_ele_test('2022-' || v_month);
end loop;
end;
$procedure$
;
;
;
