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

On 06/26/2015 10:02 AM, Tim Smith wrote:
> Me again, I've reworded it, but its still rolling back !!!
>
> Using the code below, if I call :
> select app_security.validateSession('XYZ','10.10.123.43','Z',5,5);
>
> I get an error raised on the select that follows cleanSessionTable.
> Which is fine.
> BUT, Postgresql is still rolling back !
>
> If I go back afterwards and say
> select app_security.validateSession('XYZ','10.10.123.43','Z',5,5);
>
> I get the session data shown to me again ?
>
>
> 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;
> BEGIN
> perform app_security.cleanSessionTable(p_forcedTimeout,p_sessionTimeout);
> EXCEPTION
> WHEN OTHERS THEN
> RAISE EXCEPTION 'Failed to clean session table % (SQLSTATE: % -
> SQLERRM: %)', session_id,SQLSTATE,SQLERRM
> USING HINT = 'Database error occured (sval cleansess fail)';
> END;
> 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
> RAISE EXCEPTION 'Failed to validate session for session % (SQLSTATE: %
> - SQLERRM: %)', session_id,SQLSTATE,SQLERRM

I would change the above to RAISE NOTICE. The EXCEPTION has already been
raised. Re-raising it without an enclosing block to capture it will I am
pretty sure abort/rollback the function/transaction.

> USING HINT = 'Database error occured (sval fail)';
> END;
> $$ LANGUAGE plpgsql;
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com