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

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