> > Did not see the strict. In any case I thought cleanSessionTable was cleaning > out app_security.app_sessions not app_security.app_val_session_vw. Yes. cleanSessionTable does the actual cleaning. The point of the select from app_security.app_val_session_vw is that if the session is valid, the function returns a JSON containing pertinent information relating to the session. > > Assuming something else is going you have two options(sense a theme?): > > 1) Remove the strict and do as I suggested in the previous post. > > 2) Move the count and IF before the select * into .. and then do what you > want. > So are you saying I need to do both this counting stuff AND the "ask for forgiveness", I thought you were suggesting mutuallly exclusive options earlier ? I'll work on integrating the count stuff now, but I still don't understand why a BEGIN subblock still gets rolled back. This is on 9.4.4 if it makes any difference, by the way. >>> 2) Act and then ask for forgiveness. >>> Regarding this part, I have changed to RAISE NOTICE and added a return to the bottom of the Pl/PGSQL. The function does not abort now, I get a simple : NOTICE: Failed to validate session for session XYZ (SQLSTATE: P0002 - SQLERRM: query returned no rows) HINT: Database error occured (sval fail) validatesession ----------------- [false] (1 row) But the problem persists in that the delete still gets rolled back, despite it being in its own sub block. 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 NOTICE 'Failed to validate session for session % (SQLSTATE: % - SQLERRM: %)', p_session_id,SQLSTATE,SQLERRM USING HINT = 'Database error occured (sval fail)'; return '[false]'; END; $$ LANGUAGE plpgsql; -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
