how to use SAVEPOINT in stored function

Started by Frank Motzkat/IC3S AGover 20 years ago3 messagesgeneral
Jump to latest
#1Frank Motzkat/IC3S AG
frank.motzkat@ic3s.de

Hi community,

I would like using savepoints in my stored functions but I always get the
error

ERROR: SPI_execute_plan failed executing query "SAVEPOINT my_savepoint":
SPI_ERROR_TRANSACTION
CONTEXT: PL/pgSQL function "savepoint_test" line 3 at SQL statement

My test function can be found below. I would be very grateful for any hint
which brings progress to my developments ...

-- create table
CREATE TABLE testtable
(
name varchar(256),
number int4 DEFAULT 1,
id varchar(64) NOT NULL,
CONSTRAINT pk_id PRIMARY KEY (id)
)
WITHOUT OIDS;
ALTER TABLE testtable OWNER TO postgres;

-- insert dummy record
insert into testtable (id,number) values ('id_1', 1);

-- create test function
CREATE OR REPLACE FUNCTION savepoint_test
(
in_no integer,
in_name varchar,
in_id varchar
) RETURNS void
AS $$
BEGIN
BEGIN
SAVEPOINT my_savepoint;
DELETE FROM testtable WHERE number = in_no;
insert into testtable (id,number) values ('id_2', 2);
-- COMMIT;
RELEASE SAVEPOINT my_savepoint;
EXCEPTION
WHEN unique_violation THEN
ROLLBACK TO my_savepoint;
END;
END
$$ LANGUAGE plpgsql;

-- call test function
select * from savepoint_test(1, CAST('test-1' AS VARCHAR), CAST('id_1' AS
VARCHAR));

regards,

frank

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Frank Motzkat/IC3S AG (#1)
Re: how to use SAVEPOINT in stored function

Frank.Motzkat@ic3s.de writes:

I would like using savepoints in my stored functions but I always get the
error
ERROR: SPI_execute_plan failed executing query "SAVEPOINT my_savepoint":

In plpgsql, you're supposed to use exception blocks instead. See
http://www.postgresql.org/docs/8.1/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

regards, tom lane

#3Volkan YAZICI
volkan.yazici@gmail.com
In reply to: Tom Lane (#2)
Re: how to use SAVEPOINT in stored function

On 12/5/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:

In plpgsql, you're supposed to use exception blocks instead.

AFAIC, SAVEPOINT usage isn't permitted in any procedural language. But
it also isn't documented. (I couldn't find any phrase mentioning about
this in the docs.)

One more question, if we'd try to use a SAVEPOINT inside an SQL
function, it dumps below error:
ERROR: SAVEPOINT is not allowed in a SQL function
CONTEXT: SQL function "tmp_func" during startup
But we got below error message if we'd try the same in a pl/pgsql precedure:
ERROR: SPI_execute_plan failed executing query "SAVEPOINT svp0":
SPI_ERROR_TRANSACTION
CONTEXT: PL/pgSQL function "tmp_func" line 4 at SQL statement

Is it possible to make the latter error message some more informative
and user-friendly like the one in the previous?

Regards.