Re: Functions, savepoints, autocommit = I am confused !

On Fri, Jun 26, 2015 at 9:38 AM, Tim Smith <randomdev4+postgres(at)gmail(dot)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.​