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(dot)klaver(at)aklaver(dot)com