Обсуждение: 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

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.

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,

"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.