Functions, savepoints, autocommit = I am confused !

From: Tim Smith <randomdev4+postgres(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Functions, savepoints, autocommit = I am confused !
Date: 2015-06-26 13:38:42
Message-ID: CA+HuS5GvTpjRAwKaLroWrmyEih1eyKScMGjg--Uy_XTi-Q2qaA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2015-06-26 13:52:25 Re: Functions, savepoints, autocommit = I am confused !
Previous Message Sylvain MARECHAL 2015-06-26 06:56:30 Re: BDR: Can a node live alone after being detached