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

On 06/26/2015 06:38 AM, Tim Smith wrote:
> Hi,
>
> Apologies if I am being incredibly stupid, but I just can't seem to
> get this to work for me.
>
> 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

So this is in a plpgsql function?

If so see here:

http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

40.6.6. Trapping Errors

>
>
> app_security.cleanSessionTable works beautifully on its on, i.e. give
> TTL values and it deletes the appropriate roles from the session table
> etc.
>
> 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 ?

Where is the validateSession function?

More to the point, can you show how it is used in conjunction with?

>
> 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.

See the plpgsql link above.

>
> Help !
>
> Thanks
>
> Tim
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com