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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
