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

ne 25. 10. 2020 v 11:56 odesílatel Sumit Sarkar <sumit(at)techtexas(dot)net>
napsal:

> Dear Suport Team,
>
> We tried to compile the SP below (statement part) with guidance available
> on
> https://www.postgresql.org/docs/11/sql-savepoint.html
>
> 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.

https://www.postgresql.org/docs/current/plpgsql.html

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
> www.techtexas.net
> *AN ISO 27001:2013 Company*
> Success is simple. Do what's right, the right way, at the right time. -
> Arnold H. Glasow
>