Each transaction (consisting of one or more entries) has a session id. But this id is not unique globally. I noticed that in jsp pages (written via struts, java, jsp), the same session id will be reused once it has been destroyed. In php pages (IA), session id is reused less, but this problem still exists.
To create unique session IDs:
step1: create a composite index first by session_id, then by id
step2: order all entries first by session_id, then by id
then
CREATE OR REPLACE FUNCTION make_session_unique(OUT num INTEGER) AS $$
DECLARE
currow RECORD;
viewed timestamp without time zone;
session text;
newid BIGINT;
BEGIN
viewed = '3000-01-1';
session = '1';
FOR currow IN (SELECT id, php_session_id, time_viewed
FROM tracking_page_hits ORDER BY php_session_id, id)
LOOP
IF (session <> currow.php_session_id) THEN
newid = currow.id;
session = currow.php_session_id;
ELSIF viewed < currow.time_viewed - INTERVAL '30 MINUTES'
THEN
newid = currow.id;
END IF;
UPDATE
tracking_page_hits
SET
new_session_id = php_session_id || '--' || newid
WHERE
id = currow.id;
viewed = currow.time_viewed;
END LOOP;
END;
$$ LANGUAGE plpgsql;
step3: drop the composite index
Subscribe to:
Post Comments (Atom)
How did this work for you?
ReplyDeleteIn looking at the code I see where it changes the currow's new_session_id, but shouldn't you change the newid to be currow.id only when:
viewed > currow.time_viewed - INTERVAL '30 MINUTES'
Perhaps I am confused... But, if you do it < 30 minutes then you are changing the tail of everything in that particular session---thus doing exactly the opposite of what you are trying to do, right?
By only changing newid on a whole new session id OR when it should be counted as the first entry of a new session, then it will have sessid||'--'||newid where newid is the id of the first entry for that session.
Perhaps adding some comments as to your intent or some pseudocode would help.