create unique session IDs

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