Обсуждение: Postgresql procedure failing to compile when rollback to savepoint is mentioned

Поиск
Список
Период
Сортировка

Postgresql procedure failing to compile when rollback to savepoint is mentioned

От
Sumit Sarkar
Дата:
Dear Suport Team,

We tried to compile the SP below (statement part) with guidance available on

Our aim was to create procedure with transaction rollback on error. We tried to compile the given SP and wanted to run it (which should throw error as the primary key (ID field) has the same value in the second INSERT. On encountering the error, the SP should go to savepoint a without committing the first INSERT.

However, we could not handle exceptions with rollback to user-defined SAVEPOINT. The system is throwing error when we try to use ROLLBACK TO SAVEPOINT (mentioned in your manual). Apparently this is a bug as it defies your own example. Please let us know once it is fixed or suggest the alternative at the earliest.

"

SQL error:

ERROR:  syntax error at or near "TO"
LINE 14: ROLLBACK TO SAVEPOINT a;                  ^

In statement:
CREATE OR REPLACE PROCEDURE transaction_test()
LANGUAGE plpgsql
AS $$
DECLARE
BEGIN
SAVEPOINT a;
insert into public.aocodes(id,cc_id,name,assigned_to,status,entity,created_by)
select 4,4,'101201',8,'Active',1,1;

insert into public.aocodes(id,cc_id,name,assigned_to,status,entity,created_by)
select 4,5,'101201',8,'Active',1,1;
exception
when others then
ROLLBACK TO SAVEPOINT a;
RELEASE SAVEPOINT a;
Commit;
END $$;

"




Thanks & Regards
Sumit Sarkar
Managing Consultant
0091-8697727443
0091-8902727443
TechTexas Technical Services LLP
AN ISO 27001:2013 Company
Success is simple. Do what's right, the right way, at the right time. - Arnold H. Glasow

Re: Postgresql procedure failing to compile when rollback to savepoint is mentioned

От
Pavel Stehule
Дата:


ne 25. 10. 2020 v 11:56 odesílatel Sumit Sarkar <sumit@techtexas.net> napsal:
Dear Suport Team,

We tried to compile the SP below (statement part) with guidance available on

Our aim was to create procedure with transaction rollback on error. We tried to compile the given SP and wanted to run it (which should throw error as the primary key (ID field) has the same value in the second INSERT. On encountering the error, the SP should go to savepoint a without committing the first INSERT.

However, we could not handle exceptions with rollback to user-defined SAVEPOINT. The system is throwing error when we try to use ROLLBACK TO SAVEPOINT (mentioned in your manual). Apparently this is a bug as it defies your own example. Please let us know once it is fixed or suggest the alternative at the earliest.

"

SQL error:

ERROR:  syntax error at or near "TO"
LINE 14: ROLLBACK TO SAVEPOINT a;                  ^

In statement:
CREATE OR REPLACE PROCEDURE transaction_test()
LANGUAGE plpgsql
AS $$
DECLARE
BEGIN
SAVEPOINT a;
insert into public.aocodes(id,cc_id,name,assigned_to,status,entity,created_by)
select 4,4,'101201',8,'Active',1,1;

insert into public.aocodes(id,cc_id,name,assigned_to,status,entity,created_by)
select 4,5,'101201',8,'Active',1,1;
exception
when others then
ROLLBACK TO SAVEPOINT a;
RELEASE SAVEPOINT a;
Commit;
END $$;

"

This is not a bug. This feature is not supported. For Postgres you can do just

CREATE OR REPLACE FUNCTION test()
RETURNS VOID AS $$
BEGIN
  INSERT INTO xxx VALUES;
  EXCEPTION WHEN OTHERS THEN
    /* do nothing, rollback to savepoint is implicit */
END;
$$ LANGUAGE plpgsql;

run this function under autocommit mode or commit explicitly.

Your code is very Oraclish. Handling exceptions is pretty different in Postgres - please, start by documentation reading.


Regards

Pavel Stehule

p.s. Don't use INSERT SELECT when INSERT VALUES is enough - your code is slower, and looks messy

 




Thanks & Regards
Sumit Sarkar
Managing Consultant
0091-8697727443
0091-8902727443
TechTexas Technical Services LLP
AN ISO 27001:2013 Company
Success is simple. Do what's right, the right way, at the right time. - Arnold H. Glasow