On 06/26/2015 08:38 AM, Tim Smith wrote:
> Adrian,
>
> Ok, let's start fresh.
>
> app_security.validateSession() calls app_security.cleanSessionTable().
>
> app_security.cleanSessionTable(), when called on its, own, does not
> cause me any issues. It operates as designed.
>
> I have added ROLLBACK TO SAVEPOINT to validateSession(), so that it now reads :
>
> 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;
> 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;
> select row_to_json(v_row) into v_json ;
> return v_json;
> EXCEPTION
> WHEN OTHERS THEN
> ROLLBACK TO SAVEPOINT sp_cleanedSessionTable;
> RAISE EXCEPTION 'Failed to validate session for session % (SQLSTATE: %
> - SQLERRM: %)', session_id,SQLSTATE,SQLERRM
> USING HINT = 'Database error occured (sval fail)';
> END;
> $$ LANGUAGE plpgsql;
>
>
> Calling the function yields the following :
>
> ERROR: cannot begin/end transactions in PL/pgSQL
> HINT: Use a BEGIN block with an EXCEPTION clause instead.
> CONTEXT: PL/pgSQL function
> app_security.validatesession(app_domains.app_uuid,inet,text,bigint,bigint)
> line 16 at SQL statement
>
>
> Line 16 to which it refers is "ROLLBACK TO SAVEPOINT"
Well AFAIK, you can not explicitly use SAVEPOINT in plpgsql as the
EXCEPTION handling is implicitly using it.
Still not quite sure what you want.
Are you looking to catch any exception coming from cleanSessionTable and
then abort the 'select into * .." section?
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com