ISOLATION LEVEL SERIALIZABLE locking postgresql 9.6

Question

DO $$
BEGIN
raise notice '%', (SELECT * from public.clientcalledthisfunction(1,2));
END $$;

CREATE OR REPLACE FUNCTION  public.clientcalledthisfunction(userid1_ integer, userid2_ integer)
RETURNS integer
AS $$
DECLARE
result integer;
BEGIN
result:=(SELECT  * from public.call_updatedata(userid1_, userid2_)) ;
RETURN result;
EXCEPTION WHEN others THEN
End $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION public.call_updatedata(userid1_ integer, userid2_ integer)
RETURNS integer
AS $$
DECLARE
userdata_1 integer;
userdata_2 integer;
userdata_total integer;
BEGIN

SELECT * FROM public.updatedata(userid1_) INTO userdata_1;

SELECT * FROM public.updatedata(userid2_) INTO userdata_2;

userdata_total:=(userdata_1 + userdata_2);
RETURN userdata_total;
EXCEPTION WHEN others THEN
End $$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION public.updatedata(userid_ integer)
RETURNS integer
AS $$
DECLARE
userdata_ integer;
BEGIN

LOOP
SET  TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN
SAVEPOINT foo;

SELECT userdata FROM public.footable WHERE userid=userid_ INTO userdata_;

UPDATE public.footable SET userdata = userdata_ + 1 WHERE userid=userid_ ;

EXIT ;
EXCEPTION WHEN others THEN
    ROLLBACK TO SAVEPOINT foo;
END;
END LOOP;
RETURN userdata_ + 1;
EXCEPTION WHEN others THEN
END $$ language plpgsql;

Client calls public.clientcalledthisfunction() function;

I need to implement ISOLATION LEVEL SERIALIZABLE on SELECT + UPDATE in updatedata() function, because i dont want "lost update"...I want to set SERIALIZABLE isolation level only in public.updatedata function().

And in updatedata() function if there is an exception; i want it to rollback to savepoint foo and retry select + update process through loop again...

But i get error saying "control reached end of function without RETURN"...I can't understand where the problem is.

Answer 1

I want to set SERIALIZABLE isolation level only in public.updatedata function().

You can't. Isolation is a transaction-level property.

And in updatedata() function if there is an exception; i want it to rollback to savepoint foo

You can use a BEGIN ... EXCEPTION ... block for that.

But this wouldn't actually work if you were using serializabile isolation since some serialization failures can only be found at commit time.

Answer 2

In every function you have

EXCEPTION WHEN others THEN
End

at the end. If any exception occures then this piece of code fires. And since there is no return statement afterwards it crashes with "control reached end of function without RETURN" error.

Analyze those exceptions and you will know exactly what is going on. Or even better, simply remove those lines. After all EXCEPTION WHEN others is a root of all evil.

---

EDIT: After some searching it seems that you can't change isolation level inside a function. After all calling a function is a query and isolation level has to be set before any query.