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$
;
asked 2 years ago1832 views
1 Answer
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
SUPPORT ENGINEER
Somto_M
answered 2 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions