RDS Postgres 12.7 Procedure Stopped Working - invalid transaction termination

0

We have a stored procedure that has been running without issue for months in PG12.7 on RDS. The procedure is shown below. Last night around 6PM MT this procedure stopped working. The error throw is ERROR: invalid transaction termination. Nothing around our database configuration or schema has changed. Our RDS instance is no longer capable of running any commits inside a procedure, even from known good code. We've even scraped the web to find examples of simple commit procedures that work and none do.

Is it possible some patch was applied behind the scenes that incapacitated our ability to run these procedures? Seems like a stretch but it's all ive got.


CREATE OR REPLACE PROCEDURE public.me_cpro_session_insert(integer)
  LANGUAGE plpgsql
AS
$body$
begin
insert into me_cpro_session(patient_guid,last_name,first_name,id_session,start_date,end_date,is_complete,instrument,question,position,choice,range_choice,score,instrument_score,appointment_guid)
select
p.id_external as patient_guid,
p.last_name,
p.first_name,
s.id as id_session,
s.start_date,
s.end_date - interval '5 hours' as end_date,
s.is_complete,
qs.name as instrument,
t1.text as question,
lq.position,
t2.text as choice,
r.range_choice,
c.score,
sc.score as instrument_score,
s.id_appointment
from response r
join session s on r.id_session = s.id
join patient p on p.id = s.id_patient
join question_set qs on qs.id = r.id_question_set
join link_question lq on lq.id_question = r.id_question and lq.id_question_set = r.id_question_set
join question q on r.id_question = q.id
left join choice c on r.id_choice = c.id
join translation t1 on t1.id_expression = q.id_expression and t1.id_language = 2
left join translation t2 on t2.id_expression = c.id_expression and t2.id_language = 2
left join score sc on sc.id_session = s.id and sc.id_question_set = r.id_question_set
where s.id = $1
order by qs.id asc, lq.position asc;
commit;
END;
$body$
;
已提问 2 年前1857 查看次数
1 回答
0

Hello,

I understand that an error message "invalid transaction termination" occurred when calling a stored procedure and you want to know if patch was applied on the DB Instance.

In order to check the DB Instance, please open a support case with AWS using the following https://console.aws.amazon.com/support/home#/case/create and we can check the DB Instance prior and during the time of the error message to see if there are issue that may have occurred the time frame of the issue.

On further research, the error message can be due to one of the below reasons :

  • There may have been custom settings on the database or which are set for session during the execution of the above stored procedures.
  • Explicitly starting a transaction with BEGIN before calling the stored procedure.
  • if there are SECURITY DEFINER in the stored procedure.

Recommendations :

AWS
支持工程师
Somto_M
已回答 2 年前

您未登录。 登录 发布回答。

一个好的回答可以清楚地解答问题和提供建设性反馈,并能促进提问者的职业发展。

回答问题的准则