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

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