Thanks for the replies (including CB messages). To summarise ...
dkubb was correct in Re: Re: Apache::Session::MySQL when he suggested simply adding a column of type 'timestamp'. The reason I couldn't see any code to implement this is that 'timestamp' is a MySQL built-in that automatically tracks the time a row was last updated.
Since I'm using PostgreSQL, I don't have that exact functionality at my finger tips. dga suggested simply adding a timestamp column with a default value of the current time. This would store the timestamp when the session was created but would not increment the timestamp when the session data was subsequently updated. For 99% of applications, this is probably an adequate solution - if a session was created say 2 days ago then you can probably assume it is no longer being used.
There may be some applications where you can't make that assumption. If sessions might be actively used for an arbitrary length of time, then you need to track when the session was last updated rather than when it was created. In PostgreSQL, you can achieve this with a trigger.
To save someone else having to dig around in the PostgreSQL docs, here's what I did to make the trigger solution work. First of all, since my trigger will use a plpgsql function, I need to install the language in the database by running this from the command line (not from in psql):
createlang -U postgres -P <password> plpgsql <dbname>
Then I need to define the function (from within psql):
CREATE FUNCTION timestamp_row () RETURNS OPAQUE AS '
BEGIN
NEW.timestamp := timeofday();
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
Finally, I need to create a trigger to call the function:
CREATE TRIGGER timestamp_session BEFORE INSERT OR UPDATE ON sessions
FOR EACH ROW EXECUTE PROCEDURE timestamp_row();
Note: you can reuse the same function if you want the same timestamping functionality on other tables. Note also, that the function assumes the standard sessions table definition has been altered like this:
ALTER TABLE sessions ADD COLUMN timestamp timestamp;
ALTER TABLE sessions ALTER COLUMN timestamp SET DEFAULT CURRENT_TIMEST
+AMP;
To re-iterate, this trigger-based solution is only necessary in those cases where the creation timestamp is not good enough. The downside is that the extra overhead of firing the trigger will occur on every update (which presumably will be on every request if the timestamp is to be meaningful).
Detour: While implementing the trigger, I came up against an interesting phenomenom - as I repeatedly requested pages, the timestamp value for my session record sometimes went forwards and sometimes went backwards in time (yikes!). The reason for this was that I had used 'CURRENT_TIMESTAMP' to set the timestamp value. This function is the ANSI equivalent of the PostgreSQL 'now' function, which behaves the same way. Rather than returning the exact current time, CURRENT_TIMESTAMP returns the time at the start of the current transaction. Since I am using Apache::DBI and have enabled autocommit, the start of the current transaction is really the time the last transaction completed. If my request happened to be handled by an Apache process that hadn't been hit recently then its last transaction may have happened quite some time ago and the timestamp would be misleadingly old. To get around this, I used the timeofday() function instead of CURRENT_TIMESTAMP. Unfortunately, I guess this leads to even more overhead on every hit. Only you can decide whether you really need this level of accuracy on your session timestamps. |