On Fri, Jun 26, 2015 at 9:38 AM, Tim Smith <randomdev4+postg...@gmail.com> wrote:
> 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 http://www.postgresql.org/docs/9.4/static/sql-rollback-to.html otherwise the the ROLLBACK issued at pg-session end will simply rollback everything. David J.
