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

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"