Me again, I've reworded it, but its still rolling back !!!
Using the code below, if I call :
select app_security.validateSession('XYZ','10.10.123.43','Z',5,5);
I get an error raised on the select that follows cleanSessionTable.
Which is fine.
BUT, Postgresql is still rolling back !
If I go back afterwards and say
select app_security.validateSession('XYZ','10.10.123.43','Z',5,5);
I get the session data shown to me again ?
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;
BEGIN
perform app_security.cleanSessionTable(p_forcedTimeout,p_sessionTimeout);
EXCEPTION
WHEN OTHERS THEN
RAISE EXCEPTION 'Failed to clean session table % (SQLSTATE: % -
SQLERRM: %)', session_id,SQLSTATE,SQLERRM
USING HINT = 'Database error occured (sval cleansess fail)';
END;
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
RAISE EXCEPTION 'Failed to validate session for session % (SQLSTATE: %
- SQLERRM: %)', session_id,SQLSTATE,SQLERRM
USING HINT = 'Database error occured (sval fail)';
END;
$$ LANGUAGE plpgsql;