I have a function that validates a web session is still active, so my
code looks something like this :
BEGIN
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;
etc. etc.
END
However, when used in conjunction with the broader validateSession
function, whatever cleanSessionTable does gets rolledback because
obviously the select/update statements don't work because cleanSession
table has deleted the expired session ?
As you can see, I've tried adding a savepoint, but this seems to have
no effect ? The autorollback still re-instates the expired session.
You need to trap exceptions and in the handler block issue a
ROLLBACK TO SAVEPOINT
otherwise the the ROLLBACK issued at pg-session end will simply rollback everything.
David J.
