app_security.cleanSessionTable(), when called on its, own, does not cause me any issues. It operates as designed.
I have added ROLLBACK TO SAVEPOINT to validateSession(), so that it now reads :
CREATE FUNCTION app_security.validateSession(p_session_id app_domains.app_uuid,p_client_ip inet,p_user_agent text,p_forcedTimeout bigint,p_sessionTimeout bigint) RETURNS json AS $$ DECLARE v_now bigint; v_row app_security.app_val_session_vw%ROWTYPE; v_json json; BEGIN v_now := extract(epoch FROM now())::bigint; perform app_security.cleanSessionTable(p_forcedTimeout,p_sessionTimeout); SAVEPOINT sp_cleanedSessionTable; select * into strict v_row from app_security.app_val_session_vw where session_id=p_session_id and session_ip=p_client_ip and session_user_agent=p_user_agent; update app_security.app_sessions set session_lastactive=v_now where session_id=p_session_id; select row_to_json(v_row) into v_json ; return v_json; EXCEPTION WHEN OTHERS THEN ROLLBACK TO SAVEPOINT sp_cleanedSessionTable; RAISE EXCEPTION 'Failed to validate session for session % (SQLSTATE: % - SQLERRM: %)', session_id,SQLSTATE,SQLERRM USING HINT = 'Database error occured (sval fail)'; END; $$ LANGUAGE plpgsql;
Calling the function yields the following :
ERROR: cannot begin/end transactions in PL/pgSQL HINT: Use a BEGIN block with an EXCEPTION clause instead. CONTEXT: PL/pgSQL function app_security.validatesession(app_domains.app_uuid,inet,text,bigint,bigint) line 16 at SQL statement
Line 16 to which it refers is "ROLLBACK TO SAVEPOINT"
I may have led you astray here - though from what you've described (no checking on my end) apparently the SAVEPOINT is processed and silently ignored when it seems like it should give the same error as you get when trying to invoke ROLLBACK TO SAVEPOINT.