Обсуждение: Functions, savepoints, autocommit = I am confused !
Hi, Apologies if I am being incredibly stupid, but I just can't seem to get this to work for me. I have a function that validates a web session is still active, so my code looks something like this : BEGIN 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; etc. etc. END app_security.cleanSessionTable works beautifully on its on, i.e. give TTL values and it deletes the appropriate roles from the session table etc. However, when used in conjunction with the broader validateSession function, whatever cleanSessionTable does gets rolledback because obviously the select/update statements don't work because cleanSession table has deleted the expired session ? As you can see, I've tried adding a savepoint, but this seems to have no effect ? The autorollback still re-instates the expired session. Help ! Thanks Tim
I have a function that validates a web session is still active, so my
code looks something like this :
BEGIN
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;
etc. etc.
END
However, when used in conjunction with the broader validateSession
function, whatever cleanSessionTable does gets rolledback because
obviously the select/update statements don't work because cleanSession
table has deleted the expired session ?
As you can see, I've tried adding a savepoint, but this seems to have
no effect ? The autorollback still re-instates the expired session.
You need to trap exceptions and in the handler block issue a
ROLLBACK TO SAVEPOINT
otherwise the the ROLLBACK issued at pg-session end will simply rollback everything.
David J.
On 06/26/2015 06:38 AM, Tim Smith wrote: > Hi, > > Apologies if I am being incredibly stupid, but I just can't seem to > get this to work for me. > > I have a function that validates a web session is still active, so my > code looks something like this : > > BEGIN > 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; > etc. etc. > END So this is in a plpgsql function? If so see here: http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING 40.6.6. Trapping Errors > > > app_security.cleanSessionTable works beautifully on its on, i.e. give > TTL values and it deletes the appropriate roles from the session table > etc. > > However, when used in conjunction with the broader validateSession > function, whatever cleanSessionTable does gets rolledback because > obviously the select/update statements don't work because cleanSession > table has deleted the expired session ? Where is the validateSession function? More to the point, can you show how it is used in conjunction with? > > As you can see, I've tried adding a savepoint, but this seems to have > no effect ? The autorollback still re-instates the expired session. See the plpgsql link above. > > Help ! > > Thanks > > Tim > > -- Adrian Klaver adrian.klaver@aklaver.com
> So this is in a plpgsql function? It is yes, but I thought I would spare you a copy/paste of the entire thing. The error trapping section currently looks like this : EXCEPTION WHEN OTHERS THEN RAISE EXCEPTION 'Failed to validate session for session % (SQLSTATE: % - SQLERRM: %)', session_id,SQLSTATE,SQLERRM USING HINT = 'Database error occured (sval fail)'; END; > > Where is the validateSession function? > > More to the point, can you show how it is used in conjunction with? > The validateSession function was the one I pasted ? Do you mean you want to see the actual function() definition at the top ? The cleanSession function (the one validateSession calls at the top) is simple (the v_ values are simply 'epoch minus TTL') : BEGIN delete from app_security.app_sessions where session_start<=v_forcedTimeout or session_lastactive<=v_sessionTimeout; EXCEPTION WHEN OTHERS THEN RAISE EXCEPTION USING ERRCODE = sqlstate, MESSAGE = 'Failed to clean session table (' || sqlerrm || ')', HINT = 'Database error(sclean fail)'; END; $$ LANGUAGE plpgsql; > > See the plpgsql link above. Will take a look at the link. Thanks !
> You need to trap exceptions and in the handler block issue a > > ROLLBACK TO SAVEPOINT > > http://www.postgresql.org/docs/9.4/static/sql-rollback-to.html > > > otherwise the the ROLLBACK issued at pg-session end will simply rollback > everything. > > David J. > Thanks, will take a look.
Hi David, I should have perhaps made clear this was a saved function, so my understanding is ROLLBACK can't be used as its implicit.
Hi David,
I should have perhaps made clear this was a saved function, so my
understanding is ROLLBACK can't be used as its implicit.
I am pretty certain "ROLLBACK" cannot be used but the "ROLLBACK TO SAVEPOINT" can - they are and do two different things. If you can issue a savepoint inside a stored function it would stand to reason you must be able to rollback to that named savepoint from within the same - nothing external would even known about it.
David J.
On 06/26/2015 07:24 AM, Tim Smith wrote: >> So this is in a plpgsql function? > > It is yes, but I thought I would spare you a copy/paste of the entire thing. > > The error trapping section currently looks like this : > EXCEPTION > WHEN OTHERS THEN > RAISE EXCEPTION 'Failed to validate > session for session % (SQLSTATE: % - SQLERRM: %)', > session_id,SQLSTATE,SQLERRM > USING HINT = 'Database error occured > (sval fail)'; > END; > >> >> Where is the validateSession function? >> >> More to the point, can you show how it is used in conjunction with? >> > > The validateSession function was the one I pasted ? Do you mean you > want to see the actual function() definition at the top ? > > The cleanSession function (the one validateSession calls at the top) Well what you showed before was cleanSessionTable, are we talking the same thing? > is simple (the v_ values are simply 'epoch minus TTL') : > BEGIN > delete from app_security.app_sessions where > session_start<=v_forcedTimeout or > session_lastactive<=v_sessionTimeout; > EXCEPTION > WHEN OTHERS THEN > RAISE EXCEPTION > USING ERRCODE = sqlstate, > MESSAGE = 'Failed to clean session table (' || sqlerrm || ')', > HINT = 'Database error(sclean fail)'; > END; > $$ LANGUAGE plpgsql; > >> >> See the plpgsql link above. > > > Will take a look at the link. Thanks ! > > -- Adrian Klaver adrian.klaver@aklaver.com
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"
Tim Smith wrote on Friday, June 26, 2015 5:38 PM: > 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 believe I've read you can have nested BEGIN ... END blocks, and the transaction control is done implicitly by the PL/pgSQLexception handling, so you probably can write BEGIN ... BEGIN ... EXCEPTION WHEN OTHERS THEN ... END END; which would (hopefully) only roll back the second ... not the first ... (not sure if you still need to declare the savepoint,at least, as you found out, explicitly rolling back to the savepoint is not allowed in PL/pgSQL). Note that thethird ... probably should not raise or re-raise an exception, otherwise you have an exception in the outer BEGIN-END blockand everything is rolled back. Best regards Holger Friedrich
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
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.
Adrian, "what I want" is quite simple, I want the function to work as intended. ;-) Let's step through the function : (1) perform app_security.cleanSessionTable(p_forcedTimeout,p_sessionTimeout); Function calls cleanSessionTable. cleanSessionTable is simple. It calls DELETE on the session table using epochs as filters. That's fine, it works, I've tested that function. The reason I want cleanSessionTable called here is because this is the back-end to a web app. This function is called "validateSession", hence it needs to do what it says on the tin and make sure expired sessions are not validated. The problem happens next .... (2) select * into strict v_row .etc IF cleanSessionTable deleted the row, then this select will fail. Which is fine ... EXCEPT for the fact that Postgresql will then roll-back the good work it did on the previous statement (cleanSessionTable). I want the deleted session rows to remain deleted. I don't want them back.
On 06/26/2015 09:08 AM, Tim Smith wrote: > Adrian, > > "what I want" is quite simple, I want the function to work as intended. ;-) Well that was my problem, I did not know what was intended. > > Let's step through the function : > > (1) perform app_security.cleanSessionTable(p_forcedTimeout,p_sessionTimeout); > > Function calls cleanSessionTable. cleanSessionTable is simple. It > calls DELETE on the session table using epochs as filters. That's > fine, it works, I've tested that function. > > The reason I want cleanSessionTable called here is because this is the > back-end to a web app. This function is called "validateSession", > hence it needs to do what it says on the tin and make sure expired > sessions are not validated. > > The problem happens next .... > > (2) select * into strict v_row .etc > > IF cleanSessionTable deleted the row, then this select will fail. > Which is fine ... EXCEPT for the fact that Postgresql will then > roll-back the good work it did on the previous statement > (cleanSessionTable). > > I want the deleted session rows to remain deleted. I don't want them back. Two options that I can see if I am following correctly: 1) Look before you leap Before this: update app_security.app_sessions set session_lastactive=v_now where session_id=p_session_id; Do: In the DECLARE ct_var integer; select count(*) into ct_var from app_security.app_sessions where session_id=p_session_id and then use IF on the ct_var to either UPDATE if cat_var > 0 or just pass if = 0 2) Act and then ask for forgiveness. You can have more then one BEGIN/END block in plpgsql. So you could put the update in its own block and catch the exception there. See: http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING Example 40-2. Exceptions with UPDATE/INSERT > > -- Adrian Klaver adrian.klaver@aklaver.com
> Well that was my problem, I did not know what was intended. apt-get install mind-reading ;-) > 1) Look before you leap > I'm confused by this option ? My script reads as follows : perform app_security.cleanSessionTable(p_forcedTimeout,p_sessionTimeout); 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; Its the "select * into strict" that's causing me grief, not the "update", isn't it ?!? > > 2) Act and then ask for forgiveness. > > You can have more then one BEGIN/END block in plpgsql. So you could put the > update in its own block and catch the exception there. I'll give that a go. I thought it might be an option, but it was not too clear from the docs whether the sub-blocks were treated as seperate transactions when used within a function.
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 USING HINT = 'Database error occured (sval fail)'; END; $$ LANGUAGE plpgsql;
On 06/26/2015 09:54 AM, Tim Smith wrote: >> Well that was my problem, I did not know what was intended. > > apt-get install mind-reading ;-) > >> 1) Look before you leap >> > > I'm confused by this option ? > > My script reads as follows : > perform app_security.cleanSessionTable(p_forcedTimeout,p_sessionTimeout); > 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; > > > Its the "select * into strict" that's causing me grief, not the > "update", isn't it ?!? 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. 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. > > > >> >> 2) Act and then ask for forgiveness. >> >> You can have more then one BEGIN/END block in plpgsql. So you could put the >> update in its own block and catch the exception there. > > I'll give that a go. I thought it might be an option, but it was not > too clear from the docs whether the sub-blocks were treated as > seperate transactions when used within a function. > > -- Adrian Klaver adrian.klaver@aklaver.com
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.klaver@aklaver.com
> > 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;
On 06/26/2015 10:49 AM, Tim Smith wrote: >> >> 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 ? Yes, they are different ways of approaching the problem. > > 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. I knew I was missing something:( http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING "When an error is caught by an EXCEPTION clause, the local variables of the PL/pgSQL function remain as they were when the error occurred, but all changes to persistent database state within the block are rolled back. As an example, consider this fragment:" So I would try 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 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; BEGIN v_now := extract(epoch FROM now())::bigint; 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; END; $$ LANGUAGE plpgsql; -- Adrian Klaver adrian.klaver@aklaver.com
> > > I knew I was missing something:( > http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING > > "When an error is caught by an EXCEPTION clause, the local variables of the > PL/pgSQL function remain as they were when the error occurred, but all > changes to persistent database state within the block are rolled back. As an > example, consider this fragment:" > > > So I would try You are a genius. ;-) Thank you ! Have a delightful weekend.