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

On Fri, Jun 26, 2015 at 11:38 AM, Tim Smith <randomdev4+postgres(at)gmail(dot)com>
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"
>
>
​I may have led you astray here - though from what you've described (no
checking on my end) apparently the SAVEPOINT is processed and silently
ignored when it seems like it should give the same error as you get when
trying to invoke ROLLBACK TO SAVEPOINT.

David J.