Adrian,
Ok, let's start fresh.
app_security.validateSession() calls app_security.cleanSessionTable().
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.
David J.
